Insert data using multidimensional array from multiple fields

Question

I have a form which is actually a table, the table (form) is dynamic, thus the table can consist of anything from 10 to 300 rows,

The form table:

enter image description here

Following is the code for the input form table:

include("x.xxx");
$cxn = mysqli_connect($host,$user,$password,$dbname);
$query = "SELECT * FROM `inspjc` ORDER BY `scaffreq_id`";
$result = mysqli_query($cxn,$query)
    or die ("Couldn't execute query.");     

echo "<table align='center'><br>
<tr bgcolor='#8DB4E3'>
 <th>X</th>
 <th>Scaffold Req No</th>
 <th>Elevation</th>
 <th class='vertical'>Foundations</th>
 <th class='vertical'>Ledgers</th>
 <th class='vertical'>Face Brace</th>
 <th class='vertical'>Plan Brace</th>
 <th class='vertical'>Platforms</th>
 <th class='vertical'>Mobiles</th>
 <th class='vertical'>Uprights</th>
 <th class='vertical'>Transoms</th>
 <th class='vertical'>Transverse Braces</th>
 <th class='vertical'>Ties</th>
 <th class='vertical'>Safe Access</th>
 <th class='vertical'>Signs</th>
 <th>If Not Inspected<br>Supply a Reason</th>
</tr>";
while($row = mysqli_fetch_assoc($result))
{   
    extract($row);

    echo "<tr>\n
    <td><input type='checkbox' checked='checked' name='scaffreq_id[]' value='$scaffreq_id' /></td>\n
        <td><center>$scaffreq_id</center></td>\n
        <td><center>$level m</center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp1[]' value='y' /></center></td>\n            
        <td><center><input type='checkbox' checked='checked' name='insp2[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp3[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp4[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp5[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp6[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp7[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp8[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp9[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp10[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp11[]' value='y' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='insp12[]' value='y' /></center></td>\n
        <td><center><p><input type='text' name='insp_reason[]' maxlength='255' size='45' value='$insp_reason'></p></center></td>\n
        </tr>\n";
}
echo "</table><br>";
?>

The desired output looks like this (simplified):

x   ID    a    b    c    d    e    f    g
---------------------------------------------------
1   365   1    1    0    0    0    1    North Bay
1   211   1    0    1    1    1    1    South Bay
0   237   0    1    1    1    1    0    Boiler

x represents an input type of type='checkbox' and ID represents its ID key. On submit, I would like to write the data to a database.

I have tried a few loop option, but I need the loop to run from php and not loop through my query (this takes too long), especially with 300 rows selected.

The basic outlay of the php code:

$ID = $_POST[ID];
$a = $_POST[a];
$b = $_POST[b];
$c = $_POST[c];
$d = $_POST[d];
$e = $_POST[e];
$f = $_POST[f];
$g = $_POST[g];

$userData = array();

foreach ($ID as $newid)
    {
    $userData[] = "('" . $newid['ID'] . "', 
        '" . $newid['a'] . "', 
        '" . $newid['b'] . "', 
        '" . $newid['c'] . "', 
        '" . $newid['d'] . "', 
        '" . $newid['e'] . "', 
        '" . $newid['f'] . "', 
        '" . $newid['g'] . "')";
    }

$query = "INSERT INTO `inspect` (`ID`,`a`,`b`,`c`,`d`,`e`,`f`,`g`) VALUES ";
$query .= implode(',',$userData);

But, echo'ing the query only gives me "('0',0','0','0','0','0','0','0'),('0'..." yet there is exactly the quantity of data (within brackets) as the selected ID fields.

Shouldn't it be `$_POST['ID']` instead of `$_POST[ID]`. Also, your code is strange, you're assigning values to separate variables and then seem to expect them to be in an array right after. — Osuwariboy, Jul 20 '15 at 14:26
@Osuwariboy it shouldn't make a difference, both should work. None the less, I have echo'd each $_POST, they all contain the correct data... — JJ Morgan, Jul 20 '15 at 14:31
Ok, so `$newid` is actually an array containing a, b, c, d... and so on. That wasn't immediately clear in your code. Still, why then are you assigning the values before your loop and not using the variables? — Osuwariboy, Jul 20 '15 at 14:36
`foreach ($ID as $key => $newid) { $userData[] = "('" . $newid . "', '" . $a[$key] . $b[$key]....` — splash58, Jul 20 '15 at 14:38
@splash58, the loop you gave helped to show the actual id of each row, but all of a-g is set to one, regardless of what was selected... — JJ Morgan, Jul 22 '15 at 08:49
This looks very insecure and [vulnurable to SQL injections](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). Use [PDO prepared statements](http://php.net/manual/de/pdo.prepared-statements.php)! — Gottlieb Notschnabel, Jul 22 '15 at 09:08

score 0 · Answer 1 · edited May 23 '17 at 11:44

UPDATE

(Wow, that's a tough one): You're defining all your input fields in your HTML form as arrays. You can either remove those [] which makes them arrays or you can use name=insp[] on your insp-checkboxes. Then output your submission with var_dump($_POST) and you see how you can parse the array.

Or create a nested array. Something like this will help:

while($row = mysqli_fetch_assoc($result)) {

    extract($row);

    echo "<tr>\n
    <td><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][id]' /></td>\n
        <td><center>{$scaffreq_id}</center></td>\n
        <td><center>{$level} m</center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][a]' /></center></td>\n            
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][b]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][c]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][d]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][e]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][f]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][g]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][h]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][i]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][j]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][k]' /></center></td>\n
        <td><center><input type='checkbox' checked='checked' name='items[{$scaffreq_id}][insp][l]' /></center></td>\n
        <td><center><p><input type='text' name='items[{$scaffreq_id}][reason]' maxlength='255' size='45' value='Reason{$insp_reason}'></p></center></td>\n
        </tr>\n";
}

It's not easy to help without seeing the HTML form code. But as it seems to me, $ID ist most likely not an array. So using foreach($ID as ...) is not good. Instead, build your string like this:

$query = "INSERT INTO `inspect` (`ID`,`a`,`b`,`c`,`d`,`e`,`f`,`g`) VALUES ";
$query .= "({$ID}, {$a}, {$b}, {$c}, {$d}, {$e}, {$f}, {$g})";

Warning:
Be aware that your code is insecure and vulnurable to SQL injections (see this SO Q&A). Use PDO prepared statements instead of mysql() functions!

Thank you for your input, especially about PDO prepared statements. When I echo the above query, the output looks like this: INSERT INTO `inspect` (`ID`,`a`,`b`,`c`,`d`,`e`,`f`,`g`) VALUES (Array,Array,Array,Array,Array,Array,Array,Array,) — JJ Morgan, Jul 22 '15 at 12:37

Insert data using multidimensional array from multiple fields

1 Answers1

Linked