Using for() to update multiple rows in mysql database

Question

I am trying to update multiple rows in a database. I have the total number of rows in my table counted and bound to a variable:

$result = '32'

I then attempt to query each row:

for ($x = 0; $x <= $result; $x++)
{
    if ($update = $db -> prepare("UPDATE substrates SET substrate_name = ?, substrate_desc = ?, substrate_base = ?, substrate_tax = ? WHERE substrate_id = ?"))
    {
        $substrate_name = $_POST['substrate_name'];
        $substrate_desc = $_POST['substrate_desc'];
        $substrate_base = $_POST['substrate_base'];
        $substrate_tax  = $_POST['substrate_tax'];
        $substrate_id   = $x;
        $update -> bind_param('sssss', $substrate_name, $substrate_desc, $substrate_base, $substrate_tax, $substrate_id);
        if ($update -> execute() == true) {
            // Success stuff...
        } 
        else {
            // Error stuff...
        }
        $update -> close();
    }
}

My problem is when I run this script, each row in the table is filled with the last row edited. For example:

AAA | Aaaaa | 1.00 | 6.35
BBB | Bbbbb | 2.00 | 6.35
CCC | Ccccc | 3.00 | 6.35

Becomes:

CCC | Ccccc | 3.00 | 6.35
CCC | Ccccc | 3.00 | 6.35
CCC | Ccccc | 3.00 | 6.35

How can I fix this script so it will update each row individually?

What do you mean they each become the last row edited? You set them all to the same post variables so I would expect them all to become the same. — Chris
– Chris, Commented Nov 10, 2016 at 17:57
the variables are not changing. where do they come from and in what format? — Mohamed Athif
– Mohamed Athif, Commented Nov 10, 2016 at 18:10

Mohammad Nurul Huda Rimon · Accepted Answer · 2016-11-10 17:57:42Z

1

Try this:

for ($x = 0; $x <= $result; $x++)
{
    if ($update = $db -> prepare("UPDATE substrates SET substrate_name = ?, substrate_desc = ?, substrate_base = ?, substrate_tax = ? WHERE substrate_id = ?"))
    {
        $substrate_name = $_POST['substrate_name'][$x];
        $substrate_desc = $_POST['substrate_desc'][$x];
        $substrate_base = $_POST['substrate_base'][$x];
        $substrate_tax  = $_POST['substrate_tax'][$x];
        $substrate_id   = $x;
        $update -> bind_param('sssss', $substrate_name, $substrate_desc, $substrate_base, $substrate_tax, $substrate_id);
        if ($update -> execute() == true) {
            // Success stuff...
        } 
        else {
            // Error stuff...
        }
        $update -> close();
    }
}

answered Nov 10, 2016 at 17:57

Mohammad Nurul Huda Rimon

2,5351 gold badge17 silver badges18 bronze badges

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

1 Comment

pruoccojr Over a year ago

This did not work, but it did help me get to the answer. Thanks!

pruoccojr · Accepted Answer · 2016-11-10 18:36:33Z

0

I changed the "name" fields in my form to include the substrate's ID:

<input name="substrate_name_'.$substrate_id.'" />

Then did the same for the update query (where the id is defined by the loop, $x):

$substrate_name = $_POST['substrate_name_'.$x];
$substrate_desc = $_POST['substrate_desc_'.$x];
$substrate_base = $_POST['substrate_base_'.$x];
$substrate_tax  = $_POST['substrate_tax_'.$x];
$substrate_id   = $x;
$update -> bind_param('sssss', $substrate_name, $substrate_desc, $substrate_base, $substrate_tax, $substrate_id);

And this gave me the desired result. Thanks to Rimon Khan for the idea and to everyone who commented.

answered Nov 10, 2016 at 18:36

pruoccojr

4721 gold badge3 silver badges19 bronze badges

Collectives™ on Stack Overflow

Using for() to update multiple rows in mysql database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related