如何将多个数组插入到数据库中?

2021-12-26 00:00:00 arrays foreach php mysql

我从表单中获取一些数据作为数组.每个 $_POST 值本身就是一个数组:

I get some data from a form as arrays. Each $_POST value is an array itself:

//Example snippet from my code; I have some more data/arrays
$department_name = ($_POST[department_name]);
$participant_name = ($_POST[participant_name]);
$activity = ($_POST[activity]);
$location = ($_POST[location]);

现在我知道我可以使用 foreach 循环来遍历这些数组中的每一个并将值一个一个地插入到我的数据库中:

Now I know that I could use a foreach loop to loop over each of these arrays and insert the values one by one into my database:

foreach($department_name as $department) {
    $query  = "INSERT INTO location_info (`department`) VALUES ('{$department}')";
    $result = mysqli_query($connection, $query);
}

这似乎是我所有 35 个 POST 变量的大量代码以及服务器的大量工作.更重要的是，我将如何对齐"每条数据?由于循环，它会在每次迭代时在数据库内创建一个新行，并为所有其他列保留空白字段.

This seems like a lot of code for all my 35 POST variables as well as a lot of work for the server. More importantly how would I go about to "align" each piece of data? Because of the loop it would create a new row inside the database each iteration and leave blank fields for all other columns.

所以我搜索了如何一次遍历多个数组并找到了这个解决方案:

So I searched how I could loop through multiple arrays at once and found this solution:

<?php

    $ZZ = array('a', 'b', 'c', 'd');
    $KK = array('1', '2', '3', '4');

    foreach($ZZ as $index => $value) {
        echo $ZZ[$index] . $KK[$index];
        echo "<br/>";
    }

?>

但我真的不明白这是如何工作的以及如何将其应用于我的代码?

But I don't really understand how this works and how I can apply this to my code?

基本上作为一个例子，我有多个数组，如:

Basically as an example I have multiple arrays like:

$department_name = array("A", "B", "C");
$participant_name = array(1, 2, 3);

我需要像这样将它们插入到我的数据库中:

And I need to insert them into my database like this:

INSERT INTO location_info (`department`, `participant`) VALUES ('A', 1);
INSERT INTO location_info (`department`, `participant`) VALUES ('B', 2);
INSERT INTO location_info (`department`, `participant`) VALUES ('C', 3);

所以我想我必须使用 foreach 循环并一次循环遍历所有数组以逐行插入数据，但我看不到如何将上面找到的代码应用于我的代码?

So I think I have to use a foreach loop and loop over all arrays at once to insert the data row by row, but I fail to see how I can apply the code I found above to my code?

经过一些有用的评论后，我切换到 PDO 并取得了一些进展.

After some helpful comments I switched to PDO and made some progress.

我当前的代码如下所示.

My current code looks like the following.

连接:

<?php 

    try {
        $dsn = 'mysql:host=localhost;dbname=assessment';
        $db = new PDO($dsn,  'xxx', 'xxx');
    } catch (Exception $e) {
        $error = $e->getMessage();
    }

?>

再往下看:

try {

    $sql = "INSERT INTO location_info (`department`, `participant`, `activity`, `location`, `rec_injuries`, `rec_injuries_timeframe`, `non_rec_injuries`, `non_rec_injuries_timeframe`, `competitor`, `cost_per_pair`, `usage_rate`, `leakage`, `cost_of_productivity`, `non_rec_impact`, `non_rec_sprain`, `non_rec_puncture`, `non_rec_dermatitis`, `non_rec_infection`, `non_rec_burns`, `non_rec_cuts`, `rec_impact`, `rec_sprain`, `rec_puncture`, `rec_dermatitis`, `rec_infection`, `rec_burns`, `rec_cuts`, `condition`, `general_id`)
        VALUES (:department, :participant, :activity, :location, :rec_injuries, :rec_injuries_timeframe, :non_rec_injuries_timeframe, :competitor, :cost_per_pair, :usage_rate, :leakage, :cost_of_productivity,:non_rec_impact, :non_rec_sprain, :non_rec_puncture, :non_rec_dermatitis, :non_rec_infection, :non_rec_burns, :non_rec_cuts, :rec_impact, :rec_sprain, :rec_puncture, :rec_dermatitis, :rec_infection, :rec_burns, :rec_cuts, :condition, :general_id)";

    $stmt = $db->prepare($sql);

for($i = 0, $l = count($_POST["department_name"]); $i < $l; $i++) {

  $loc_info = array(':department' => $_POST["department_name"][$i],
                    ':participant' => $_POST["participant_name"][$i],
                    ':activity' => $_POST["activity"][$i],
                    ':location' => $_POST["location"][$i],
                    ':rec_injuries' => $_POST["injuries"][$i],
                    ':rec_injuries_timeframe' => $_POST["injury_time_frame"][$i],
                    ':non_rec_injuries' => $_POST["non_rec_injuries"][$i],
                    ':non_rec_injuries_timeframe' => $_POST["non_rec_injury_timeframe"][$i],
                    ':competitor' => $_POST["competitor"][$i],
                    ':cost_per_pair' => $_POST["cost_per_pair"][$i],
                    ':usage_rate' => $_POST["usage_rate"][$i],
                    ':leakage' => $_POST["leakage"][$i],
                    ':cost_of_productivity' => $_POST["cost_of_productivity"][$i],
                    ':non_rec_impact' => $_POST["non_rec_impact"][$i],
                    ':non_rec_sprain' => $_POST["non_rec_sprain"][$i],
                    ':non_rec_puncture' => $_POST["non_rec_puncture"][$i],
                    ':non_rec_dermatitis' => $_POST["non_rec_dermatitis"][$i],
                    ':non_rec_infection' => $_POST["non_rec_infection"][$i],
                    ':non_rec_burns' => $_POST["non_rec_burns"][$i],
                    ':non_rec_cuts' => $_POST["non_rec_cuts"][$i],
                    ':rec_impact' => $_POST["impact"][$i],
                    ':rec_sprain' => $_POST["sprain"][$i],
                    ':rec_puncture' => $_POST["puncture"][$i],
                    ':rec_dermatitis' => $_POST["dermatitis"][$i],
                    ':rec_infection' => $_POST["infection"][$i],
                    ':rec_burns' => $_POST["burns"][$i],
                    ':rec_cuts' => $_POST["cuts"][$i],
                    ':condition' => $_POST["condition"][$i],
                    ':general_id' => $_POST["id"][$i]
            );

    $stmt->execute($loc_info);      
}

} catch (Exception $e) {
    $error = $e->getMessage();
}

