Sanity check - inserting multiple FK records

[Jonob]

Hi all,

 

I need to populate a mysql database with some demo data from php. Easy enough.

 

However, the issue that I am coming across is that I have two linked tables - i.e. PK from first table is FK in second (I'm over-simplyfying here, but you get the idea).

 

Obviously when records are inserted into first table, I only know the PK once its returned, and I then have to use that as the FK in the second table. So, at the moment, I am looping through an array of records for the first table: inserting one record, get the PK, insert records in the second table. Over and over again.

 

Loop, loop, loop. Slow.

 

Given that this demo data has to be done on a regular basis (one of the variables in first table will change each time), am I missing something really obvious here? i.e. is there an easy way for me to create all the first table records in one database hit, and all the second table records in another?

 

Any tips greatly appreciated.

[RussellReal]

what exactly is PK? is it the auto-incrementing key in the table?

[MadTechie]

You mean like loop from 1 array (Array A) and then getting the returned ID and saving that in array B then on the Array B loop your have all the ID's required so instead of looping you could so one massive insert, ie

 

(sorry typed it direct so no tabs etc )

$arrayA = array(array("blar", "blar2","blar2"),array("blar3", "blar4", "blar5"));

$arrayB = array(array('field1' => "blar", 'field2' => "blar2", 'field3' => "blar3"),array( 'field1' => "blar4", 'field2' => "blar5", 'field3' => "blar6"));
foreach($arrayA as $K => $A)
{
//insert data $uID = retuned data
$arrayB[$K]['uID'] = $uID;
}

$data = "";
foreach($arrayB as $B)
{
$data .= "('{$B['field1']}','{$B['field2']}','{$B['field3']}','{$B['uID']}'),"; 
}
$data = trim($data,","); //remove last ,
echo "INSERT INTO table (`field1`, `field2`, `field3`, `fID`) VALUE $data";

[Jonob]

Yup yup...kinda along my thoughts too.

 

What I am trying at the moment:

 

-Bulk insert everything into tableA

-Select everything on tableA thats just been inserted (I got a FK in tableA that I can use) and create an $array

-Create sql insert string for tableB, by using relevant records from $array:

INSERT INTO tableB (field1, field2)
VALUES
($array[0]->id, 10),
($array[0]->id, 20),
($array[1]->id, 5),
($array[1]->id, 6),
($array[1]->id, 7)

-Bulk insert into tableB

 

This means 1 insert into tableA, 1 select from tableA, 1 insert into TableB

 

Gotta be faster than looping each time...

[MadTechie]

True, but if you do a bulk in insert with arrayA then how will you get all the ID's (i mean what if another insert is made while your doing this ?)

 

maybe a paired insert

INSERT INTO TableA (fields) VALUES ($dataA); INSERT INTO TableB (fields,id) VALUES ($dataB,LAST_INSERT_ID)

 

 

*Should i move this to MySQL section ?*

[PFMaBiSmAd]

How about, lock the table(s) as necessary, bulk insert the new rows into table A. Use a single INSERT ... SELECT query to insert the new rows into table B that correspond to what was just inserted into table A. Execute UPDATE queries on table B to fill in the unique information for each row.

[Jonob]

True, but if you do a bulk in insert with arrayA then how will you get all the ID's (i mean what if another insert is made while your doing this ?)

That shouldnt make a difference.

 

All the records that go into tableA also have a FK, but its the same FK (lets call it company_id) for each bulk insert into tableA. So, after I have inserted, I do

$array = SELECT id FROM tableA WHERE company_id = 5

(yeah, thats just shorthand for creating the array)

 

Now, onto tableB bulk insert.

-I know that the FK for the first two records will always be $array[0]->id

-I know that the FK for the next 3 records will always be $array[1]->id

-And so on

 

So, I have my sql bulk INSERT for tableB prepared, with variables in place for the FK (trans_id) in php

INSERT INTO `trans_detail` (`trans_id`, `someField`) 
VALUES
(" . $array[0]->id . ", 5),
(" . $array[0]->id . ", 10),
(" . $array[1]->id . ", 6),
(" . $array[1]->id . ", 7),
(" . $array[1]->id . ", ,
etc