0

Possible Duplicate:
How to get the id of a row i've just inserted php/mysql

I was wondering what's the most efficient way of inserting something and selecting it's ID property at the same time?

For example, I have a table with auto incrementing primary ID column. I insert an item into that table and I need to know the autoincremented ID for use with another table.

Right now, what I do is:

  1. INSERT INTO table1 the data
  2. SELECT FROM table1 the ID
  3. INSERT INTO table2 another set of data along with ID.
Community
  • 1
  • 1
Propeller
  • 2,465
  • 6
  • 35
  • 49
  • All DB libraries I know (you give no clue of which one you are using) can fetch the auto-incremented ID from the INSERT statement. There's absolutely no need to make a SELECT. – Álvaro González Oct 02 '12 at 16:45
  • I'm using a MySQL class I found on GitHub. I believe it's this one. https://github.com/roncli/PHP-MySQL-Class – Propeller Oct 02 '12 at 16:46
  • You need to read the PHP and MySQL manuals, there are **many** examples about auto-increment ids. – Jocelyn Oct 02 '12 at 17:59

2 Answers2

6

You can do this

INSERT INTO foo (auto,text)
    VALUES(NULL,'text');         --generate ID by inserting NULL
INSERT INTO foo2 (id,text)
    VALUES(LAST_INSERT_ID(),'text');  --use ID in second table

src: http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html

rs.
  • 26,707
  • 12
  • 68
  • 90
  • Will this work if I have my queries set up as follows? `INSERT INTO foo (secondcolumn,thirdcolumn) VALUES ('value1','value2')`? Notice how I omitted the auto incrementing ID column. – Propeller Oct 02 '12 at 16:47
  • LAST_INSERT_ID() (with no argument) returns a BIGINT (64-bit) value representing the first automatically generated value that was set for an AUTO_INCREMENT column by the most recently executed INSERT statement to affect such a column. – edwardmp Oct 02 '12 at 20:22
1

You can try mysql_insert_id();

http://php.net/manual/en/function.mysql-insert-id.php

poudigne
  • 1,694
  • 3
  • 17
  • 40