Unanswered: mysql_insert_id in multi values INSERT statements
first i explain what i want to do:
suppose that, i have 2 table: CARS (that keep cars infos) & CAR_REPAIRS (keep explaination of repairments done on the car).
always , when i add car infos to `CARS` table, i have a repair case that must add to CAR_REPAIRS table. so i need a car_id in CARS table for adding a record in CAR_REPAIRS table.
now in case that i want to add multi records to cars table, i need multi car_id for insert repairments to CAR_REPAIRS table.
now i want to know best way that i can insert these records:
is there anyway for joinin tables in INSERT statement?
or i must take the mysql_insert_id and rewrite a INSERT statement for insertin to CAR_REPAIRS table after run this query:
INSERT INTO `cars`
mysql_insert_id will ONLY return the first row insert ID for a multi-row statement. So in your scenario you are entering rows 1,2,3 but mysql_insert_id will only return 1. Thus causing a bit of an issue.
Generally speaking if you're going to do multiple value inserts use placeholders in your SQL statements or stored procedures (in mysql5). Thus utilising mysql_insert_id after each insert as guelphdad suggested. It might be advisable to utilise transactions also, the reasoning being that if your second insert (into repairs table) doesn't work you DON'T want to insert in the first table to remain and should probably rollback. You could specify savepoints as you go along with your transaction looping your inserts.
Just an out of MySQL question for you : are you going to be adding a car at the same time as adding a repair report? They seem like logically different steps to me. You add a bunch of cars to your system, then every time some repair is done to it you add a repair report (having selected the ID from the cars table for the car in question).
Ah ok, so you're adding a batch of (200) cars AND reports at the same time. Sounds like something that would be better suited to a programming language rather than pure SQL. In PHP you could grab an array of the inserted ids for each car that is processed and for each report you then add on in batch you apply the corresponding number in the array (i.e. the number that was returned from the inserted car record).
I'm just trying to think if there is a better way. I know that in a database like Oracle you could probably use PL/SQL to run this kind of batch job nicely.
Another question (related to design): I'm guessing the relationship between cars and repair reports is a one-to-many? Obviously if this is the case it justifies the use of seperate tables for reports and cars. Just curious about the way you're modelling it. Helps me to get a better overall picture of what you're trying to achieve.