Coders' Guidebook
Tutorials Dictionary

How to build a search engine using SQL and PHP

This tutorial will show you how to create a search engine that allows users to search the content on your website. To complete this tutorial you will require a database to store the search results. You should be able to create a database easily if you chose a hosting provider that offers access to cPanel, as recommended in our tutorial on registering your website.

Creating the SQL database

The search engine will draw results from a SQL database. This tutorial will assume your web hosting provider offers access to cPanel. Your view of cPanel will depend on which theme you are using. The screenshots for this tutorial are from cPanel in Paper Lantern mode. If your cPanel layout does not look like the screenshots below then you may need to change your theme to Paper Lantern theme by clicking the 'Switch Theme' button pictured below.

switch_theme.PNG

Once in Paper Lantern mode, navigate to the section called 'MySQL® Databases'.

mysql.png

If you have not done so already, then we will need to create an SQL user profile to manage and access the SQL database. To do this, navigate to the Add New User section and enter the username and password you would like to use. Be sure to note these details down somewhere safe because you will need them later. Part of the username may already have been filled in in the light grey box. Include this part in your notes too because it forms part of the complete username.

addsqluser.png

Next, navigate to the Create New Database section and create a database called search. Again, a preceding part of the database name may be auto-filled in the grey box.

createnewdatabase.PNG

Once you have created the database, return to the main cPanel dashboard and navigate to the section called phpMyAdmin.

phpmyadmin.png

We will use phpMyAdmin to manage the search engine database. First, we need to create a table to store the search engine results. To do this, navigate to the search database. The database should be empty, as shown below.

create-new-sql-database.png

Let's create a new table. The table will be called Results and will comprise five columns, each containing a different item of information about the search results. To create the results table, click the SQL tab then copy and paste the following SQL code into the text box:

CREATE TABLE Results (
    ID int NOT NULL AUTO_INCREMENT,
    Title varchar(255),
    URL varchar(255),
    Description varchar(255),
    Preview varchar(255),
    PRIMARY KEY (ID)
);

The above SQL code creates a table called Results that comprises the following columns:

Once you have pasted the SQL code into the text box, press the Go button to run the code and create the Results table. You should see a confirmation message that the table was successfully created.

create-search-results-sql-table.png

Inputting search results

Let's now add search results to the database. To do this, navigate to the Results table in phpMyAdmin and click the Insert tab, as shown below.

insert-new-sql-database-record.png

Fill in each field with the details of the search result. You can leave the ID field blank because the database will automatically assign a unique ID value. In the below screenshot, the search result description is simply "Dragon's blood tree" because we will use that as a test search term later to confirm the search engine works; however, when you are creating your database entries you might like to copy and paste large sections of text from the target webpage. Providing as much useful text as possible in the description field will help ensure the record is returned in response to the user's search query.

insert-new-sql-table-entry.png

If you ever need to edit a database entry then you can do this by returning to the Results table and pressing the edit button for the record you would like to change.

edit-sql-database-record.png

Using PHP to retrieve search results

Search results will be displayed on a PHP (PHP: Hypertext Preprocessor) web page. PHP is a server-side scripting language, which allows you to perform actions on the server that your website is hosted on. The results of these actions can be displayed to the user. In this instance, we will use PHP to query the search database and retrieve relevant results that we can show to the user. You can add HTML code to PHP files and build web pages in the usual way, just with the added functionality of PHP.

In your code editor, create a new PHP file and save it in your website's root directory (the same directory as your website's homepage) using the filename search.php. To begin, add the regular HTML code you would add to a typical web page. For example, you should define the head and body elements, as shown below:

<!DOCTYPE html>
<html lang="en-GB">
<head>
    <title>Search results</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" type="text/css" href="main.css">
</head>
<body>

</body>
</html>

Next, we'll write the PHP code that connects to the database. To do this, add the following code to the body element:

<?php
    $servername = "LOCALHOST";
    $username = "USERNAME";
    $password = "PASSWORD";
    $database = "DATABASE";

    $con = mysqli_connect($servername, $username, $password, $database);
	
    if (mysqli_connect_errno()){
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
        exit();
    }
	
    // Retrieve the search results here
?>

In the above code, replace LOCALHOST, USERNAME, PASSWORD and DATABASE with the details of your database. You set up a username, password and database in the section on creating the search database so you should already have these details. For the localhost number, you can find this by navigating to the Remote MySQL® section of cPanel and noting down the access hosts number (an example of what this number will look like is circled in red below):

localhost.png

Once the PHP file has connected to the database, we can retrieve the search results. To do this, replace the // Retrieve the search results here comment in the PHP script with the following code:

if (isset($_GET['search'])) {
    $param = "%{$_GET['search']}%";
    $query = mysqli_prepare($con, "SELECT * FROM Results WHERE Description LIKE ?");
    mysqli_stmt_bind_param($query, "s", $param);
    mysqli_stmt_execute($query);
    $results = mysqli_stmt_get_result($query);
    $rows = mysqli_num_rows($results);
    mysqli_stmt_close($query);

    if ($rows > 0) {
        echo "<h2>Search results for: {$_GET['search']}</h2>";
             
        while ($result = mysqli_fetch_array($results)) {
            $result_title=$result['Title'];
            $result_url=$result['URL'];
            $result_preview=$result['Preview'];
				
            echo "<div class='search_result'> 						
                <h3><a href='$result_link'>$result_title</a></h3>
                <article><a href='$result_url'>$result_preview</a></article>			
            </div>";
        }   
    } else {
        echo "<h2>No results found for your search: {$_GET['search']}</h2>";
    }
} else {
    echo "<h2>No search query provided. Please try your search again.</h2>";
}
mysqli_close();

The user's search query will be appended to the web page URL. In the above code, we retrieve the search query using PHP's GET command. If no search query is provided, then a message will be printed to the web page asking the user to submit their query again. Meanwhile, if the PHP code successfully retrieves the user's search query, then it wraps the query string in percentage symbols (%). The percentage symbols indicate that one or more characters can appear before or after the user's search query when we search the database for suitable matches. This step is important because it means search results will be returned even if the user only enters a fragment of an entries Description field in the Results database table.

Next, we prepare the SQL query that will search the Records table for entries that contain the user's search query somewhere in their description. It is important to never add PHP variables directly to the SQL query string because hackers can manipulate the variables using SQL injection and access your database. For this reason, the above code instead prepares the query then binds the user's search query parameter later. Preparing and binding SQL queries introduces extra validation steps and ensures the SQL query has not been manipulated by hackers.

If the user's search query returns results from the database, then the PHP script will extract the information from each result and add it to the web page. Meanwhile, if no results are found then a message will advise the user accordingly.

searchresult.PNG

To begin styling the search results, it would be a good idea to add the following CSS code to your main.css stylesheet:

.search_result {
    color: MidnightBlue;
    text-align: center;
}

.search_result a {
    color: RoyalBlue;
}

The above code styles the text colour and text alignment of the text and links in each search result.

<<< Previous

Next >>>