InnoDB insert into multiple tables from flat file

[minderella]

Hello all,

I'm trying to insert a flat file into two tables.  I need to use regex or something like explode() to format the file and then insert the data into my tables.  Here's what the dataset looks like:

  doc_number            pin_number
2005123413214 20-16-105-034- & 20-16-104-035-
0146546487831 15-14-230-155-0000
2465454878646 15-14-569-233-0000
2456545478979 14-24-553-123- & 14-24-553-123, 23-17-105-026-

Here's what I've gotten so far:

[code]explode("& || ,", $pin_number);// I haven't quite tested this yet, I need help getting the array into input columns!

$str = preg_replace("/[^0-9]/", "", $pin_number);
$length = strlen($str);
if($length < 14){
$input = $str . "0000";
}

else {
$input = $str;
}[/code]

Here is my database design:

[code]$query1 = "CREATE TABLE mtg_doc (
  doc_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  doc_number INT NOT NULL
) ENGINE = INNODB";

$query2 = "CREATE TABLE mtg_pin (
pin_id INT PRIMARY KEY AUTO_INCREMENT,
pin_number INT NOT NULL
) ENGINE = INNODB";

$query3 = "CREATE TABLE mtg_pin_identifier (
doc_id INT NOT NULL,
pin_id INT NOT NULL,
PRIMARY KEY(doc_id, pin_id),
INDEX (doc_id),
FOREIGN KEY (doc_id) REFERENCES mtg_doc (doc_id) ON DELETE CASCADE ON UPDATE CASCADE,
INDEX (pin_id),
FOREIGN KEY (pin_id) REFERENCES mtg_pin (pin_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = INNODB";



[/code]

Any suggestions or ideas?  I can't figure out how to create columns for the pin_number and associate them with the doc_number.  Oh, and there are alot more than three pin numbers in some rows of the dataset.

[artacus]

Ok, so the pin# are all seperated by an "&"?

I would split it into arrays twice. The first time to split it into $doc and $pin arrays, so now
$doc[$i] matches $pin[$i] and then split $pin[$i] again.

[code]
for($i=0; $i<count($doc); $i++) {
  //insert into the doc table here
  for($n=0; $n<count($pin[$i]); $n++) {
      //insert into the pin table here  $doc[$i] --> $pin[$i][$n];
  }
}[/code]

[minderella]

No, pin_number entries are separated by & and ",".  I'm going to use regex to clean it. 

[artacus]

Yeah, use regex to split it actually but whatever the separators are, the process is still the same.

[minderella]

I'm still trying to figure out how this is going to work because I am absolutely terrible with arrays.  Could you elaborate with an example?  Would it be easier if I used joins instead? 

Do you know much about ID synchronization?  I've heard stories about fields not loading properly and getting all jumbled up.  I think that there are two PHP functions for getting the last generated ID but I can't remember what they are called or which one would better suit my needs. 

Any help would be appreciated.