Creating a Basic Shopping List Script with PHP

Being healthy is the “new” thing around the office at Interspire. Rodney and I have taken up the challenge of Mitch and Ed and have been keeping to a strict routine of exercise and a healthy diet.

Instead of scouring the local take-aways for lunch everyday, we now pop on down to the supermarket to buy fresh produce and meats to make our own lunches. However, we always found ourselves in a situation where we would forget something. Sure we could be old fashion and make a list and put it on the office fridge, but then I asked myself, “why do that when I could whip up an online shopping list?”

Our list became a hot little item around the office, so I thought I’d spend an hour explaing how you too can build your very own shopping list with. Keep in mind that you don’t have to use this just as a shopping list – it can also be a To-Do list or practically a list of anything you need to remember.

In this article I do assume you have a small amount of PHP and MySQL experience, so if you don’t then you might have to stick to a written shopping list for now.

Let’s get busy!

First up we’re going to create the MySQL database table. Here is the schema for the table we are going to use.

CREATE TABLE `thelist` (
`itemid` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL default ”,
`owner` varchar(255) default ”,
`purchased` int(11) NOT NULL default ’0′,
`quantity` int(11) NOT NULL default ’0′,
`reoccuring` int(11) NOT NULL default ’0′,
PRIMARY KEY(`itemid`)

We have 6 fields in 1 table for this script.

  1. itemid – Our primary field, used for making each row unique.
  2. name – The name of the item. e.g. ‘Milk’
  3. owner – Sometimes with our list an item was for a specific person rather than the entire group, such as Eddie with his Pitted Olives or Rod with his shower caps Swiss cheese.
  4. purchased – This will be a binary field, 1 or 0. Used to indicate whether the item has been picked up yet.
  5. quantity – More often than not, with 2-6 of us eating this way, we’ll want more than one of the things on the list.
  6. reoccuring – We find that we need to pick up the same things each week. This binary flag allows the item to stay on the list every week.

Now that we’ve setup the database we will set up our PHP script. To make it easy we’ll keep everything in one file – unlike how we code our applications, mind you ;)

Let’s start off with the database connection:


$hostname = ‘localhost’;
$username = ‘username’;
$password = ‘password’;
$databasename = ‘database’;

if (!$connection_result = mysql_connect($hostname, $username, $password)) {
    die(‘Error Connecting to MySQL Database: ‘ . mysql_error());

if (!$db_result = mysql_select_db($databasename, $connection_result)) {
    die(‘Error Selecting the MySQL Database: ‘ . mysql_error());

// if we made it here, we are one with the database


So far, so good. This code makes sure we’re connected to the database. Once that’s done we can do what we want with the table.

Continuing on from there we are going to use a switch statement to select what action to take. We have all of our actions inside separate functions so that the code is easy to maintain and update at a later time. (this code follows on from above)

// check what action we are performing
    case ‘delete’:
    case ‘edit’:
    case ‘new’:
    case ‘save’:

The functions are pretty self explanatory so I wont go into too much detail. You can download the shopping list script here to try it yourself. Just make sure you create the database table before running the script or you’ll receive a nasty error!

This entry was posted in Web Technologies. Bookmark the permalink.

5 Responses to “Creating a Basic Shopping List Script with PHP”

  1. annette taylor says:

    Great little script can wait to try it.
    Thank you

  2. ORamos says:

    I'm going to try!

  3. web says:

    it is great and will try it

  4. Chris Colden says:

    Nice base script. Easy to expand to something with more features. There are a few errors in the script. the link for edit goes to action=edititem when it should be action=edit, nothing too hard to change. Also depending on your version of MySql you may have to change the SQL queries. Over all a nicely coded script. Thank you

  5. Daniel says:

    Thanks for sharing. Made it easy when someone had done all the heaving lifting. Would be nice to get a version posted without the SQL or URL bug. I’ve already added the ability to toggle the Purchased? option with a link.

    Thanks very much

Leave a Reply