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.
<?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.
<?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)");
?>
<?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)");
}
?>
<?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)");
?>
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.
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.
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...
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
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.
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