Insert into MySQL from array into two tables at once based on some conditions

Question

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

Warning: You are wide open to SQL Injections and should really use parameterised prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input, especially that which comes from the client side. Escaping is not enough — Phil
– Phil, Commented Apr 21, 2022 at 3:59

Phil · Accepted Answer · 2022-04-21 04:58:59Z

Prepare your INSERT statements
Bind parameters
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();
    }
}

Collectives™ on Stack Overflow

Insert into MySQL from array into two tables at once based on some conditions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related