Loop through rows and INSERT multiple rows into MySQL

Question

In a nutshell, I'm looping and trying to INSERT all the rows but it only inserts the last row for as many times that it loops. So, for two loops, it will loop through twice, and insert the last row twice (not inserting the first row at all).

I'm using this as the row / items counter:

$countRecords;

This is a basic counter that tells me how many rows are included in the previous invoice. This works. In my example, it displays "2" as two items (rows) are in the previous invoice.

I'm using the first part of the MySQL outside of the loop:

// the MySQL statement out of the loop
$stmt="INSERT INTO o70vm_invoices_items
    (`id`, `invoice_id`, `name`, `desc`, `value`, `amount`, `discount`, `ordering` ) VALUES ";

I'm then running the loop:

// loop through the rows
for($rowCounter = 0; $rowCounter < $countRecords; $rowCounter++) 
    {


    // assign variables based on each loop iteration
    $invoiceID = htmlentities($_POST['invoice_id'], ENT_QUOTES);
    $program = htmlentities($_POST['name'], ENT_QUOTES);
    $forWeek = htmlentities($_POST['desc'], ENT_QUOTES);
    $value = htmlentities($_POST['value'], ENT_QUOTES);
    $qty = htmlentities($_POST['amount'], ENT_QUOTES);


    // put the VALUES array string for each row as such
    $ValuesAddToQuery[] ="(NULL, '$invoiceID', '$program', '$forWeek', '$value', '$qty','0.00','1')";

    }

After the loop, I implode the $ValuesAddToQuery array to insert as such:

// add all the values into the statement
$stmt .= implode(',',$ValuesAddToQuery);

echo $stmt;

// execute statement
//mysql_query($stmt) or exit(mysql_error());

As I said, this loop only inserts the last row the same number of times as it loops through the code.

My var_dump shows that I have the last row added twice in the array also.

So much appreciate any help on this.

For an uncut version of my loop, here it is:

                $stmt="INSERT INTO o70vm_invoices_items 
                (`id`, `invoice_id`, `name`, `desc`, `value`, `amount`,`discount`,`ordering` ) VALUES ";

                // loop through the rows
                for($rowCounter = 0; $rowCounter < $countRecords; $rowCounter++) 
                    {


                    // assign variables based on each loop iteration
                    $invoiceID = htmlentities($_POST['invoice_id'], ENT_QUOTES);
                    $program = htmlentities($_POST['name'], ENT_QUOTES);
                    $forWeek = htmlentities($_POST['desc'], ENT_QUOTES);
                    $value = htmlentities($_POST['value'], ENT_QUOTES);
                    $qty = htmlentities($_POST['amount'], ENT_QUOTES);


                    // put the VALUES array string for each row as such
                    $ValuesAddToQuery[] ="(NULL, '$invoiceID', '$program', '$forWeek', '$value', '$qty','0.00','1')";


                    }

                    // add all the values into the statement
                    $stmt .= implode(',',$ValuesAddToQuery);

                    echo $stmt."<br /><br />";

                    var_dump($ValuesAddToQuery);

                    // execute statement
                    //mysql_query($stmt) or exit(mysql_error());

For a little more detail, I'm getting the data from the previous invoice here:

    <td>
    <input type="number" title="'.$row->invoice_id_on_items.'" name="invoice_id" size="2" id="invoice_id" value="' . $row->invoice_id_on_items. '" />
</td>

<td>(<a href="getIndItems.php?id=' .$row->item_id. '" target="_blank">EDIT</a>)<br>'.$row->item_id.'</td>';

$_SESSION['itemIDSelected']=$row->item_id;

echo'

<input type="hidden" title="'.$row->item_id.'" name="id" size="13" id="id" value="'.$row->item_id. '" />

<td>
    <input type="text" title="'.$row->forweek.'" name="desc" size="15" id="desc" value="' . $row->forweek. '" />
</td>
<td>
    <input type="text" title="'.$row->program.'" name="name" size="50" id="name" value="' . $row->program. '" />
</td>
<td>
    <input type="number" title="'.$row->fee.'" name="value" size="3" id="value" value="' . $row->fee. '" />
</td>
<td>
    <input type="number" title="'.$row->qty.'" name="amount" size="3" id="amount" value="' . $row->qty. '" />
</td>
';
$Fee = floatval($row->fee);
$Qty = floatval($row->qty);
$ItemFee=$Fee*$Qty;
echo '
<td>
    <input type="text" title="'.$ItemFee.'" name="total_fee" size="3" id="total_fee" value="' . $ItemFee. '" />
</td>

Then, I'm using the already illustrated above loop code to get the data. I am able to retrieve the past invoice data without issue but wanted to include it in my question so you would have all the necessary information to provide guidance.

// add all the values into the statement $stmt .= implode(',',$ValuesAddToQuery); echo $stmt; // execute statement //mysql_query($stmt) or exit(mysql_error()); put these lines inside your loop — Happy Coding
– Happy Coding, Commented Oct 30, 2015 at 12:38
Thanks. If I do that, It seems it adds the last row once, then loops again and adds the last row twice . . . so, three rows added of the last row. Strange. — kentrenholm
– kentrenholm, Commented Oct 30, 2015 at 12:48
Do you get your data from multiple inputs?(multiple rows inside your table) — Silencio
– Silencio, Commented Oct 30, 2015 at 12:55

lc. · Accepted Answer · 2015-10-30 12:59:37Z

2

This:

$invoiceID = htmlentities($_POST['invoice_id'], ENT_QUOTES);
$program = htmlentities($_POST['name'], ENT_QUOTES);
$forWeek = htmlentities($_POST['desc'], ENT_QUOTES);
$value = htmlentities($_POST['value'], ENT_QUOTES);
$qty = htmlentities($_POST['amount'], ENT_QUOTES);

You're getting the same values from $_POST each time through the loop. (You are looping through this code multiple times, but each time you are getting "invoice_id", "name", "desc", etc.)

If you are getting multiple values from the same form, each must have a different name which would then be passed via POST to your script.

For example you could add a number after each field and use the loop counter variable.

HTML:

<input name="invoice_id0" ... />
<input name="name0" ... />
<input name="desc0" ... />

<input name="invoice_id1" ... />
<input name="name1" ... />
<input name="desc1" ... />

PHP - in your loop:

$invoiceID = htmlentities($_POST['invoice_id' . $rowCounter], ENT_QUOTES);
$program = htmlentities($_POST['name' . $rowCounter], ENT_QUOTES);
$forWeek = htmlentities($_POST['desc' . $rowCounter], ENT_QUOTES);

But in reality, you really, really, really shouldn't inject user-supplied values into a SQL query like you are doing. Use parameters instead. (The logic of getting the appropriate values for each loop iteration from above still applies though. Just don't concatenate it into a VALUES statement.)

answered Oct 30, 2015 at 12:59

lc.

117k21 gold badges163 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kentrenholm Over a year ago

Thanks. That makes a lot of sense. I made the changes. It was showing empty values for the first row inserted and the first value of rows for the second values inputted. I just then added the value of "1 to the input counter, and it works. On the form code, I had my loop adding before the data, not after. Thanks again.

Collectives™ on Stack Overflow

Loop through rows and INSERT multiple rows into MySQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related