How To Create A Simple To-Do List App Using HTML, CSS, JS, and PHP – Part 9

This is the continuation of a series of tutorials about creating a to-do list web application, if you haven’t checked out the entire tutorial please start from Part 1.

In this tutorial, we are going to add a simple file database using SQLite and use PHP to interact with the database. We will then add some data into our database and use JavaScript to send an AJAX request and retrieve that data on the client-side.

First, you will need to enable SQLite functionality in PHP. Open up wherever you installed/extracted PHP and create a file called ‘php.ini’ unless already exists then just open it, and add the following three lines to the file (changing the extension_dir value to the appropriate location on your computer) and save the file. This will tell PHP to load and activate its SQLite extension.

extension_dir=”C:\Program Files\Php\ext”
extension=sqlite3
extension=pdo_sqlite

Let’s create a new file called ‘database.php’ to hold all of our database interactions. In that new file we are going to create a class that extends the SQLite3 class to handle all of our database requirements. A class is basically a blueprint for an object, we can add functionality to a class and then create multiple objects which all have that functionality. Extending a class essentially means to grab all the functionality from one class and use it in our class, without having to rewrite it all. Classes, objects, and inheritance are explained in other posts that will be linked at the bottom of this page.

Next, we are going to create a constructor function in our object, that will get called as soon as an object instance of our class is created. In our constructor function, we first need to open a SQLite file database and specify what file we wish to save our database. I have chosen to name my file ‘mysqlitedb.db’ but it doesn’t really matter what you name it. After this, we then use the ‘execute’ function to execute some SQL code. SQL is a scripting language used for SQL databases. In this SQL code we are saying to create a table called ‘items’, but only if the table doesn’t already exist, and to add three columns to that table. The first column is called ‘id’, which will use to identify a unique list item as we did in JavaScript, which is an integer type and is used as the primary key. The primary key essentially refers to which column you are going to use to identify unique individual items in the database, it isn’t a requirement that a table ha a primary key. The next column is called ‘checked’, which will use to store if the list item has been checked or done, which is a boolean type. We then specify that this column cannot be null, which means it new entries must have a value that is not null, and that the default value if one is not supplied will be ‘false’. The final column is called ‘text’ and is a VARCHAR type, which basically means string or plain text. Again this column cannot be null.

Now that we create our database we need to add some data into it, otherwise, it will be empty and we won’t be able to retrieve any list items from it. We do this by the execute function again, as we did before, but we use an ‘insert’ SQL command to put data into our database. The insert command we use specifies which columns we want to enter data for, we only need to specify the ‘text’ column as the ‘id’ column is an auto-incrementing primary key and the ‘checked’ column has a default value if we don’t set it. The below insert statement inserts three different to-do list items into our ‘items’ table. Each item will have a unique ‘id’, as it is auto-incrementing, and the ‘checked’ value will default to ‘false’.

We are now going to create another function in our ‘MyDatabase’ class so that we can grab all the data from our ‘items’ database table. The first thing we do in our ‘getItems’ function creates an array called ‘arr’, that starts as an empty array. Next, we need to run a SQL query, using the ‘query’ function to run a ‘select’ statement on our ‘items’ table. The asterisk ‘*’ is where we can specify which columns we want to retrieve, in this example we are getting all the columns of the table. After we have run our query we need to loop through all the results of that query, loop through all the items in our database that were returned. Below I have used a while loop to fetch each row as an array using the ‘fetchArray’ function and then add each row into our ‘arr’ variable. This means our ‘arr’ variable will now contains a list of array objects for each row in the database table. We can now return the result, the items from our table, but we must first transfer it into JSON. We reason we have to convert it to JSON is because we will be sending it from the server-side to the client-side, and we cannot send just normal objects they must be transferred to JSON.

Right now we cannot use the functions in our ‘MyDatabase’ class as we cannot run functions on a class without creating an instance of the class first. After the class definition, outside of the curly brackets, we can create a variable called ‘db’ and create a new instance of the ‘MyDatabase’ class. We can create multiple instances of a class but for our purposes, we only need one instance. This is still within the ‘database.php’ file.

In our ‘server.php’ we cannot currently use any of the code within the ‘database.php’ file. We need to include one of the files into the other, effectively importing the ‘database.php’ into our ‘server.php’ file. We can use the ‘require_once’ function in php to achieve this, we put this at the top of the page, above the switch statement. Without the ‘require_once’ function the ‘db’ variable will be undefined and if we just the ‘require’ function we may end up with multiple instances of the ‘db’ variable.

Now we can use the ‘db’ variable we defined in ‘database.php’ in our ‘server.php’ file. Within our switch statement, in the case for the ‘/api/items’ URI we can use our call the ‘getItems’ function and return all the items from our database. This means when a user sends a request to the ‘/api/items’ they will get a JSON object of the all the items from our database as a response.

If you now run the PHP inbuilt server using the “PHP -S localhost:8080 server.php” on the command line and open the page in a browser, as we did in the previous part. In the background our JavaScript code will send a request to ‘/api/items’ and print the result to the developer console. You should see a JSON string in the developer console containing information for all the items in the database table. You may notice that every time you refresh the page more items are added to the database, this is because every time the client sends a request to the server it will run the ‘insert’ command again, resulting in adding more data to the database table.

Further Reading

What is a Database?
Relational Databases Explained
Classes and Objects
Inheritance and Polymorphism
Interfaces and Abstract Classes
What is SQL?
What is JSON?

Remember all of the code for this tutorial series can be seen at the following link: https://github.com/acroynon/tutorial-to-do-list-app
and a demo of the to-do list application can be seen at the following link: https://acroynon.github.io/tutorial-to-do-list-app/

Continue to Part 10

Leave a Reply