I am currently trying to retrieve the auto incremented ID value by using LAST_INSERT_ID(), but am not sure if I'm doing it right. I need to use the ID field as foreign key for multiple records on the many side at times. The values I intend to insert on the many side of the relationship are stored in a PhP array, and I need to use the foreign key ID field for multiple records. Here are some ways I thought of trying. I don't know which are possible:
- Can I call LAST_INSERT_ID() multiple times, or does it only work once?
- If I can't call it several times, I was wondering if I could retrieve the ID field and store it in a PhP variable, and then use that variable each time I create an Insert query.
- If neither of these approaches work, do I need to run a loop to build up just one query with multiple Insert statements (one for each record on the many side)?
When I print the query on the PhP page, it prints LAST_INSERT_ID() and not the value. I'm guessing that's okay, as the processing that retrieves and uses the ID value takes place on the database server and not the web server?
The server has MySQL version 4.1.21.
The first insert statements goes as follows (a bit shortened, with '###' representing regular fields):
INSERT INTO tblProjects VALUES ('###', '###', '###', NULL);The next insert statment would then be as follows:
INSERT INTO tblLinks VALUES (LAST_INSERT_ID(), '###');
Thanks for reading through my long-winded question!