SELECT last id, without INSERT

mysql lastinsertid
Illes Peter picture Illes Peter · Jun 28, 2010 · Viewed 108.3k times · Source

I'm trying to retrieve the id of one table A to insert into another table B. I cannot use last_insert_id() as i have not inserted anything into A. Any ideas on how to do this nicely?

$n = mysql_query("SELECT max(id) FROM tablename"); doesn't seem to work, nor does

$n = mysql_query("SELECT max(id) FROM tablename GROUP BY id");

Answer

Mike picture Mike · Jun 28, 2010

In MySQL, this does return the highest value from the id column:

SELECT MAX(id) FROM tablename;

However, this does not put that id into $n:

$n = mysql_query("SELECT max(id) FROM tablename");

To get the value, you need to do this:

$result = mysql_query("SELECT max(id) FROM tablename");

if (!$result) {
    die('Could not query:' . mysql_error());
}

$id = mysql_result($result, 0, 'id');

If you want to get the last insert ID from A, and insert it into B, you can do it with one command:

INSERT INTO B (col) SELECT MAX(id) FROM A;

Related questions

How to program a MySQL trigger to insert row into another table?

I'm looking to create a MySQL trigger on a table. Essentially, I'm creating an activity stream and need to log actions by users. When a user makes a comment, I want a database trigger on that table to fire and: …

Read More
mysqli last insert id

I would like to associate the image with firstname, lastname...how can I retrieve the last rowand use it to insert to the other table? I tried $image = $mysqli->insert_id; then binding but it doesn't work. Can someone …

Read More
MySQL LAST_INSERT_ID() used with multiple records INSERT statement

If I insert multiple records with a loop that executes a single record insert, the last insert id returned is, as expected, the last one. But if I do a multiple records insert statement: INSERT INTO people (name,age) VALUES (…

Read More