Building a Book Recommendations App With PHP, SQLite, and Docker
Last updated
Was this helpful?
Last updated
Was this helpful?
PHP is one of the first technologies that made dynamic web applications possible, and it’s still widely used today. In this tutorial, we’ll look at how to build a CRUD application with PHP and SQLite. We’ll build a basic book recommendation application where we can Create new entries or Read, Update, or Delete existing ones. Nearly all applications rely on these four CRUD operations, so you’ll be able to extend this application to do anything else you want.
Here’s what the final app will look like:
You will need the following to complete the tutorial:
Docker installed locally.
IDE or text editor of your choice.
(Optional) a local PHP developer environment.
You can do all the development using the Docker environment that we’ll create as part of the tutorial, but it can be easier to run and debug code locally so if you haven’t used PHP before and don’t want to do the set-up, you can rely only on Docker.
To deploy the application to Code Capsules, you’ll also need:
Let’s start by creating a project folder that will house all our files.
In a terminal, navigate to the directory you’ll be keeping the application files in. Run the commands below to create the project folder and navigate into it.
Copy
Let’s begin by building our app’s index page. This page will use PHP and HTML, as it will contain both static and dynamic content. Create a file named index.php in the project root folder and populate it with the code below:
Copy
It then sets up a table structure that we’ll use PHP to populate later.
Even though our app doesn’t do anything yet, let’s run it to see our progress so far. We’ll use Docker for this. In the same project directory, create a file called exactly Dockerfile (note the capital D and no file extension), and add the following code.
Copy
This pulls an official Docker container which already has the PHP language installed and integrated with Apache, a web server. It copies all files from the local directory (in our case, just index.php for now), and exposes port 80, which is the port that Apache is set to serve files on.
Now run the following command in your terminal.
Copy
This builds a Docker image from the Dockerfile in the current directory and gives it book-app as a tag. The second command (after &&) runs the container, and maps our local port 8000 to the Docker port 80. Once it’s running, you can visit http://localhost:8000 in your web browser to see the application.
Hit Ctrl + C in the terminal window running Docker to stop the server.
To allow the user to add new books, we’ll need a form with an input. Let’s build that now.
Add the following code to your index.php file, above the existing table definition.
Copy
This sets up a form with inputs and a submit button. Note the hidden field with a value of “create”, which we’ll be using later to differentiate between different actions, such as creating, updating, or deleting books.
If you run the app again, you’ll see something like the following.
Now you can type in a book and author name and press the “Save” button, but then the app will crash as we haven’t built the backend yet. Let’s do that next.
Next, we’ll create an app.php file to handle the backend logic and database connection for our application. Create this file and add the following code.
Copy
Note that we use a database directly in the /tmp folder of the Docker container. This means you’ll lose all data every time you restart your application. We’ll fix this towards the end of the tutorial when we deploy the application and set up persistent storage.
To use this code from the main index.php file, add the following lines to the top.
Copy
This imports all the code into index.php, as if you had written in that file. We keep most of the PHP code in a separate file to make our codebase better organized.
You can run the application again now, and you’ll see that you’re able to insert books using the form. However, we aren’t ever reading the books from the database again, so they’ll just disappear. Let’s add some more logic to retrieve any saved books from the database and display them to the user.
We’ll display our books to the user by grabbing them all from the database, looping through them, and adding a table row for each entry.
In the app.php file, add the following function to the top of the file.
Copy
This retrieves the books from the database and returns an array containing all of them. Update the Books section of the index.php file with the following code.
Copy
Here we use our getBooks function to retrieve all the books and then a while loop to iterate through each one. We add each book as a new table row, displaying the title and the author in their own columns. We also add more columns with an “Edit” and “Delete” button for each book. The buttons call JavaScript functions (that we haven’t written yet), passing in the ID, title, and author of the book that the user wants to edit or delete.
If you run the app again, you’ll see that now any books that you add automatically show up in the table. The Edit and Delete buttons don’t work yet, though, so let’s fix that.
To avoid our table getting too messy, we’ll use some basic JavaScript to edit and delete books, along with some hidden forms at the top of our page. Right after the opening <body> tag in index.php, add the following forms.
Copy
These are two forms consisting only of hidden input fields, so they won’t be visible to the user. We’ll populate the values and submit them using JavaScript. The first form has values for new_title and new_author so we can update the database with new values supplied by the user. The “delete” form only has the book ID, as that’s all we need to remove it from the database.
We need matching JavaScript functions to use these forms, so add the following code to the <head> section of your index.php file.
Copy
We pass variables for the ID, title, and author to both functions. Although the existing title and ID are not strictly necessary, it’s nice to show them to the user for reference when they are entering the new information or as a confirmation for the delete function. The update function prompts the user for a new title and author and then submits the update form, while the delete function confirms if the user really wants to delete that entry and then submits the delete form.
Finally, to make these work on the backend, update the if statement in the app.php file to look as follows.
Copy
This now handles the update and delete forms we built, calling UPDATE or DELETE statements on our database as required. Note that we are still using prepared statements to protect against SQL injection.
The application should now run fine on your local machine, but let’s deploy it to the internet, so others can use it too. We’ll:
Create a GitHub repository and push the code to GitHub.
Create a Docker and Data Capsule on Code Capsules and bind them together.
Deploy the code to Code Capsules.
Copy
Your remote repository will now be up-to-date with your local one.
The final step is to deploy our app to Code Capsules. We’ll use two capsules for this: a Docker Capsule for the application and a persistent storage Data Capsule for the database, so that our data doesn’t disappear each time the application is restarted.
Change the line where you connect to the database in the app.php file to match the following.
Copy
In Code Capsules, the PERSISTENT_STORAGE_DIR environment variable will point to the Data Capsule once the two capsules are bound together.
Now create a new Data Capsule and a Docker Capsule in a single Space in Code Capsules. For the Data capsule, choose “A persistent storage mounted directly to your capsule”.
For the Docker capsule, choose your GitHub repository and enter Dockerfile for the Dockerfile location. In the configuration tab, set “Network Port” to “80” to match what Apache is running on, and bind the Docker Capsule to the Data Capsule.
Deploy and build the application, and you should see it running on a custom URL that you can share with the world.
A account and Git installed locally.
A account.
This builds out a basic HTML page. We add links in the header to , a CDN to get open-source fonts and , a classless CSS framework that makes HTML look better out of the box without having to add specific class names like you would with a framework like Bootstrap or Tailwind.
The code above connects to a database when the app is launched. SQLite is a lightweight alternative to full database systems such as PostgreSQL or MySQL. It stores all data in a simple file. It also automatically creates a database if you give it a file that doesn’t exist, so in our case it will create the books.db file the first time we run this code.
This code also creates a table for our books if it doesn’t already exist. The if statement checks if there’s any data in $_POST, which will be populated from the form we defined in index.php, and then we insert this data into the database. Note that we use instead of basic string concatenation to include the dynamic user input in the INSERT INTO statement. This is to prevent , which is a common vulnerability where a malicious user hacks your database by modifying the database inputs.
Head over to and create a new repository. Then, in your project’s root folder, run the commands below from the terminal, replacing “username” and “repository_name” with your own values from GitHub.
Push all of your code changes up to your GitHub repository and ensure that Code Capsules is authorized to read that repository. You can reference this to see how to do so in greater detail.