但这仍然不起作用.

想法?数组里面不能放数组吗?

Thoughts? Can I not put an array inside an array?

推荐答案

经过几次尝试和错误以及@Rizier123 的一些帮助后，好的，这里是答案:

Ok after a few trial and errors and some help from @Rizier123, here is the answer:

表单页面上的html

为了清楚起见，我试图弄清楚如何将几个数据数组添加到我的数据库中.所以在动态表单页面上，我的输入类似于:

For clarity sake, I was trying to figure out how add several arrays of data to my db. So on dynamic form page, I have inputs similar to:

<p>Location: <input type='text' name='location[]'  > </p>

处理表单并将数据输入数据库

首先，我从 mysqli 切换到 PDO，然后我用以下代码运行:

First, I switched from mysqli to PDO, then I ran with the following code:

try {
        $sql = "INSERT INTO location_info (`department`, `participant`, `activity`, `location`, `rec_injuries`, `rec_injuries_timeframe`, `non_rec_injuries`, `non_rec_injuries_timeframe`, `competitor`, `cost_per_pair`, `usage_rate`, `leakage`, `cost_of_productivity`, `non_rec_impact`, `non_rec_sprain`, `non_rec_puncture`, `non_rec_dermatitis`, `non_rec_infection`, `non_rec_burns`, `non_rec_cuts`, `rec_impact`, `rec_sprain`, `rec_puncture`, `rec_dermatitis`, `rec_infection`, `rec_burns`, `rec_cuts`, `condition`, `general_id`)
        VALUES (:department, :participant, :activity, :location, :rec_injuries, :rec_injuries_timeframe, :non_rec_injuries, :non_rec_injuries_timeframe, :competitor, :cost_per_pair, :usage_rate, :leakage, :cost_of_productivity,:non_rec_impact, :non_rec_sprain, :non_rec_puncture, :non_rec_dermatitis, :non_rec_infection, :non_rec_burns, :non_rec_cuts, :rec_impact, :rec_sprain, :rec_puncture, :rec_dermatitis, :rec_infection, :rec_burns, :rec_cuts, :condition, '{$id}')";

        $stmt = $db->prepare($sql);

for($i = 0, $l = count($_POST["department_name"]); $i < $l; $i++) { 

    $loc_info = array(':department' => $_POST["department_name"][$i],
                        ':participant' => $_POST["participant_name"][$i],
                        ':activity' => $_POST["activity"][$i],
                        ':location' => $_POST["location"][$i],
                        ':rec_injuries' => $_POST["injuries"][$i],
                        ':rec_injuries_timeframe' => $_POST["injury_time_frame"][$i],
                        ':non_rec_injuries' => $_POST["non_rec_injuries"][$i],
                        ':non_rec_injuries_timeframe' => $_POST["non_rec_injury_timeframe"][$i],
                        ':competitor' => $_POST["competitor"][$i],
                        ':cost_per_pair' => $_POST["cost_per_pair"][$i],
                        ':usage_rate' => $_POST["usage_rate"][$i],
                        ':leakage' => $_POST["leakage"][$i],
                        ':cost_of_productivity' => $_POST["cost_of_productivity"][$i],
                        ':non_rec_impact' => $_POST["non_rec_impact"][$i],
                        ':non_rec_sprain' => $_POST["non_rec_sprain"][$i],
                        ':non_rec_puncture' => $_POST["non_rec_puncture"][$i],
                        ':non_rec_dermatitis' => $_POST["non_rec_dermatitis"][$i],
                        ':non_rec_infection' => $_POST["non_rec_infection"][$i],
                        ':non_rec_burns' => $_POST["non_rec_burns"][$i],
                        ':non_rec_cuts' => $_POST["non_rec_cuts"][$i],
                        ':rec_impact' => $_POST["impact"][$i],
                        ':rec_sprain' => $_POST["sprain"][$i],
                        ':rec_puncture' => $_POST["puncture"][$i],
                        ':rec_dermatitis' => $_POST["dermatitis"][$i],
                        ':rec_infection' => $_POST["infection"][$i],
                        ':rec_burns' => $_POST["burns"][$i],
                        ':rec_cuts' => $_POST["cuts"][$i],
                        ':condition' => $_POST["condition"][$i] );

$stmt->execute($loc_info);
}

相关文章