How to get the last inserted id on a table to use it on another insert on another table?

Question

Im trying to get id of the last inserted id on a table called authors to use it in another insert to a table called mail.

As you can see this is inserting a new author in table authors, and right after the insertion it must insert a welcome message to the new author in table mail.

Im using mysql_insert_id() but its not working. Nothing is saved to database and no errors occurs.

I know i should not use mysql_query but its ok, thats not the point here, just ignore this please.

if(!isset($row[email])){ $sql = "INSERT INTO authors VALUES (NULL, 0, '".$email."', '".$username."', NULL, '".$first."', '".$fullname."', '".$first."', NULL, NULL, NULL, NULL,'http://www.gravatar.com/avatar/".md5(strtolower(trim($email)))."', 0);"; mysql_query($sql); $sqlmsg = "INSERT INTO mail VALUES (NULL, 1, mysql_insert_id(), 'Welcome', Hello! Welcome to the website!', 'unread', NOW, 0, 0)"; mysql_query($sqlmsg); }

Why nothing happens when a new author is inserted?

Thank you.

John Woo · Accepted Answer · 2013-04-12 03:56:28Z

You need to concatenate it within the string,

$sqlmsg = "INSERT INTO mail VALUES (NULL, 1, " . mysql_insert_id() . ", 'Welcome', Hello! Welcome to the website!', 'unread', NOW, 0, 0)";

LawrenceGS · Accepted Answer · 2013-04-12 04:46:48Z

Try saving it on a temporary variable before using it on the next query.

<?php if(!isset($row[email])){ $sql = "INSERT INTO authors VALUES (NULL, 0, '".$email."', '".$username."', NULL, '".$first."', '".$fullname."', '".$first."', NULL, NULL, NULL, NULL,'http://www.gravatar.com/avatar/".md5(strtolower(trim($email)))."', 0);"; mysql_query($sql); $author_id = mysql_insert_id(); $sqlmsg = "INSERT INTO mail VALUES (NULL, 1, $author_id, 'Welcome', Hello! Welcome to the website!', 'unread', NOW, 0, 0)"; mysql_query($sqlmsg); } ?>

Cory Cox · Accepted Answer · 2013-04-12 04:33:43Z

Use a select query on the table and order by 'id' desc with limit 1

select `id` from `authors` order by `id desc limit 1

will return the last id if it's auto incremented.

Collectives™ on Stack Overflow

How to get the last inserted id on a table to use it on another insert on another table?

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related