Insert Rows into MySQL and immediately retrieve row id

I want to insert a new row with data into a MySql table, then be able know what the row id is (row id is an autoincrement column in mysql, so the value is sent by mysql).

Let's say (for example) that this is my table structure:

CREATE TABLE IF NOT EXISTS `subpages` (
  `subpage_id` mediumint(3) NOT NULL auto_increment,
  `meta_title` varchar(120) NOT NULL default '',
  KEY `subpage_id` (`subpage_id`)
) ;

Code (markup):

I use PHP to insert a row of data. I then want to use PHP to reference the newly inserted row by subpage_id. How do I know what the correct value is?

Hope this makes sense...if anyone can point me to a tutorial or info for this, that would be great. Thanks!

 

http://www.tutorialspoint.com/mysql/mysql-using-sequences.htm

after you insert the record with mysql_query(...);
use something like
$newid=mysql_insert_id();

mysql_insert_id() retrieves the AUTOINCREMENT-column value of your last added record

 

OK, so I found a thread on this issue over here.

According to that thread, this is the code I need:

$lastID = mysql_insert_id($link);

Code (markup):

But...what is the $link variable? Do I need to set that? Or just leave it empty?

 

How are you connecting to mysql and querying? plain mysql, mysqli, etc...

Generally:

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');

mysql_select_db('db');

$query = mysql_query("INSERT.....");

$last_id = mysql_insert_id();

mysqli works a little differently.

 

As long as you don't run another insert query on the actual page that the script is running on, it will retrieve the proper id. The insert_id is attached to the link / query that you just completed and not the database itself, so there isn't a chance of a collision or missed id.

 

Not sure. I'm guessing plain MySQL. Here is my connection code:

$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!");

mysql_select_db($db) or die ("Unable to select database!");

Code (markup):

 

If you don't use a link-id for your database connection and query, you can ignore the $link bit.

If you work with connections to different databases (or hosts) you would use a link id for the different connections.

http://nl2.php.net/manual/en/function.mysql-connect.php

 

The code above should work fine then. Just put the insert_id function after the insert statement.

 

Yup, it works. Thanks, mate!