Foreach loop updating every database value

Question

Good afternoon,

I am building a journal, in which I have separate divs.
Each div element displays a different log, with data specific to each log in the div.
For each of the logs, I have a submit button that allows the form to be updated and edited.

What is happening, is that when I submit the form, it is updating each entry in my database, rather than just 1 single entry. I know that the trouble is with my foreach loop, but I am unsure how to remedy this.

Any assistance or pointers would be greatly appreciated.

Code below:

<?php 
    $sqlGrow = "SELECT * FROM grow_details ";
    $query = $conn->query($sqlGrow);

    $grows = array();

     while ($grow = mysqli_fetch_assoc($query) ) {
        $grows[] = $grow;
    } 

    foreach($grows as $grow) {
        $id = $grow['id'];
        $growName = $grow['name'];
?>
    <div class="container">
        <div class="details">
            <h2><?php echo $grow['name']; ?></h2>
            <p class="growNum">Grow #: <?php echo $id; ?></p>
            <table class="growDetails">
                <form method="POST" action="">
                    <tr>
                        <td class="label_growDetails"><label for="datePlanted">Date Planted:</label></td>
                        <td><input type="text" name="edit_datePlanted" id="edit_datePlanted" value="<?php echo $grow['datePlanted']; ?>" />
                    </tr>
                    <tr>
                        <td class="label_growDetails"><label for="strain">Strain:</label></td>
                        <td><input type="text" name="edit_strain" id="edit_strain" value="<?php echo $grow['strain']; ?>" /></td>
                    </tr>
                    <tr>
                        <td class="label_growDetails"><label for="toMaturity">Days to mature:</label></td>
                        <td><input type="text" name="edit_toMaturity" id="edit_toMaturity" value="<?php echo $grow['toMaturity']; ?>" /></td>
                    </tr>
                    <tr>
                        <td class="label_growDetails"><label for="type">Type:</label></td>
                        <td><input type="text" name="edit_type" id="edit_type" value="<?php echo $grow['type']; ?>" /></td>
                    </tr>
                    <tr>
                        <td class="label_growDetails"><label for="gender">Gender:</label></td>
                        <td><input type="text" name="edit_gender" id="edit_gender" value="<?php echo $grow['gender']; ?>" /></td>
                    </tr>
                    <tr>
                        <td class="label_growDetails"><label for="medium">Medium:</label></td>
                        <td><input type="text" name="edit_medium" id="edit_medium" value="<?php echo $grow['medium']; ?>" /></td>
                    </tr>
                    <tr>
                        <td class="label_growDetails"><label for="watts">Watts:</label></td>
                        <td><input type="text" name="edit_watts" id="edit_watts" value="<?php echo $grow['watts']; ?>" /></td>
                    </tr>
                    <tr>
                        <td class="label_growDetails"><label for="lightType">Light Type:</label></td>
                        <td><input type="text" name="edit_lightType" id="edit_lightType" value="<?php echo $grow['lightType']; ?>" /></td>
                        <td class="edit"><input type="submit" name="submit_editGrowDetails" id="submit_editGrowDetails" value="&nbsp;&nbsp;&nbsp;Save Edits&nbsp;&nbsp;&nbsp;" /></td>
                    </tr>
                </form>
            </table>

        </div>

And the PHP for the submit button:

<?php
if(isset($_POST['submit_editGrowDetails'])){
    $edit_datePlanted = mysqli_real_escape_string($conn, $_POST['edit_datePlanted']);
    $edit_strain = mysqli_real_escape_string($conn, $_POST['edit_strain']);
    $edit_toMaturity = mysqli_real_escape_string($conn, $_POST['edit_toMaturity']);
    $edit_type = mysqli_real_escape_string($conn, $_POST['edit_type']);
    $edit_gender = mysqli_real_escape_string($conn, $_POST['edit_gender']);
    $edit_medium = mysqli_real_escape_string($conn, $_POST['edit_medium']);
    $edit_watts = mysqli_real_escape_string($conn, $_POST['edit_watts']);
    $edit_lightType = mysqli_real_escape_string($conn, $_POST['edit_lightType']);

    $name = $grow['name'];

    $edit_growDetails = "UPDATE grow_details
                 SET datePlanted = '$edit_datePlanted', 
                    strain = '$edit_strain', 
                    toMaturity = '$edit_toMaturity', 
                    type = '$edit_type', 
                    gender = '$edit_gender', 
                    medium = '$edit_medium', 
                    watts = '$edit_watts', 
                    lightType = '$edit_lightType' 
                 WHERE name = '$name'; ";

    $query_edit_growDetails = $conn->query($edit_growDetails);

    if($query_edit_growDetails) {
        echo '<p class="success" id="success">Successfully updated log for '.$name.'! <a class="refresh" href="journal.php">Refresh</a></p>';
    } else {
        echo '<p class="error" id="error">There was an error: '. $conn->error .'</p>';
    }
}
?>

Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) — RiggsFolly, Jan 16 '18 at 21:48
No, id is the unique key in the table. Even if i were to use WHERE id = $id, it would still update every entry when I submit the form though as it is still in the foreach loop. When i submit it, it submits foreach div, rather than for just that specific one. — Cody MacLeod, Jan 16 '18 at 21:53
Im thinking, if I turn the name of the submit button into an array, i might be able to get it to work. Unsure how to do this, though. Investigating. — Cody MacLeod, Jan 16 '18 at 22:11

