如何插入多个数组到数据库中？ [英] How to insert multiple arrays into a database?
问题描述
$ _ POST 值都是一个数组本身： //示例代码片段从我的代码;我有更多的数据/数组
$ department_name =（$ _POST [department_name]）;
$ participant_name =（$ _POST [participant_name]）;
$ activity =（$ _POST [activity]）;
$ location =（$ _POST [location]）;
现在我知道我可以使用foreach循环遍历每个数组，并插入值一个一个地进入我的数据库：
foreach（$ department_name as $ department）{
$ query =INSERT INTO location_info（`department`）VALUES（'{$ department}'）;
$ result = mysqli_query（$ connection，$ query）;
$ b $ p
$ b 这对我所有的35个POST变量来说都是一个很好的代码，为服务器做了很多工作。更重要的是，我将如何去对齐每一块数据？由于循环，它会在数据库中创建一个新的行，每次迭代，并留下所有其他列的空白字段。
所以我搜索了如何循环多个数组一旦找到这个解决方案：
<？php
$ ZZ = array（'a' ，'b'，'c'，'d'）;
$ KK = array（'1'，'2'，'3'，'4'）;
foreach（$ ZZ as $ index => $ value）{
echo $ ZZ [$ index]。 $ KK [$指数];
echo< br />;
}
？>
但我真的不明白这是如何工作的，以及如何将其应用于我的代码？ / b>
基本上作为一个例子，我有多个数组：
$ department_name =数组（A，B，C）;
$ participant_name = array（1，2，3）;
我需要像这样将它们插入到我的数据库中：
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循环并一次遍历所有数组来插入数据逐行，但我没有看到我怎么可以将我发现的代码应用到我的代码？
之后一些有用的意见，我切换到PDO，并取得了一些进展。
我目前的代码如下所示。
Connection：
<？php
try {
$ dsn ='mysql：主机=本地主机; DBNAME =评估';
$ db = new PDO（$ dsn，'xxx'，'xxx'）;
} catch（Exception $ e）{
$ error = $ e-> getMessage（）;
}
？>
再往下看：
<$ p $ 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`，`leak`，`cost_of_productivity`，`non_rec_impact`，`non_rec_sprain`，`non_rec_puncture`，`non_rec_dermatitis`，`non_rec_infection`，`non_rec_in ``````，```````````````````，```````````，````````` ：参与者：活动：位置：rec_injuries：：rec_injuries_timeframe：：non_rec_injuries_timeframe：：竞争者：：cost_per_pair：：usage_rate：：泄漏，：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_p ：rec_dermatitis，：rec_infection，：rec_burns，：rec_cuts，：condition，：general_id）;
$ stmt = $ db-> prepare（$ sql）; $
（$ i = 0，$ l = count（$ _ POST [department_name]）; $ i <$ l; $ i ++）{
$ loc_info = array（'：department'=> $ _POST [department_name] [$ i]，
'：participant'=> $ _POST [participant_name] [$ i]，
' ：$'$'，$ b $：$ _POST [i，$ b $：$'$']，$ b $：$ _POST [location > $ _POST [injury] [$ 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 [leak] [$ 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 [感染] [$ 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（）;
}
但是这仍然不起作用。
想法？我可以不把一个数组放在一个数组中吗？
确定经过几次试验和错误以及@ Rizier123的帮助，这里是答案：
表单页面上的html
为了清楚起见，我试图找出如何添加几个数据数组到我的分贝。所以在动态表单页面上，我有类似于以下内容的输入：
< p>位置：< input type ='text' name ='location []'> < / p为H.
处理表单并将数据输入到数据库
首先，我从mysqli切换到PDO，然后运行以下代码：
<$ c $ INSERT INTO location_info（`department`，`participant`，`activity`，`location`，`rec_injuries`，`rec_injuries_timeframe`，`non_rec_injuries`，`non_rec_injuries_timeframe`，`competitor'）{try {
$ sql = cost_per_pair，usage_rate，leak，cost_ofproductivity，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， ：rec_injuries，：rec_injuries_timeframe，：non_rec_injuries，：non_rec_injuries_timeframe，：：竞争者，：cost_per_pair，：usage_rate，：泄漏，：cost_of_productivity，：no n_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）; $
（$ i = 0，$ l = count（$ _ POST [department_name]）; $ i <$ l; $ i ++）{
$ loc_info = array（'：department'=> $ _POST [department_name] [$ i]，
'：participant'=> $ _POST [participant_name] [$ i]，
' ：$'$'，$ b $：$ _POST [i，$ b $：$'$']，$ b $：$ _POST [location > $ _POST [injury] [$ 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 [leak] [$ 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 [感染] [$ i]，
'：rec_burns'=> $ _POST [burns] [$ i]，
'：rec_cuts'=> $ _POST [cuts] [$ i]，
'：condition'=> $ _POST [condition] [$ i]）;
$ stmt-> execute（$ loc_info）;
}
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]);
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);
}
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);
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?
After some helpful comments I switched to PDO and made some progress.
My current code looks like the following.
Connection:
<?php
try {
$dsn = 'mysql:host=localhost;dbname=assessment';
$db = new PDO($dsn, 'xxx', 'xxx');
} catch (Exception $e) {
$error = $e->getMessage();
}
?>
Further down I have:
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();
}
But this is still not working.
Thoughts? Can I not put an array inside an array?
Ok after a few trial and errors and some help from @Rizier123, here is the answer:
html on the form page
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>
Processing the Form and Inputting the Data to the DB
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);
}
这篇关于如何插入多个数组到数据库中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
