1

I have a table with has the possibility of a parent child relationship. "parentid" has a foreign key to it's parent's "dataid" for example:

dataid     dataname     parentid
---------------------------------
1          example      NULL
2          record       NULL
3          foo          1
4          bar          1

In this example population "foo" and "bar" are children of "example". Now i would like to insert multiple data records into this table, since dataid is auto incremented i would like to know if i can get this id after my insert without executing an extra query. So for example:

INSERT INTO table (dataname, parentid) VALUES 
    ( cat, NULL ), ( tiger, **dataid from cat** ), ( lion, **dataid from cat** );

To get this result:

dataid     dataname     parentid
---------------------------------
5          cat          NULL
6          tiger        5
7          lion         5

ANSWER -- Thanks to PLB (and Simon Cambier):

Still executing two queries after each other but i think this is solution will do.

/*first insert parent*/
INSERT INTO table (dataname, parentid) VALUES 
   ( cat, NULL );

/*insert children*/
INSERT INTO table (dataname, parentid) VALUES
   ( tiger, LAST_INSERT_ID() ), ( lion, LAST_INSERT_ID() );
rofavadeka
  • 577
  • 6
  • 19
  • 1
    This is the link you're looking for: http://stackoverflow.com/questions/1685860/php-how-to-get-last-inserted-id-of-a-table – Simon Cambier Jun 19 '13 at 13:21
  • I think Simon is right - I think you'll need a minimum of two queries for what you are looking for. – Nathan Loding Jun 19 '13 at 13:21
  • 1
    [`LAST_INSERT_ID()`](http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id)? – Leri Jun 19 '13 at 13:22
  • Thanks, for the quick response. I think Simon is right, therefor +1. When i got it working i will let you know! – rofavadeka Jun 19 '13 at 13:23
  • I like PLB's since i can execute that right away without having go back to PHP. +1. Will post my answer below. – rofavadeka Jun 19 '13 at 13:49

0 Answers0