Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
jay_gorio

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>

  

Share this post


Link to post
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 =)

 



Share this post


Link to post
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 =)

 


Share this post


Link to post
Share on other sites

Thanks for your post! That would work. I'd just invoke the PHP mysql_insert_id() function, which calls last_insert_id() for you.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...