I have this array with data rows from an excel file used with SimpleXLSX in php check https://github.com/shuchkin/simplexlsx, Working just fine, but the thing is that i want all the datas to insert them into two MySQL tables

In table 1 it's ok, but in table 2 it's the problem, I want to take all the last inserted IDs entered from table 1 and insert them in table 2 with dansatori IDs which is an array if you look at the SimpleXLSX output at [dansatori]

Please check this image to see what i want to achive: MySQL Tables

Array SimpleXLSX output in PHP:

Array
(
    [0] => Array
        (
            [numecoregrafie] => Vampire Dance
            [niveldans] => 2
            [coregraf] => Damon Salvatore
            [sectiuni] => 1
            [disciplina] => 7
            [catvarsta] => 6
            [dansatori] => Array
                (
                    [0] => 84
                    [1] => 86
                )

            [nrdansatori] => 2
        )

    [1] => Array
        (
            [numecoregrafie] => End of the world
            [niveldans] => 1
            [coregraf] => Stephany
            [sectiuni] => 2
            [disciplina] => 14
            [catvarsta] => 4
            [dansatori] => Array
                (
                    [0] => 82
                    [1] => 87
                )

            [nrdansatori] => 2
        )

    [2] => Array
        (
            [numecoregrafie] => Slapping Chris Rock
            [niveldans] => 2
            [coregraf] => Will Smith
            [sectiuni] => 1
            [disciplina] => 13
            [catvarsta] => 18
            [dansatori] => Array
                (
                    [0] => 84
                )

            [nrdansatori] => 1
        )

)

Wthat i have tried so far:

$file  = "MomenteMultiple_RDCP_2.xlsx";  // The excel file 
$xlsx  = new SimpleXLSX( $file );  // SimpleXLSX object

$dns       = array();
$skip      = 1; 
$dansatori = array();
$lastID    = array();

foreach ($xlsx->rows() as $key => $fields)
 { 
    if($skip !=1)   // Skipping the first row from XLSX file.
    {
      if($fields[7] > 0) {
         
        $rowValue   = !empty($fields) && $fields != "" ? $fields : 'null';   
        $result     = array_filter($rowValue);  // remove empty values from array
        $values     = explode(",",  $result[6]);  // explode string into array
        $dancersIDS = array_filter($values);  
        
        $dns[] = [
            'numecoregrafie' => $result[0],
            'niveldans'      => $result[1],
            'coregraf'       => $result[2],
            'sectiuni'       => $result[3],
            'disciplina'     => $result[4],
            'catvarsta'      => $result[5],
            'dansatori'      => $dancersIDS,
            'nrdansatori'    => $result[7],
            'uid'            => $user->filter->id
        ];   // Add the values to the array
      }   
    }
    $skip  ;  // Increment the skip value
 }
 
// Table 1
 $query = 
 'INSERT INTO `rdcp_momente` 
  (numecoregrafie, 
   niveldans, 
   coregraf, 
   sectiuni, 
   disciplina, 
   catvarsta, 
   nrdansatori
   ) VALUES';   // Query to insert values into table 1
  
  // Table 2
 $queryd = 
 'INSERT INTO `rdcp_dansatorim` 
  (`did`, 
  `uid`,
   `mid`
  ) VALUES';   // Query to insert values into table 2
  
  foreach($dns as $d) { 
      $query .= "
      (
        '".$d['numecoregrafie']."', 
        '".$d['niveldans']."', 
        '".$d['coregraf']."', 
        '".$d['sectiuni']."', 
        '".$d['disciplina']."', 
        '".$d['catvarsta']."', 
        '".$d['nrdansatori']."'
      ),"; // Query to insert values into table 1
      foreach($d['dansatori'] as $dansator) 
      { 
        $queryd .= "
        (
          '".$dansator."', 
          '".$user->filter->id."', 
          '".$lastID."'
        ),"; // LastID is the last inserted id of the table 1
      } 
  } 
  
  $query  = rtrim($query, ",");  // remove last comma
  $queryd = rtrim($queryd, ","); 
  $query .= ";";  
  $queryd .= ";"; 
  $db->query($query);  // insert into table 1 
  $lastID[] = $db->insertId(); // get the last inserted id
  $db->query($queryd); // insert into table 2
   
    echo '<pre>';  
    echo var_dump($query);    
    echo '<pre>';  
    echo var_dump($queryd);

Only one ID inserted instead of all ids that corresponds with last inserted rows

CodePudding user response：

1. Prepare your INSERT statements
2. Bind parameters
3. Iterate over the data
   1. Execute your first statement
   2. Get the last insert ID
   3. Iterate over the dansatori index
      1. Execute your second statement

Because mysqli_stmt::bind_param takes variable references, you can prepare and bind parameters before you start iterating your data.

$stmt1 = $db->prepare('INSERT INTO `rdcp_momente` (numecoregrafie, niveldans, coregraf, sectiuni, disciplina, catvarsta, nrdansatori) VALUES (?, ?, ?, ?, ?, ?, ?)');
// you might want to tweak the parameter types
$stmt1->bind_param('sssssss',
    $d['numecoregrafie'], 
    $d['niveldans'], 
    $d['coregraf'], 
    $d['sectiuni'], 
    $d['disciplina'], 
    $d['catvarsta'], 
    $d['nrdansatori']);

$stmt2 = $db->prepare('INSERT INTO `rdcp_dansatorim` (`did`, `uid`, `mid`) VALUES (?, ?, ?)');
$stmt2->bind_param('ssi', $dansator, $user->filter->id, $lastId);

foreach ($dns as $d) {
    $stmt1->execute();
    $lastId = $db->insert_id;
    foreach($d['dansatori'] as $dansator) {
        $stmt2->execute();
    }
}