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 Even if you are escaping inputs, its not safe! Use prepared parameterized statements — RiggsFolly
– RiggsFolly, Commented Jan 16, 2018 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
– Cody MacLeod, Commented Jan 16, 2018 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
– Cody MacLeod, Commented Jan 16, 2018 at 22:11

Carlos · Accepted Answer · 2018-01-16 21:58:54Z

2

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>';
    }
}
?>

answered Jan 16, 2018 at 21:58

Carlos

1,34112 silver badges30 bronze badges

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

2 Comments

YouneL Over a year ago

You forgot one thing, is to create a hidden field for name too, because this line has no sense $name = $grow['name'];

Cody MacLeod Over a year ago

Thijs Steel · Accepted Answer · 2018-01-16 22:24:54Z

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.

Collectives™ on Stack Overflow

Foreach loop updating every database value

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related