What’re you eating? Part 5

It’s been a while since I’ve written on this project, but it hasn’t been far from my mind. I’ve had great discussions with colleagues and friends about implementation options and improvement suggestions which have kept my mind active and helped me form out the next few stages of this project. Something that came up several times was what I’ll do if I decide something wasn’t the right choice - what if there’s a better way to do [x]? Or if I decide to approach [y] entirely differently? I’ll revisit it! This space isn’t meant to be just a step-by-step guide for setting up projects - if I wanted to do that then it’d just be directions and script blocks for people to copy/paste - it’s meant to be following through my thought process and decisions so everyone can benefit. With that out of the way - let’s begin!

Last time I had just got my recipes digitalised (although they still needed some TLC to clean up the data and make them look nice) and I said my next step was to create a database for my pantry items. For those of you who aren’t aware, a database is a collection of tables that are, in turn, made up of fields & data. After investigating several potential databases I settled on SQLite - no particular logic here, it just seemed to fulfil my needs on paper and I can always change it if I need to. Installing it on the pi and creating a database was incredibly easy which was a big help. In the terminal:

sudo apt install sqlite3
sqlite3 pantry.db

Now that I’ve got my pantry.db database, I had to think about what fields I’d need in my currentStock table. After pulling out and inspecting a bunch of items I had in the cupboard and considering the common factors and what I might need/want to use, I came up with the following list:

id INTEGER - essentially a row number

name TEXT - product name

size TEXT - how many units - separated out from size_type to reduce input variation

size_type TEXT - grams/kilos/millilitres/litres - I plan to have these entered via radio buttons

expiry_date TEXT - either explicit or estimated - I’ll probably look to have a calendar widget for this

expiry_type TEXT - best before or use by

barcode TEXT - potentially to scan items in the future to speed up entry

I set all of the product information to text types mostly for my own ease, but if I’m going to be interacting with the data with python then it won’t matter too much anyway. I can now create the first table in my database with the following command:

CREATE TABLE currentStock(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, size TEXT, size_type TEXT,expiry_date TEXT, expiry_type TEXT, barcode TEXT);

This command is pretty straight-forward if you understand a bit about the SQL language (if you don’t, it’s pretty easy to pick up the basics) but just to explain PRIMARY KEY and AUTOINCREMENT: Primary Key indicates that the field is a unique identifier for that row and autoincrement just means that I don’t have to enter the value (which would involve my swiss-cheese brain remembering what number I was up to).

Now I have to create an HTML form to send data to the table - the process is pretty straight-forward and well documented so I’ll skip most of the ‘working' out’ (read: fiddling) I did to get it looking like how I wanted. Behold:

Product:
Size:
	ML L G KG
Expiry:
	Best Before Use By
Barcode:

While you’re free to dive into the source of the page and grab the full HTML code, in the interest of not crowding the screen while I explain it to you, I’ve included just some key points below:

<title>Food Updater</title>
----------
<form action="food_updater.php" method='post'> <!-- In the live example above  -->
    <table>
        <tr>
            <td align="right" valign="middle">
                Product:
            </td>
            <td align="left" valign="middle">
                <input type="text" name="product">
            </td>
        </tr>
----------
         <input type="radio" id="millilitres" name="size_type" value="ML">
         <label for="millilitres">ML</label><br>
         <input type="radio" id="litres" name="size_type" value="L">
         <label for="litres">L</label><br>
         <input type="radio" id="grams" name="size_type" value="G">
         <label for="grams">G</label><br>
         <input type="radio" id="kilograms" name="size_type" value="KG">
         <label for="kilograms">KG</label><br>
----------
         <input type="date" name="expiry_date" />
----------
         <tr>
             <td colspan="2">
                 <center>
                 <br>
                 <input type='submit' value="Submit"/>
             </td>
         </tr>

To start, the title is just what gets displayed in the browser tab. Next is form - an HTML form submits the information within to a particular location. I specify that I want it to submit to a page called ‘food_updater.php’ via a POST request. The 2 main types of HTML requests are POST and GET - GET requests are for requesting information and the submitted data is sent to the next page in the URL of the request (think about a Google search), whereas POST requests are for sending information for the server to use and it’s not sent in the URL but in the request body (think about a login form). You can read more about the different request types here. I then populate the form with a table made of rows <tr> and cells <td> to structure the data, putting headings in the left and the input fields in the right. In terms of input there are 4 different types: text for free-text, radio for a choice, date for a calendar widget which is mostly for convenience but also to make sure the data is the same format each time, and lastly submit to make a button to send the form contents off to the action page.

Now the form is made, I need to make the food_updater.php page that receives and actions the request. I’ve talked to a colleague about PHP and he said “Friends don't let friends PHP” and proceeded to send me this and this (the standout quote being “PHP is the Nickelback of programming languages”). A note for James: I’ll revisit this later once everything’s running and slap a fresh coat of ‘better practice’ around, but for now, PHP serves the purpose I need it to.

To do this I had to look up a guide for interacting with sqlite databases with php but it turns out the process is pretty easy and I came up with a solution pretty quickly:

<?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("INSERT INTO currentStock (name, size, size_type, expiry_date, expiry_type, barcode) VALUES (:value1, :value2, :value3, :value4, :value5, :value6)");
    // Set the values for the update statement
    $stmt->bindValue(':value1', $_POST['product']);
    $stmt->bindValue(':value2', $_POST['size']);
    $stmt->bindValue(':value3', $_POST['size_type']);
    $stmt->bindValue(':value4', $_POST['expiry_date']);
    $stmt->bindValue(':value5', $_POST['expiry_type']);
    $stmt->bindValue(':value6', $_POST['barcode']);

    // 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=index.html"></html>

A quick explainer aside from the comments in the code (“//”): I define the database and the INSERT SQL statement with some variables. I then bind those variables to the data that came in from the form (you’ll see the name field on each input lines up with the $_POST[‘variable’] variable names) and execute the query, adding that line to the database’s table.

A quick side note - I added the <meta> tag to make the page redirect back to the form so I can enter multiple items in a row (pretty clever, I know) but when I was entering it to this site I forgot to click the “display source code” option which resulted in this page immediately redirecting to index.html which meant that I lost all of my progress because I couldn’t access the page anymore. Faced with the prospect of typing out all the above and refusing to bow to that challenge, I realised I could use the python requests library to get a copy of the page without the browser redirecting me. It ended up taking about 15mins to get it back to a usable state, but it was infinitely better than trying to write it all again - always neuter your code examples!

Finally, I also wrap that all in a try/catch code block to make sure it tells me what went wrong - but we all know I’m perfect and nothing will go wrong so I feel like this a good time to end this entry - I’ll catch you next time!