score 2 · Accepted Answer · answered Jan 16 '18 at 21:58

You should always update by the ID of the table, not the name because some may have the same name.

Let's say you have:

ID | Name
1   | Test 1
2   | Test 2
3   | Test 1

With the update query you have now, if you update "Test 1", it will update both "Test 1" with ID 1 and 3.

To fix this, on you html form, you should put a hidden field under the form like so:

<form method="POST" action="">
    <input type="hidden" value="<?php echo $grow['id']; ?>" name="edit_id">
...
...
rest of code

This will echo out the id of the item you are editing.

Then on you php side, you should do this:

<?php
if(isset($_POST['submit_editGrowDetails'])){
    $edit_datePlanted = mysqli_real_escape_string($conn, $_POST['edit_datePlanted']);
    $edit_strain = mysqli_real_escape_string($conn, $_POST['edit_strain']);
    $edit_toMaturity = mysqli_real_escape_string($conn, $_POST['edit_toMaturity']);
    $edit_type = mysqli_real_escape_string($conn, $_POST['edit_type']);
    $edit_gender = mysqli_real_escape_string($conn, $_POST['edit_gender']);
    $edit_medium = mysqli_real_escape_string($conn, $_POST['edit_medium']);
    $edit_watts = mysqli_real_escape_string($conn, $_POST['edit_watts']);
    $edit_lightType = mysqli_real_escape_string($conn, $_POST['edit_lightType']);

    $id = mysqli_real_escape_string($conn, $_POST['edit_id']);
    $name = $grow['name'];

    $edit_growDetails = "UPDATE grow_details
                 SET datePlanted = '$edit_datePlanted', 
                    strain = '$edit_strain', 
                    toMaturity = '$edit_toMaturity', 
                    type = '$edit_type', 
                    gender = '$edit_gender', 
                    medium = '$edit_medium', 
                    watts = '$edit_watts', 
                    lightType = '$edit_lightType' 
                 WHERE id = '$id'; ";

    $query_edit_growDetails = $conn->query($edit_growDetails);

    if($query_edit_growDetails) {
        echo '<p class="success" id="success">Successfully updated log for '.$name.'! <a class="refresh" href="journal.php">Refresh</a></p>';
    } else {
        echo '<p class="error" id="error">There was an error: '. $conn->error .'</p>';
    }
}
?>

You forgot one thing, is to create a hidden field for name too, because this line has no sense `$name = $grow['name'];` — YouneL, Jan 16 '18 at 22:02

Thijs Steel · Answer 2 · 2018-01-16T22:24:54.583

I think your problem is with the following line in your submit handler:

$name = $grow['name'];

Where this $grow is referenced from i cannot see, but you should add the name in as a hidden input in your form. That way the handler knows which tuple to update. For example:

<input type="hidden" value="<?php echo $grow['name']; ?>" name="grow_name">

You can then access it via like any other piece of data from the form, i.e.

$name = mysqli_real_escape_string($conn, $_POST['grow_name']);

Some more advice:

Instead of using raw queries, you should use prepared statements with parameters, they are much safer.
Instead of updating by name, update by id. Even if the name is also unique. An id will generally have a primary key index making the query a lot faster.
The fact that the code actually updated all the entries in the database indicates to me that your handler is actually inside the for loop, that is definitely not what you want, move it out of the. With this update it will work, but if you don't move it out of the loop, it will launch a query for each tuple.

Finally @Carlos, @Riggsfolly, i'm not trying to copy your answers. Just thought i could structure the answer better.

Foreach loop updating every database value

2 Answers2