CODEWITHSUNDEEP
phphtmlloopsforeach
Cody MacLeod
Cody MacLeod

Reputation: 87

Foreach loop updating every database value

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

Upvotes: 0

Views: 90

Answers (2)

Thijs Steel
Thijs Steel

Reputation: 1272

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.

Upvotes: 0

Carlos
Carlos

Reputation: 1331

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

Upvotes: 2

Related Questions