jay_gorio Posted March 8, 2015 Share Posted March 8, 2015 I want to get the primary key values of movie and actor table. When i selected values in radio button primary key has been created automatically to the movie and actor table. Now I want to get that primary key value and insert to the movie_actor table. my code connect.php <?php $dbc = mysqli_connect('localhost','root','black98765','db_name') OR die("Cannot connect to MySQL:" . mysqli_connect_error()); //insert into actor table $q = "INSERT INTO actor (name) VALUES ('$actor')"; //insert into movie table $q2 = "INSERT INTO movie (movie_name, release_year) VALUES ('$movie','$year')"; //movie_actor table $q3 = "INSERT INTO movie_actor (movie_no,actor_no,rate) VALUES ('$rate')"; //what value i need to put inside values for movie_no and actor_no? //connect and insert $q $r = mysqli_query($dbc,$q); $r2 = mysqli_query($dbc,$q2); $r3 = mysqli_query($dbc,$q3); if($r && $r2 && $r3){ echo "Inserted Successfully!"; }else{ echo "Failed to Insert Data!"; mysqli_error($dbc); } mysqli_close($dbc); ?> form.php <?php if(isset($_POST['submit'])){ if($_SERVER['REQUEST_METHOD'] == 'POST'){ $error = array(); //choose actor if(!isset($_POST['actor'])){ $error[] = "Please choose of the following actors!"; }else{ $actor = $_POST['actor']; } //choose movie if(!isset($_POST['movie'])){ $error[] = "Please choose of the following movies!"; }else { $movie = $_POST['movie']; } //choose release year if(!isset($_POST['year'])){ $error[] = "Please choose of the following release year!!"; }else{ $year = $_POST['year']; } //choose rate if(!isset($_POST['rate'])){ $error[] = "Please choose of the following rate!"; }else{ $rate = $_POST['rate']; } //if no errors if(empty($error)){ require('connect.php'); }else{ echo "<p>System Error!</p>"; foreach($error as $msg){ echo $msg."<br/>\n"; } } } } ?> <form action="form.php" method="POST"> <p>Select on the following Selections</p> <p><label for="actor">Name of Actor:</label> <input type="radio" name="actor1" value="Jet Li"/>Jet Li <input type="radio" name="actor2" value="Sylvester Stallone"/>Sylvester Stallone <input type="radio" name="actor3" value="Jason Statham"/>Jason Statham</p> <p><label for="movie">Name of Movie:</label> <input type="radio" name="movie1" value="Expendables 3"/>Expendables 3 <input type="radio" name="movie2" value="Rocky"/>Rocky <input type="radio" name="movie3" value="Kiss of the Dragon"/>Kiss of the Dragon</p> <p><label for="movie">Release Year:</label> <input type="radio" name="year1" value="2014"/>2014 <input type="radio" name="year2" value="1976"/>1976 <input type="radio" name="year3" value="2001"/>2001</p> <p><input type="submit" name="submit" value="Insert"/></p> </form> Link to comment Share on other sites More sharing options...
jay_gorio Posted March 8, 2015 Author Share Posted March 8, 2015 Please help thanks.. Link to comment Share on other sites More sharing options...
luisfalcon Posted March 9, 2015 Share Posted March 9, 2015 SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name'; with this command you can get the auto increment value for the table that you want. so you could build your insert using a subquery like this $q3 = "INSERT INTO movie_actor (movie_no,actor_no,rate) VALUES ( SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'movie' AND TABLE_NAME = 'table_name', SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'actor' AND TABLE_NAME = 'table_name', '$rate'); but this is an ugly solution. that subquery is a mess. You could use the last_inserted_id() function on a select statement and run the query and assign it to a variable then use those variables on your query. But this is not a super good solution either i think since you have to hit the database too many times. Now think of this word (auto_increment) Meaning that it increases all the time so the last id inserted on a table is the biggest one too!!! So going back to sub-queries you could do the approach from the first answer i gave (the ugly one) but with a simpler and prettier query: $q3 = "INSERT INTO movie_actor (movie_no,actor_no,rate) VALUES (SELECT MAX(id) FROM movies, SELECT MAX(id) FROM actor, $rate); =) hope this helps... sorry for the long post =) Link to comment Share on other sites More sharing options...
luisfalcon Posted March 9, 2015 Share Posted March 9, 2015 SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name' AND TABLE_NAME = 'table_name'; with this command you can get the auto increment value for the table that you want. so you could build your insert using a subquery like this $q3 = "INSERT INTO movie_actor (movie_no,actor_no,rate) VALUES ( SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'movie' AND TABLE_NAME = 'table_name', SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'actor' AND TABLE_NAME = 'table_name', '$rate'); but this is an ugly solution. that subquery is a mess. You could use the last_inserted_id() function on a select statement and run the query and assign it to a variable then use those variables on your query. But this is not a super good solution either i think since you have to hit the database too many times. Now think of this word (auto_increment) Meaning that it increases all the time so the last id inserted on a table is the biggest one too!!! So going back to sub-queries you could do the approach from the first answer i gave (the ugly one) but with a simpler and prettier query: $q3 = "INSERT INTO movie_actor (movie_no,actor_no,rate) VALUES (SELECT MAX(id) FROM movies, SELECT MAX(id) FROM actor, $rate); =) hope this helps... sorry for the long post =) Link to comment Share on other sites More sharing options...
Larry Posted March 10, 2015 Share Posted March 10, 2015 Thanks for your post! That would work. I'd just invoke the PHP mysql_insert_id() function, which calls last_insert_id() for you. Link to comment Share on other sites More sharing options...
Recommended Posts