CODEWITHSUNDEEP
phpmysqlfor-loop
pruoccojr
pruoccojr

Reputation: 482

Using for() to update multiple rows in mysql database

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?

Upvotes: 0

Views: 39

Answers (2)

pruoccojr
pruoccojr

Reputation: 482

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.

Upvotes: 0

Mohammad Nurul Huda Rimon
Mohammad Nurul Huda Rimon

Reputation: 2525

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();
    }
}

Upvotes: 1

Related Questions