LAST_INSERT_ID()

[flowerpower]

Hi, I have two tables in a one-to-many relationship. On the one side of the relationship is an auto-incremented ID field. This part works. On the many side I intend to use the ID field as foreign key, but I have a problem with the insert statement.

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:

[list]
[*]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)?
[/list]

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):
[code]
INSERT INTO tblProjects VALUES
          ('###',
          '###',
          '###',
          NULL);
[/code]
The next insert statment would then be as follows:
[code]
INSERT INTO tblLinks VALUES
          (LAST_INSERT_ID(),
          '###');
[/code]

Thanks for reading through my long-winded question!

[flowerpower]

After browsing other threads on this site, and googling a bit, I found that I could indeed build the query by looping through the array of data values I want to insert in the linked table and just use LAST_INSERT_ID() many times, to build up an Insert statement with many records, so I'm all set now.

[shoz]

[quote author=flowerpower link=topic=107709.msg432453#msg432453 date=1158023332]
After browsing other threads on this site, and googling a bit, I found that I could indeed build the query by looping through the array of data values I want to insert in the linked table and just use LAST_INSERT_ID() many times, to build up an Insert statement with many records, so I'm all set now.
[/quote]

Just keep in mind that LAST_INSERT_ID() will have the auto_increment value generated for the last INSERT made. If you plan on using the same id in many inserts then you should store that value in a PHP var and build the insert statements using it.

The exception would be if the table you're inserting into has no auto_increment column meaning the LAST_INSERT_ID() value wouldn't change after each insert.