php - MySQL insert multiple records and reference them in another table -

i make inquiry generic possible, don't want specifics rather learn accepted approach problem.

i have data, structured this:

$data => array     (      [0] => array            (             [field_index] => 2             [comment] => damage of object 1             [image] => array                    (                     [url] => /path/to/file/my_image.jpg                     [fname] => my_image.jpg                     [fsize] => 77005                     )              ),       [1] ...       [2] ...       ) 

i trying insert in 2 tables files , records, image data stored in first , record data reference image id stored in second.

the way can loop through items , run insert queries 1 one, so:

foreach($data $item){  // insert image files table // insert_id // insert item data records (use insert_id of image reference) } 

my question is:

a) ok run insert queries one-by-one opposed inserting multiple rows in single query? b) there alternative method of achieving after - referencing data in 2 tables.

i want keep file data separately because:

• not records may have image data
• $data may have revisions, records change not all, , don't want redundant image records

you have 2 options -

1. you can insert records individually starting parent , finishing children. can use last_insert_id last id inserted mysql, allows insert parent, it's primary key , include in insert on child records. return last_insert_id output parameter in mysql stored proc calling code can manage it.

you can insert multiple rows table single insert command - faster way of inserting lots of records.

2. use orm (object-relational manager) can insert data objects in code, meaning don't have worry sql side of things orm wrapper handles communication database you. quite big change existing project, worthwhile.

one note on orms - lot have dealt don't bulk inserts of thousands or tens of thousands of records particularly well, sql may best bet in instance.