Insert Multiple rows using implode

Question

I am having a hard time getting my code to work. I grasp the basic concept but can't get it to work. I have a form with multiple rows:

<form action="multiscript.php" method="post" id="form">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><input type="text" name="field_PK[]" id="field_PK" /></td>
<td><input type="text" name="sql_fk[]" id="sql_fk" /></td>
<td><input type="text" name="account_fk[]" id="account_fk" /></td>
<td><input type="text" name="field_code[]" id="field_code" /></td>
<td><input type="text" name="field_name[]" id="field_name" /></td>
<td><input type="text" name="field_px[]" id="field_px" /></td>
<td><input type="text" name="field_order[]" id="field_order" /></td>
</tr>
<tr>
<td><input type="text" name="field_PK[]" id="field_PK" /></td>
<td><input type="text" name="sql_fk[]" id="sql_fk" /></td>
<td><input type="text" name="account_fk[]" id="account_fk" /></td>
<td><input type="text" name="field_code[]" id="field_code" /></td>
<td><input type="text" name="field_name[]" id="field_name" /></td>
<td><input type="text" name="field_px[]" id="field_px" /></td>
<td><input type="text" name="field_order[]" id="field_order" /></td>
</tr>
</table>
<input type="submit" name="button" id="button" value="Submit" />
</form>

I then have a script to process the form that I wish to insert multiple rows into my mysql database. My script looks like this:

<?php 
$values = array();?>
<?php
foreach( $_POST as $row ) { 
$values[] =  "(" . $row['field_PK'] . "," . $row['sql_fk'] . "," . $row['account_fk']. "," . $row ['field_code']. "," . $row['field_name']. "," . $row['field_px']. "," . $row['field_order'].")";
}
if( !empty($values) ) {
$query = "INSERT INTO `Table_fields` (field_PK, sql_fk, account_fk, field_code, field_name, field_px, field_order) VALUES ". implode(',',$values);

mysql_query($query);
}
?>

I know there is something wrong because when i "print" the query i get this:

INSERT INTO `Table_fields` (field_PK, sql_fk, account_fk, field_code, field_name, field_px, field_order) VALUES (,,,,,,),(,,,,,,),(,,,,,,),(,,,,,,),(,,,,,,),(,,,,,,),(,,,,,,),(S,S,S,S,S,S,S) 

Can someone please look over my form and script and tell me what I am doing wrong and why it is not inserting the mutliple rows into my database using the implode method. Thank you.

Answer 1

Your $_POST superglobal actually looks like this:

Array
(
    [field_PK] => Array
        (
            [0] => value1
            [1] => value2
        )
    [sql_fk] => ...
    [account_fk] => ...
    [field_code] => ...
    [field_name] => ...
    [field_px] => ...
    [field_order] => ...
    [button] => Submit
)

When you do foreach( $_POST as $row ) you are looping through each field instead of each row of fields as you expected. As such, you get mostly blanks (I'm guessing you submitted a blank form to test it) and then the 'S' from Submit.

You can do it the way you want to, but you need to transpose your $_POST values first. This answer has a very simple transpose function. (He calls it "flipDiagonally", but it's a typical transpostion).

Using the function from the linked answer, you could do like this:

$transposed = transpose( $_POST );

foreach( $transposed as $index => $row ) { 
    $values[] = "(" . $row['field_PK'] . "," . $row['sql_fk'] . "," . $row['account_fk']. "," . $row ['field_code']. "," . $row['field_name']. "," . $row['field_px']. "," . $row['field_order'].")";
}

Answer 2

Try

foreach($_POST as $index => $row)

opposed to

foreach($_POST as $values)

Of course, don't forget the usual story about SQL injection attacks and similar.

Edit:

seeing that all the fields are text type, regardless of whether they're empty or not - they'll be sent. They also share the same indexes, so you can type it in a more elegant manner:

foreach($_POST['field_pk'] as $index => $values)
{
    $values[] =  "(" . $_POST[$index]['field_PK'] . "," . $_POST[$index]['sql_fk'] . "," . $_POST[$index]['account_fk']. "," . $_POST[$index]['field_code']. "," . $_POST[$index]['field_name']. "," . $_POST[$index]['field_px']. "," . $_POST[$index]['field_order'].")";

}

I must admit, not reading properly led to me giving out the wrong answer. Same SQL-injection prevention rule applies, make sure your input is filtered!

Answer 3

You don't want to iterate over all POST variables. Instead you want to iterate over the $_POST["field_PK"] arrays.