Combine two array values for insert using SQL

Question

I'm using PHP to loop through a list of books and create a form to add them to their own list with a different status, 1 = Read, 2 = Reading, 3 = To-Read.

Each book has a unique ID. So I end up with an array like this using a foreach loop

foreach($_POST as $key => $val){
  $count++;
  $formElements[] = $val; 
}

Array ( [0] => 6748 [1] => 1 [2] => 6759 [3] => 2 [4] => 87804 [5] => 3 [6] => [7] => submit )

So [0] and [1] are the book_ID and status. Same with [2] and [3], and [4] and [5].

How can I concat those together or something so I can insert them into an SQL table?

Showing my form, sorry guys!

<table>
          <thead>
            <tr><th></th>
            <th>Title</th>
            <th>Author</th>
          </tr></thead><tbody><tr>
            <td><input type="checkbox" value="6748" id="bookID" name="bookID1"></td>
            <td>A Supposedly Fun Thing I'll Never Do Again:  Essays and Arguments</td>
            <td>David Foster Wallace</td>
            <td><select name="readStatus1">
              <option></option>
              <option value="1">Read</option>
              <option value="2">Reading</option>
              <option value="3">To-Read</option>
            </select></td>

          </tr><tr>
            <td><input type="checkbox" value="6759" id="bookID" name="bookID2"></td>
            <td>Infinite Jest</td>
            <td>David Foster Wallace</td>
            <td><select name="readStatus2">
              <option></option>
              <option value="1">Read</option>
              <option value="2">Reading</option>
              <option value="3">To-Read</option>
            </select></td>

          </tr><tr>
            <td><input type="checkbox" value="87804" id="bookID" name="bookID3"></td>
            <td>Yes Man</td>
            <td>Danny Wallace</td>
            <td><select name="readStatus3">
              <option></option>
              <option value="1">Read</option>
              <option value="2">Reading</option>
              <option value="3">To-Read</option>
            </select></td>

          </tr><tr>
            <td><input type="checkbox" value="5986375" id="bookID" name="bookID4"></td>
            <td>This Is Water: Some Thoughts, Delivered on a Significant Occasion, about Living a Compassionate Life</td>
            <td>David Foster Wallace</td>
            <td><select name="readStatus4">
              <option></option>
              <option value="1">Read</option>
              <option value="2">Reading</option>
              <option value="3">To-Read</option>
            </select></td>

          </tr></tbody></table><input type="submit" value="submit" name="submit">

To me it looks like you could solve that with better naming of the html from inputs. If you make them arrays they would be grouped together already. Another possibility would be to group them in your for each to "books". Please show the html form and/or what the fields are named (= each `$key`) — Jeff, Apr 08 '19 at 20:44
Listen to @Jeff. Show your form inputs, there's a better way.. — AbraCadaver, Apr 08 '19 at 20:46
You would want an insertMulti function. Take a look at this function here https://stackoverflow.com/a/2098689/5066625 — Miroslav Glamuzina, Apr 08 '19 at 20:46

score 0 · Answer 1 · answered Apr 08 '19 at 20:47

0

What I recommend is inside your form you do something like so:

<input type="text" name="bookReadStatus[bookIDHERE]">
<input type="text" name="bookReadStatus[bookIDHERE]">

Then using this a super easy way to get it into the database is to implode it in.

https://www.php.net/manual/en/function.implode.php

implode(',',$_POST);

answered Apr 08 '19 at 20:47

Lulceltech

1,662
11
22

that gives me nothing. Though when I var_dump($_POST) I see it as an array `array(9) { ["bookID1"]=> string(4) "6748" ["readStatus1"]=> string(1) "1" ["bookID2"]=> string(4) "6759" ["readStatus2"]=> string(1) "2" ["bookID3"]=> string(5) "87804" ["readStatus3"]=> string(1) "3" ["bookID4"]=> string(7) "5986375" ["readStatus4"]=> string(1) "3" ["submit"]=> string(6) "submit" }` – Michael H Apr 08 '19 at 21:03
when you see `["bookID1"]=>..` in `$_POST` you haven't changed the name as @TimHinz suggested – Jeff Apr 08 '19 at 21:07

score 0 · Answer 2 · answered Apr 08 '19 at 20:51

do something in the form or convert to array with key values

Array ( [0] => 6748 [1] => 1 [2] => 6759 [3] => 2 [4] => 87804 [5] => 3 [6] => [7] => submit )

// you could use foreach( $a as $v)
$ma = [
   [
       'id' => $a[0],
       'status' => $a[1]
   ],
   /* etc */

]

// use json_encode - use you pdo syntax here
'insert into mysql( "store field") values( "' + json_encode( $ma) +'")';

score 0 · Answer 3 · answered Apr 08 '19 at 21:02

My suggestion would be to make the books arrays already in html:

                                                         <!-- here -->
<td><input type="checkbox" value="6748" id="bookID" name="bookID[]"></td>
<td>A Supposedly Fun Thing I'll Never Do Again:  Essays and Arguments</td>
<td>David Foster Wallace</td>
                <!-- and here: -->
<td><select name="readStatus[]">
          <option></option>
          <option value="1">Read</option>
          <option value="2">Reading</option>
          <option value="3">To-Read</option>
</select></td>
<!-- repeat for other books coming from db -->

Then you can access those arrays and itererate them in php:

foreach($_POST['bookID'] as $index => $id) {
     // use the bookID's index to get the status:
     $status = $_POST['readStatus'][$index];
     // now use $id and $status to insert into database.
     // either directly here in the loop, or build a new dedicated array:
     $books[] = ["id" => $id, "status" => $status];
     // and use that later to do your inserts.
}

Alternatively you can also use @TimHinz' approach by using the ID as index

Combine two array values for insert using SQL

3 Answers3