Jump to content
Larry Ullman's Book Forums

How To Get Values Of Auto Incremented Values Using Mysqli_Insert_Id()?


Recommended Posts

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


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

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

 Share

×
×
  • Create New...