[SOLVED] - How to get the auto generated ID just generated

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

[SOLVED] - How to get the auto generated ID just generated

Post by anjanesh »

Table1 is having a structure as :
ID : int auto_generate
Name : varchar

Table 2 has a structure as :
ID : This is the ID that is present in Table1
Pass : varchar

In my script I need to do enter into 2 tables with the same ID. Obviously the first is auto generated. But I need the same ID in the second table. For that how am I supposed to get the autogenerated ID that it generated ? If I try SELECT I wound't know which one to select because ID is primary. Also I dont want to select based on last time because if some 100 people at the same time (upto same second) add data then I would get 100 possible values.

Code: Select all

<?php
mysql_query("INSERT INTO Table1 VALUES ('',$Name)");
$ID= // Get the auto generated ID from Table1
mysql_query("INSERT INTO Table2 VALUES ($ID,$Pass)");
?>
Thanks
Last edited by anjanesh on Sat Aug 07, 2004 11:08 am, edited 1 time in total.
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

I would think of something along the lines of:

Code: Select all

<?php
$query = "INSERT INTO Table1 VALUES ('',$Name)";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_assoc($result);

$ID= $row['ID']  // Get the auto generated ID from Table1

mysql_query("INSERT INTO Table2 VALUES ($ID,$Pass)");
?>
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

It gave the warning :

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in xxxxxxxxxx\x.php on line x
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Try:

Code: Select all

<?php
$query = "INSERT INTO Table1 VALUES ('',$Name)";
$result = mysql_query($query) or die(mysql_error());

if (mysql_num_rows($result))
{
 $row = mysql_fetch_assoc($result);
 $ID= $row['ID']  // Get the auto generated ID from Table1
 mysql_query("INSERT INTO Table2 VALUES ($ID,$Pass)");
}
?>
Or you may need to do it like:

Code: Select all

<?php
$query = "INSERT INTO Table1 VALUES ('',$Name)";
$result = mysql_query($query) or die(mysql_error());

if (mysql_num_rows($result))
{
 $row = mysql_fetch_assoc($result);
 $ID= $row['ID']  // Get the auto generated ID from Table1
}
mysql_query("INSERT INTO Table2 VALUES ($ID,$Pass)");
?>
If neither work please give your error!
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

In both cases the execution stops at the mysql_num_rows($result). Guess $result is not the kind of resource for retrieving ?

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in xxxx\x.php on line xx
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Why dont you use a for() loop and just fill in the ID's accordingly :D
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

I did find this though but it still doesn't answer my initial question - If there are 100 users adding to the table at the very same time then will it return the last ID genertated from that script or

$result=mysql_query("SELECT LAST_INSERT_ID()");
$row=mysql_fetch_array($result);
echo $row[0]; gave the correct ID.

http://dev.mysql.com/doc/mysql/en/Infor ... tions.html
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

According to MySQL Manual :

The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions.
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Again I would use a loop instead of auto_increment attributes, Just add 1 onto the last database ID and do the same for the next which gives you two. A problem with auto_increment is when your removing certain records etc...
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

Yes. When using a loop and generating it automatically we need not only to add but check the entire table in case another such value is not in (since we need to check to primary manually). I think thats not a small algo. auto_generate will do automatically that why its easier. I was just searching for the last generated id which I found. I think the last one given will be the last one given to that particular client instead of someones else. Thats what I read in the manual. If anyone finds it wrong let please me know.
Thanks
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Well good luck anjanesh. You should also have a look at Last.
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

But Joe there is no LAST function in MySQL. Perhaps that was a standard SQL funtion or only in MS SQL or something else. I searched the entire MySQL manual for LAST but nothing. All it gave were LAST_INSERT_ID,LAST_DAY and some LAST property in some tables etc.
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Actually it does exist. :D

Untested:

Code: Select all

SELECT LAST(id) AS last_id FROM table1 ORDER BY id
User avatar
anjanesh
DevNet Resident
Posts: 1679
Joined: Sat Dec 06, 2003 9:52 pm
Location: Mumbai, India

Post by anjanesh »

SELECT LAST(ID) AS last_id FROM `table1` ORDER BY ID
gave
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(ID) AS last_id FROM `table1` LIMIT 0, 30' at line 1
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Odd, I know its an existing function, view the link I just gave a few posts upwards.
Post Reply