how to edit a record from MySQL database

Question

this is my first post!

First off, I have my code which outputs a table on index.php. At the end I have an edit link which takes me to the edit.php page:

if ($result->num_rows > 0) {
    echo "<p><table><tr><th>ID</th><th>Film Name</th><th>Producer</th><th>Year Published</th><th>Stock</th><th>Price</th><th>Function</th></tr>";
    while($row = $result->fetch_assoc()) {
        echo "<tr><td>".$row["ID"]."</td><td>".$row["FilmName"]."</td><td>".$row["Producer"]."</td><td>".$row['YearPublished']."</td><td>".$row['Stock']."</td><td>".$row['Price']."</td><td>"."<a href=\"edit.php\">Edit</a> / Delete"."</td></tr></p>";
    }
    echo "</table>";

edit.php (first there is the form):

$query = "SELECT * FROM ProductManagement WHERE ID=" . $_GET["ID"] . ";"; // Place required query in a variable
                $result = mysqli_query($connection, $query); // Execute query

                if ($result == false) { // If query failed
                    echo "<p>Getting product details failed.</p>";
                } else { // Query was successful   
                    $productDetails = mysqli_fetch_array($result, MYSQLI_ASSOC); // Get results (only 1 row 
                    // is required, and only 1 is returned due to using a primary key (id in this case) to 
                    // get the results)

                    if (empty($productDetails)) { // If getting product details failed
                        echo "<p>No product details found.</p>"; // Display error message
                    }
                }
                ?>
<form id="updateForm" name="updateForm" action="<?php echo "?mode=update&ID=" . $productDetails["ID"]; ?>" method="post">                                
                    <div>
                        <label for="updateFormProductCostPrice">ID</label>
                        <input id="updateFormProductCostPrice" name="ID" type="text" readonly 
                               value="<?php echo $productDetails["ID"]; ?>">
                    </div>
                    <div>
                        <label for="updateFormProductName">Film Name</label>
                        <input id="updateFormProductName" name="FilmName" type="text" value="<?php echo $productDetails["FilmName"]; ?>">
                    </div>
                    <div>
                        <label for="updateFormProductDescription">Producer</label>
                        <textarea rows="4" cols="50" id="Producer" 
                                  name="productDescription"><?php echo $productDetails["Producer"]; ?></textarea>                
                    </div>
                    <div>
                        <label for="updateFormProductPrice">Year Produced</label>
                        <input id="updateFormProductPrice" name="YearProduced" type="text" 
                               value="<?php echo $productDetails["YearProduced"]; ?>">
                    </div>
                    <div>
                        <label for="updateFormProductStock">Stock:</label>
                        <input id="updateFormProductStock" name="Stock" type="text" 
                               value="<?php echo $productDetails["Stock"]; ?>">
                    </div>
                    <div>
                        <label for="updateFormProductEan">Price:(&#163)</label>
                        <input id="updateFormProductEan" name="Price" type="text" 
                               value="<?php echo $productDetails["Price"]; ?>">
                    </div>
                    <div>
                        <input id="updateSubmit" name="updateSubmit" value="Update product" type="submit">
                    </div>
                </form>
</body>

Then there is the php code to update the record (edit.php continued):

if (((!empty($_GET["mode"])) && (!empty($_GET["id"]))) && ($_GET["mode"] == "update")) { // If update
        echo "<h1>Update product</h1>";
        if (isset($_POST["updateSubmit"])) { // If update form submitted
            // Check all parts of the form have a value
            if ((!empty($_POST["ID"])) && (!empty($_POST["FilmName"])) 
                    && (!empty($_POST["Producer"])) && (!empty($_POST["YearProduced"])) 
                    && (!empty($_POST["Stock"])) && (!empty($_POST["Price"]))) {
                // Create and run update query to update product details
                $query = "UPDATE products "
                        . "SET FilmName = '" . $_POST["FilmName"] . "', "
                        . "Producer = '" . $_POST["Producer"] . "', "
                        . "YearProduced = '" . $_POST["YearProduced"] . "', "
                        . "Stock = " . $_POST["Stock"] . ", "
                        . "Price = '" . $_POST["Price"] . "' "
                        . "WHERE id=" . $_GET['ID'] . ";";
                $result = mysqli_query($connection, $query);

                if ($result == false) { // If query failed - Updating product details failed (the update statement failed)
                    // Show error message
                    echo "<p>Updating failed.</p>";
                } else{ // Updating product details was sucessful (the update statement worked)
                    // Show success message
                    echo "<p>Updated</p>";
                        }
                                                                                }
                                            }
}

I do apologise that there is a lot of code here. Basically when I click edit in the table on the home page, I would expect it to load up the data for the respective row selected so I can update it.

Currently, when I click the 'edit' link, it loads the edit page and it has the blank fields and says "getting product details failed". It would be great if it can retrieve the data for the respective row selected. Can someone help please? Thanks!

Answer 1

In edit.php file $_GET["ID"] is empty because there is no ID value in your link so query returns no results. Also in your last file you have $_GET["id"] which is different from the value you use ($_GET["ID"]).

Try this:

echo "
    <tr>
        <td>".$row["ID"]."</td>
        <td>".$row["FilmName"]."</td>
        <td>".$row["Producer"]."</td>
        <td>".$row['YearPublished']."</td>
        <td>".$row['Stock']."</td>
        <td>".$row['Price']."</td>
        <td><a href=\"edit.php?ID=".$row["ID"]."\">Edit</a>
        <td><a href=\"delete.php?ID=".$row["ID"]."\">Delete</a>
    </tr>";

Also you are SQL Injection vulnerable. You can combine mysqli with prepared statements to avoid this.