How to insert multiple arrays into a database?

Question

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?

Just make a simple prepared statement and bind all values to it. See: http://php.net/manual/en/pdo.prepare.php — Rizier123, Oct 07 '16 at 13:36
wow - I knew there might be a solution out there. thank you. I'll report back with my updated code — tim r, Oct 07 '16 at 13:38
I think you are making this more complicated than it is. You just want to do a simple query here. Also take a look at: http://stackoverflow.com/q/6980792/3933332 to see some simple examples how to use PDO — Rizier123, Oct 07 '16 at 13:42
@Rizier123, my revision with PDO was as simple a string that I could come up with. :) — tim r, Oct 07 '16 at 17:37
You did a good start! Now two things: 1) You need to use quotes around your array keys: `$_POST[department_name]` -> `$_POST["department_name"]` 2) You used `?` as placeholders in the query, but you then tried to bind `:XY` named placeholders. So either remove all `?` and replace them with your named placeholders `:XY` in your query OR remove the keys in your array: `$loc_info`. Also see: http://php.net/manual/en/pdo.error-handling.php so you turn on error mode while coding. — Rizier123, Oct 07 '16 at 18:06
ok, the first part is obviously an easy fix. Can you elaborate on the second part? Here is where I'm confused - in either suggestion, how would the statement know that `$_POST["department_name"]` is the department? Is is based on the order that they're in? So if I remove the first ? and replace with the placeholder :department, does that mean that it will also look for the value of the first item in my array? — tim r, Oct 07 '16 at 18:19
Okay, you have 2 options. You either use `?` as placeholder OR `:XY` named placeholders. Now if you use `?`, e.g. `$sql = "INSERT INTO location_info (\`department\`, ...) VALUES (?, ?, ?)` then the order from the array elements is important. Array: `array(1, 2, 3)` then it will get replaced with: `VALUES(1, 2, 3)`. If you use named placeholders, e.g. `$sql = "INSERT INTO location_info (\`department\`, ...) VALUES(:dep, :id, :xy)` then you use the same names as keys in your array, e.g. `array(":id" => 2, ":xy" => 3, ":dep" => 1)` and it will get replaced with `VALUES(1, 2, 3)` — Rizier123, Oct 07 '16 at 18:27
Ok that's exactly what I thought - I'll go with named placeholders as this seems like the smarter approach moving forward. Back to my original question in this post - will all of this still work if `$_POST["department_name"]` is already an array? — tim r, Oct 07 '16 at 18:44
No. If one value is an array PHP will try to convert the array to a string and will fail and you will only get and see `Array` in your db. — Rizier123, Oct 07 '16 at 18:44
so now what? back to my foreach statement - still really confused on how PDO helps. I need to get the values of each of the POST values into the db but I don't know how to do this. — tim r, Oct 07 '16 at 18:55
So is each of your `$_POST` value an array? And if yes do they have the same amount of elements? — Rizier123, Oct 07 '16 at 18:56
Yes, they are all arrays (dynamic form fields). Technically, yes they'll all have the same amount of elements (some of which my be null if a user does not fill out a particular field). — tim r, Oct 07 '16 at 18:58
Okay then you almost have everything set up. You have your prepared statement with your named placeholders and then you probably can use a simple for loop like: `for($i = 0, $l = count($_POST["department_name"]); $i < $l; $i++){/*code here*/}` in the for loop you can simply execute the query with all your values, e.g. `$loc_info = array(':department' => $_POST["department_name"][$i], ...); $stmt->execute($loc_info);` — Rizier123, Oct 07 '16 at 19:01
okay so you said "you can simply execute the query with all your values" - so I only need 1 for loop for all the POST values? — tim r, Oct 07 '16 at 19:06
Yes. You *prepare* your query before the for loop and then in the for loop you simply execute the query in every iteration with the new array values. — Rizier123, Oct 07 '16 at 19:07
And also you can loop through the locations, adding value items to an array and then make one query to insert all values, thus reducing execution time interacting with the database (e.g. see [the answers here](http://stackoverflow.com/questions/452859/inserting-multiple-rows-in-a-single-sql-query) for more info... — Sᴀᴍ Onᴇᴌᴀ, Oct 07 '16 at 19:25
You need to put the array definition inside the for loop and since all POST values are arrays you want to use `$i` as second index inside the array definition, e.g. `$_POST["department_name"][$i]` for all values. — Rizier123, Oct 07 '16 at 19:40
@Rizier123, now I'm starting to feel dumb but still not working. — tim r, Oct 07 '16 at 20:13
@timr No need to feel dumb. You already mad a lot of progress! So first do you get any errors? Also if you do: `print_r($_POST["department_name"]);`, or any other POST value, you do get an array right? Also right now you don't have the `[$i]` second index for your array values in the for loop code. — Rizier123, Oct 07 '16 at 20:22
I've updated the code above so now the `$loc_info` array is inside the for loop. The wierd thing is that I get no errors and yes, when I use print_r on any of the variables, it shows the array. — tim r, Oct 07 '16 at 20:43
The only thing I see is that you still miss `[$i]` for each POST value in your array. Also you have turned on error mode? And have you checked if you caught an exception? — Rizier123, Oct 07 '16 at 20:54
I obviously don't know how to turn on error mode and the PHP docs are fuzzy on this. here is what I added after my execute line: `$PDO->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);` I get this error now "Call to a member function setAttribute() on null in..." — tim r, Oct 07 '16 at 21:08
You want to put `$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);` right after your connection(`new PDO...`). — Rizier123, Oct 07 '16 at 21:09
Ok I now have error mode on and here is the message I'm getting "error: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens" — tim r, Oct 10 '16 at 13:19
- and after looking at this, I know where the problem is. I think I can post an answer now : ) — tim r, Oct 10 '16 at 13:25
this is working well but now I'm running in a small issue. when successfully using PDO, how do I a redirect? when I tried something like `if ($db->query($sql)) { redirect_to($_SERVER["DOCUMENT_ROOT"]."/testing/tim/results.php"); }` but that doesn't work — tim r, Oct 10 '16 at 14:08
@Rizier123, I'm also seeing an issue with my form - it seems to only want to send 1 input value even though the fields are such up with something like `` — tim r, Oct 10 '16 at 15:44
@timr I think this post/comment section is getting pretty long and I think you were able to solve the initial problem from this questions. So if you want yo can self-answer your question and show how you solved the problem. After that if you have another separate question I would ask it as separate question, BUT make sure that you first do your research and you debugged your code. — Rizier123, Oct 10 '16 at 15:47

score 2 · Accepted Answer · answered Oct 10 '16 at 15:59

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

Few things: 1) First off, great job! You really took my advices in the comments and did your work. 2) You might want to look at the edit I did to your question and what exactly I improved, so you can do the same next time. 2.1) For example I trimmed the POST values assignments to just a few of them as example instead of showing like 35 assignments 2.2) I removed irrelevant code for the question like the hole redirect part 2.3) I added a simple example with two arrays each with 3 values to show a basic example of what you are trying to accomplish. — Rizier123, Oct 10 '16 at 16:41
3) Side thingy, you don't have to put brackets around your post values for a simple assignment, just `$xy = $_POST["xy"];` 4) Now lastly, do you still have a problem/question which you described in the comments under your question? — Rizier123, Oct 10 '16 at 16:41
@Rizier123, so this is strange - now I'm noticing that while this is working, its adding an extra blank row in the db every time - thoughts? — tim r, Oct 13 '16 at 14:39

How to insert multiple arrays into a database?

1 Answers1

Linked

Related