What’re you eating? Part 7

When I left this last, I had just got my server working and accepting new database entries but I had a couple of thoughts:

What if I want to see what’s been entered?
What if I make a mistake?

A major point of this project is to know what I need to buy and what I already have at home, so of course I need to be able to use the interface to see what I have. I know I basically never make mistakes so I probably don’t even need to worry about it - but the secondary use for this is removing something that I’ve finished - I’m looking at you oats.

In my mind I’m imagining a table that lists all the details of current stock but has an option to delete the item as well, so let’s work on creating that! The good thing is that I can recycle a lot of the code I’ve already used to access the database, but it did take a bit of research to find out how to pull information out rather than place it in. The end result:

<!DOCTYPE html>
<html>
<head><title>Pantry List</title></head>
<body>
    <h2>Pantry Items</h2>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>Name</th>
            <th>Size</th>
            <th>Date</th>
            <th>Barcode</th>
        </tr>

        <?php
        // Specify the path to your SQLite database file
        $databasePath = 'pantry.db';

        try {
            // Create a new SQLite database connection
            $db = new PDO("sqlite:$databasePath");
            $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            // Prepare the update statement
            $stmt = $db->prepare("SELECT * FROM currentStock");
            $stmt->execute();
            while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
                echo "<tr>";
                echo "<td>" . $row['id'] . "</td>";
                echo "<td>" . $row['name'] . "</td>";
                echo "<td>" . $row['size'] . $row['size_type'] . "</td>";
                echo "<td>" . $row['expiry_date'] . $row['expiry_type'] . "</td>";
                echo "<td>" . $row['barcode'] . "</td>";
                echo "</tr>";
            }
            $db->close();
      
        } catch (PDOException $e) {
            // Handle any errors that occurred during the update process
            echo 'Error: ' . $e->getMessage();
        }?>
    </table>
</body></html>

I start by setting up my table and headers and then move into the PHP scripting. You’ll notice the first lines up to $stmt->execute(); are identical to the last database access just with a different query - here I’m saying “give me everything”. I set up a while loop to keep going until there aren’t any table rows left and defined what data should be in a row before specifying the layout of the data. Remember the code is executing inside an HTML <table> tag and so I can just say “for every line make a new table row and fill it with data” which makes things very neat.

From here it’s pretty straight-forward: the “ . ” is just used to combine text and variables together so something like:

echo "<td>" . $row['size'] . $row['size_type'] . "</td>";

will format like this:

<td>350G</td>

Then I close off the connection and wrap the whole thing in the same try-catch exception as before - ready to test. Now to see what comes up when I navigate to the page.

Now that the information is coming through, I want to add the ‘delete’ function. Since I’ve still got this page open, I’ll add the following to the table:

echo '<td> <form action="pantry_delete.php" method="post"><input type="hidden" name="row_id" value="' . $row['id'] . '" /> <input type="submit" class= "Deletebutton" value="Delete"/></form> </td>';

This will just add another cell to each row that will take the row ID (remember we made the IDs automatically increment so they’d be unique?) and send it to the pantry_delete.php page that I’m about to put together. This shouldn’t take too long (famous last words) because, as before, so much of the code can be reused.

<?php

// Specify the path to your SQLite database file
$databasePath = 'pantry.db';

try {
    // Create a new SQLite database connection
    $db = new PDO("sqlite:$databasePath");
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    // Prepare the update statement
    $stmt = $db->prepare("DELETE FROM currentStock WHERE id=:value1");
    // Set the values for the update statement
    $stmt->bindValue(':value1', $_POST['row_id']);

    // Execute the update statement
    $stmt->execute();

} catch (PDOException $e) {
    // Handle any errors that occurred during the update process
    echo 'Error: ' . $e->getMessage();
}?>
<html><meta http-equiv="refresh" content="0; url=pantry_list.php"></html>

Nothing you haven’t seen before but with a query set to DELETE the item with the ID the page is provided with from the form. This time I didn’t nuke my page with the <meta> tag so I’m already learning! Now, again, we test!

Thanks for following along! Not sure what part I’ll tackle next, but I’ll update when I do!

What’re you eating? Part 8

What’re you eating? Part 6