Update multiple MySQL rows with one PHP submit button

Question

I am having the hardest time figuring out something that I think should be simple. I need to update multiple rows in my database with one submit button. I have it working with a submit for each row now, but I need to combine it. Here's what I'm trying. Where have I gone wrong? (I've been going off of multiple tutorials I found online and I think I have things all mixed up).

Here's the form:

<?php foreach ($teams as $team): 
$id[]=$team['id'];?>


<form action="?update" method="post">
    <div class="team-box">
        <h2><?php echo $team['name'] ?></h2>

        <label for="name">Name:</label>
        <input type="text" name="name" value="<?php echo $team['name'] ?>" />

        <label for="name">Match Wins:</label>
        <input type="text" name="mwins" value="<?php echo $team['mwins'] ?>" />

        <label for="name">Match Losses:</label>
        <input type="text" name="mlosses" value="<?php echo $team['mlosses'] ?>" />

        <label for="name">Match Ties:</label>
        <input type="text" name="mties" value="<?php echo $team['mties'] ?>" />

        <label for="name">Game Wins:</label>
        <input type="text" name="gwins" value="<?php echo $team['gwins'] ?>" />

        <label for="name">Game Losses:</label>
        <input type="text" name="glosses" value="<?php echo $team['glosses'] ?>" />

        <input type="hidden" name="id" value="<?php echo $team['id'] ?>" />
    </div>

Here's the PHP to handle the UPDATE:

try
{
foreach($_POST['id'] as $id) {
$sql = 'UPDATE teams SET
    name = "' . $_POST['name'.$id] . '",
    mwins = "' . $_POST['mwins'.$id] . '",
    mlosses = "' . $_POST['mlosses'.$id] . '",
    mties = "' . $_POST['mties'.$id] . '",
    gwins = "' . $_POST['gwins'.$id] . '",
    glosses = "' . $_POST['glosses'.$id] . '"
    WHERE id = "' . $_POST['id'.$id] . '"';
$pdo->exec($sql);
}

}
catch (PDOException $e)
{
    $error = 'Error adding submitted team: ' . $e->getMessage();
    include 'error.html.php';
    exit();
}

header('Location: .');
exit();

Thanks in advance!

there is no $_POST['name'.$id] only $_POST['name'] if the above is repeated you want name="name[]" have a look at print_r($_POST) to see what you are working with — user557846
– user557846, Commented Jul 3, 2012 at 20:39

LSerni · Accepted Answer · 2012-07-03 21:04:50Z

There are a couple of things that need fixing.

The FORM must be outside the foreach.
The '...name="name" value="...', to agree with the _POST code, should read instead: ... name="name" value="...

This way, a single POST will submit, say,

  name123="Rangers"
  mwins174="123"

Then you need all IDs. You can do that by issuing, in the foreach, this:

 <input type="hidden" name="id[]" value="<?php print $team['id']; ?>" />

This will result in HTML:

 <input type="hidden" name="id[]" value="123" />
 ...
 <input type="hidden" name="id[]" value="456" />

and in $_POST['id'] being an array containing 123, 456 and so on.

You could also put in HTML:

 <input type="text" name="name[<?php print $team['id']; ?>]" value="...

so that $_POST['name'] would be a vector with the same keys as the values of id, and therefore:

foreach($id as $team_id)
{
    // Pseudocode
    UPDATE... SET name=$name[$team_id]... WHERE id = $team_id;
}

This way you have one SUBMIT, and multiple UPDATEs.

Jocelyn · Accepted Answer · 2012-07-03 20:34:40Z

0

Since all your field names change for each UPDATE query, you will need to execute separate queries as you already do. I don't think you would be able to perform a single UPDATE query.

I don't understand, what's wrong with executing several update queries?

answered Jul 3, 2012 at 20:34

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

2 Comments

Jonathon Doesen Over a year ago

I'm fine with doing that in principle... but isn't that what I'm doing with the foreach loop? (I'm new to php so I'm probably mistaken). But my understanding was that the foreach loop was looping through each entry(via id) and submitting a separate query for each one.

Jocelyn Over a year ago

Either I don't understand what your problem is, or I would code that as you already did (one UPDATE query for each id).

Collectives™ on Stack Overflow

Update multiple MySQL rows with one PHP submit button

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related