根据某些条件将数组插入到MySQL的两个表中

Question

我有这个数组的数据行从SimpleXLSX使用的excel文件在php检查https://github.com/shuchkin/simplexlsx，工作很好，但事情是，我想要所有的数据插入到两个MySQL表

在表1中没问题，但在表2中就有问题了，我想把表1中最后插入的所有id和dansatori id一起插入到表2中这是一个数组如果你看一下[dansatori]

的SimpleXLSX输出请检查这个图像，看看我想要实现的:MySQL表

数组SimpleXLSX在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
)
)

我已经试过了:

$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);

只插入一个ID，而不是与最后插入的行对应的所有ID

Answer 1

1. 准备您的INSERT报表
<
2. 绑定参数/gh>
3. 遍历数据
   1. 执行第一条语句
   2. 获取最后一个插入ID
   3. 遍历dansatori索引
      1. 执行第二个语句

因为mysqli_stmt::bind_param接受变量引用，你可以在开始迭代你的数据之前准备和绑定参数。

$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();
}
}