Search Utility with PHP and MySQL as Backend Server Technologies.

In this tutorial, I’ll explain how to create a simple Library search page using PHP and MySQL databases as the backend technology in the server side.

Prerequisites

1. Basic knowledge of PHP programing language
2. Basic understanding of relational databases
3. PHP and MySQL installed in your machine.

Note that this tutorial was made with PHP 7.2 and MySQL 8.0.15 versions

Setting up the database

Let’s start by making a MySQL database ‘LibraryDB’ and a table Book using the terminal. Note that you are free to make them using your prefered SQL tool. First of all, connect to MySQL. After executing the below command, you’ll be prompted to enter your mysql password.

mysql -u root -p

Create LibraryDB database and Book table with id, book_id, author and category attributes.

mysql> CREATE DATABASE LibraryDB; 
mysql> USE LibraryDB;
mysql> DROP TABLE IF EXISTS Book;
mysql> CREATE TABLE IF NOT EXISTS Book
       (
           book_id TINYINT AUTO_INCREMENT, 
           name VARCHAR(255) NOT NULL,
           author VARCHAR(255) NOT NULL,
           category VARCHAR(255) NOT NULL,
           CONSTRAINT book_PK PRIMARY KEY (id)
        );

Finally, let’s insert some sample data to that will allow us to test our library search utility.

mysql> INSERT INTO Book (name, author) VALUES ("Clean Code", "Robert   
       C. Martin", "SWDevelopment");
mysql> INSERT INTO Book (name, author) VALUES ("Agile Principles,    
       Patterns and Practices in C#", "Robert C. Martin", 
       "SWEngineering");
mysql> INSERT INTO Book (name, author) VALUES ("Clean Architecture",     
       "Robert C. Martin", "SWEngineering");
mysql> INSERT INTO Book (name, author) VALUES ("The Pragmatic    
       Programmer",  "Andrew Hunt", "SWDevelopment");
mysql> INSERT INTO Book (name, author) VALUES ("Practices of 
       an Agile Developer", "Andrew Hunt", "SWDevelopment");

Search Utility

Our search utility will be able to search books based on its category, name, and author. The book’s category is selected from a select box, and it contains three categories: SWDevelopment, SWEngineering and all. It is filled up dynamically with data from the database when the page loads.

But, let’s start first with the backend code.

PHP Server code

First we create a Library and a Book models PHP classes that represents the database and the table of our Library utility.

Create a file structure similar to the following:

search-app 
    /models
        Book.php
        Library.php 
    /css
        index.css
    search.php

The book model encapsulates the data retrieved from the Book table. This is very useful because, this way, the data is easy to be serialized and manipulated by other objects.

The library class is in charge of manipulating the data from the database. Notice how all the methods in the Library class are implemented using SQL prepared statements. This mechanism will avoid SQL injection attacks when the user enters data in our forms.

It is important to note that in this example we are not implemented a secure connection with the database because we are sending the mysql credentials in plain text. In a real application, security is a very important key factor, and those credentials should be sent encrypted.

Front End

Let’s see how the frontend side of this app is developerd. First we add the following CSS code for our search page in the index.css file.

The following HTML will render the search.php page. The first php script in line 2 isside is in charge of retrieving all the book categories to be showed in the select box, and it also checks if there has been a post request from the form.

If the form was submitted, the code inside the if statement will be executed and the data matching all the search keywords from the category select box and the search bar will be retrieved from the database.

The second PHP script in line 49 is in charge of creating a list of book models from the data retrieved from the database, and it also creates the HTML table to show all the info from the book models.

Finally the PHP script in line 63 is the one that shows the number of results per tuple and the total number of books in the database. Note that every time we submit the form, the fields chosen by the user, and the search value stay persistent after the page loads again.

That’s it. Not hard right? Let’s see this app in action. As you can see, we can select the book category and also in the search bar, we can search by book or author

Wrapping up

In this tutorial, I explained how to create a search utility that illustrates how retrieve and select data from a mysql database using PHP and MySQL as the main server side technologies. SQL prepared statements were implemented to secure the data entered by the user in order to avoid SQL injection attacks.