Page 1 of 2
[SOLVED] - How to get the auto generated ID just generated
Posted: Sat Aug 07, 2004 10:21 am
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
Posted: Sat Aug 07, 2004 10:28 am
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)");
?>
Posted: Sat Aug 07, 2004 10:40 am
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
Posted: Sat Aug 07, 2004 10:43 am
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!
Posted: Sat Aug 07, 2004 10:53 am
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
Posted: Sat Aug 07, 2004 10:56 am
by Joe
Why dont you use a for() loop and just fill in the ID's accordingly

Posted: Sat Aug 07, 2004 10:57 am
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
Posted: Sat Aug 07, 2004 10:59 am
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.
Posted: Sat Aug 07, 2004 11:00 am
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...
Posted: Sat Aug 07, 2004 11:07 am
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
Posted: Sat Aug 07, 2004 11:09 am
by Joe
Well good luck anjanesh. You should also have a look at
Last.
Posted: Sat Aug 07, 2004 11:17 am
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.
Posted: Sat Aug 07, 2004 11:19 am
by Joe
Actually it does exist.
Untested:
Code: Select all
SELECT LAST(id) AS last_id FROM table1 ORDER BY id
Posted: Sat Aug 07, 2004 11:22 am
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
Posted: Sat Aug 07, 2004 11:24 am
by Joe
Odd, I know its an existing function, view the link I just gave a few posts upwards.