Trouble with MySQL last_insert_id()

Writting a script that inserts data into multiple tables - need Id from one

[erikcw]

Hi all,

I am writting a script that takes data from a dynamically generated form, and posts into MySQL.

I have written a function which checks what "type" of form entry it is (text, radio, check, etc.) by taking the POST array key (all of the form objects are numbered 1,2,3,4,n+) and comparing it against a DB table which tells me what type of data it is.

IF the data is a name or email address, it gets placed in the visitor table, otherwise it gets placed in the answers table (because name/email are the only two constants, other form items are dynamic).

The problem I'm running into is after I put the email/name into visitors, I try to get LAST_INSERT_ID() so then I can use in the 'uid' field in the answers table. (so I can do a table join later on, and as an identifier for who the data belongs to).

$temp_array = mysql_fetch_array(mysql_query("select last_insert_id() from visitors"));
$my_last_id = $temp_array['last_insert_id()'];

//now you can display it, to test it
echo $my_last_id;

when I echo $my_last_id, I get something like "13132" - but there is only one row in the DB right now. So I am guessing the everything after the "1" is some sort of identifier... (visitors.id is set to Primary and Indexed)

How do I get just the first part, so I can insert it into my other table?

Thanks!
Erik

PS Sorry about the long post!

[coopster]

Don't specify the table.

$temp_array = mysql_fetch_array(mysql_query("select last_insert_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.

Resource:
LAST_INSERT_ID() [dev.mysql.com]

<added>
I'm assuming your id column is

AUTO_INCREMENT

?
</added>

[erikcw]

I removed the table - but still got a similr result:

Inserted a new row into visitors:

last_insert_id() = 23435 (2 being the second row in the db - what are these other numbers?)

Thanks!
Erik

[erikcw]

Something more:

I tried running print_r($temp_array) on the array and it produced"

Array
(
[0] => 6
[last_insert_id()] => 6
)
Array
(
[0] => 46
[last_insert_id()] => 46
)
Array
(
[0] => 47
[last_insert_id()] => 47
)

So this explains the extra numbers when I echo $temp_array['last_insert_id()']; - now the question is why are these extra array entires getting added in?

[coopster]

Good question. Do you have something else in your query statement? Also, is your visitors.id set as AUTO_INCREMENT?

One last thing to do is run a query to see if there is indeed an AUTO_INCREMENT value in the table. From the command line, this query will tell you what the next auto_increment value is going to be...

SHOW TABLE STATUS LIKE 'visitors';

[erikcw]

Thanks for your reply Coopster!

Yes, I auto increment is set for visitors.id and answers.id (DB was setup in phpMyAdmin - I can verify that the auto increment is indeed working)

here is the query section from my script:

for ($i = 1; $i <= count($_POST); $i++) {
switch (checkType($i)) {
case "email":
$email = $_POST[$i];
unset($_POST[$i]); //MAKE SURE UNSET DOESN'T DESTROY array keys after.
break;
case "first":
$first = $_POST[$i];
unset($_POST[$i]);
break;
}
}
$optin = 1;
// generate and execute query
$query = "INSERT INTO visitors(email, first, optin, ip, timestamp) VALUES('$email', '$first', '$optin', '$REMOTE_ADDR', NOW())";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
$_SESSION['uid'] = mysql_insert_id();
setcookie("uid", $_SESSION['uid'], time()+60*60*24*100, "/");
for ($i = 1; $i < count($_POST); $i++) {
// generate and execute query
//$qid = key($_POST[$i]);
$temp_array = mysql_fetch_array(mysql_query("select last_insert_id()"));
$my_last_id = $temp_array['last_insert_id()'];

//now you can display it, to test it
print_r($temp_array);

//$query = "SELECT LAST_INSERT_ID() FROM visitors";
//$uid = mysql_query('SELECT LAST_INSERT_ID() FROM `visitors`');
$query = "INSERT INTO answers(uid, question_id, response, timestamp) VALUES('$uid', '$i', '$_POST[$i]', NOW())";
$result = mysql_query($query) or die ("Error in query: $query. " . mysql_error());
}
// print result
echo "<font size=-1>Update successful. <a href=list_book.php>Go back to the main menu</a>.</font><p>";
//echo $uid;

// close database connection
mysql_close($connection);

NOTE: When I tried using last_insert_id() in the last query (INSERT INTO answers(uid,....) values(LAST_INSERT_ID(),....)
It worked perfectly - the problem is that since I am looping through and performing several queries - I would get the LAST_INSERT_ID() that I wanted in the first INSERT, but after that I would get the ID from the previous INSERT (worked perfect, just not for this application... :-)

Thanks!
Erik

[coopster]

OK, first off, the reason you are getting 3 different values in the Array() printout via the print_r function is because it is within your loop! You must have 3 $_POST variables, other than the submit button I'm guessing because of the less than (<) rather than a less than or equal to (<=) in your loop check). That was really confusing me, it helps to see your code ;)

The rest of your issues start because you are running the SELECT LAST_INSERT_ID() query within your loop. It seems to me you want to get the LAST_INSERT_ID() from the INSERT into the visitors table, store that value, then use it as you insert into the answers table.

Make sense? Get the value, then enter into your loop, perform the inserts with the value.

[erikcw]

That did the trick! Thanks for your help Coopster!