Hi,
I have a problem: I have one query that does multiple inserts rows to one table. The number of rows to be inserted varies. I know that mysql_insert_id() returns only one last inserted id. How do I get each id of each inserted row from this query? What function?
Thanks,
John
you don’t, or not from query, or function.
mysql_insert_id() works on auto_increment columns. so if you have multiple, oops…
Let’s assume only 1 auto_increment.
anyway, if you have a table, want to insert with a query and find out what ids wher inserted, rather easy.
find out what exists first.
do query insert.
find out what exists now.
the difference is the inserted rows.
hmm… look like I have to do one insert at a time to get the id… too bad, mysql don’t give an array of all last inserted ids 
that I need.
Not mysql problem, in fact not problem. SQL no do this, any DBMS, reasons many.
you can use mysql_insert_id() as part of query to insert, can keep track that way.
if you want, write:
mysql_sum_insert_ids(“query”);
make the world a better place, but still won’t work consistently, reasons many.
btw, the insert will tell you how many rows inserted…
john, i’m just curious, can you explain a bit more about the circumstances that require you to know what the inserted id values were?
rudy
I’ll guess:
INSERT INTO tbl (cols)
SELECT (cols) FROM tbl WHERE …
yes, mister tinkles, or perhaps
insert into tbl
(col1, col2, …)
values
(val1a, val2a, …)
, (val1b, val2b, …)
, (val1c, val2c, …)
which is very handy syntax that only mysql has
but the question still stands: why?
i’m just curious, john
rudy
Hi r937,
I’m sorry I couldn’t tell you much since clients want to keep it secret. Basically, the application accepts input from specific type of users interested in the application. They will input the data that I will store temporary all in one table. I should store them in 2 or 3 temporary tables but I didn’t. (They are just temp so I don’t care.) Then later, clients will visually view these data and delete bad data from temporary table, but when data are good (most of the time), then I will fetch from this temp table and insert them into 4 permanent tables. Note that some of these data are array with many to many relationship so when I insert each array element, I need an array of last inserted ids so that I can create a lookup table (many to many). That why. Sorry 
since all hush hush, sounds like you need to think the DB a little better, but for your issue:
sounds like you can use mysql_insert_id() to create your lookup table for you.
for each array INSERT that needs a lookup, INSERT the lookup entry using mysql_insert_id().
as far as:
which is very handy syntax that only mysql has
I’ve used similar syntax on other DBMS. Though don’t have the code on this box, used to load the type (prod_type, acct_type) tables in one txt file, had one INSERT per table, had to update manually. Been awhile, should go dig for it…
Not handy programatically, easier to INSERT row at a time, if only for error checking. Imagine building long INSERT string, and either having syntax mistake or bad data, what then? INSERT by row, you can handle gracefully and continue, not fail.
Only about as handy as importing text file, but programatically easier to create file on the fly and then load, think the load will warn on probs but continue to next line/row.
Are we talking auto_increment columns here for ID numbers? If so, last_insert_id() should give you the ID number of the last row that was inserted in the table. If you add more than one row at a time as you’re doing, it will give the ID number of the first of those rows that was added. Knowing that IDs increment by one each time, you can then work out the ID of all rows.
For example, if you insert 4 rows, and last_insert_id() gives 5, then you know the IDs are 5, 6, 7, 8.
Important to note is that last_insert_id() gives the last auto_increment value on the current connection, so multiple connections doing simultaneous inserts will not interfere with the results that you get. I believe its purposely designed precisely for this situation.
While I’m not certain, I would also presume that when you do one INSERT statement that adds multiple rows, it is done as one atomic operation. So if you have two multiple inserts happening on two different connections, you won’t get interleaving – one will add all of its rows, then the other all of its rows, not one adding a few of its rows, then the other a few of its, the first a few more, and so on. If you do them as separate INSERT statements, however, then you could most certainly get interleaving like that.
All that assumes we’re talking auto_increment columns here, perhaps I’m off the topic…