Coders' Guidebook
Tutorials
Web development
Android apps
Search

How to build a search engine using SQL and PHP

This tutorial will guide you through how to create a search engine for your website. The search engine will load pages of your website based on user queries. To proceed with this tutorial you will need an SQL database because to store your search results. If you use x10hosting to host your website then you will already have access to an SQL database regardless of whether you have a free or premium account.

Creating the SQL database

The search engine will draw its results from an SQL database. This tutorial will cover how to create an SQL database using cPanel on x10hosting (but you can use the SQL database provider of your choice). First of all, log into your x10hosting account and open 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 switch your theme from x10Hosting Basic to Paper Lantern theme by clicking the 'Switch Theme' button pictured below.

switch_theme.PNG

Once in Paper Lantern mode, navigate to the section named '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. Navigate to the section of the webpage as indicated below and decide what username and password you would like to use. Be sure to note these details down somewhere safe because we will need them later. Part of the username will 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

Now we need to create a database called 'search' using the Create New Database section of the page. Again, the first part of the database name will have been 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 named 'phpMyAdmin'.

phpmyadmin.png

We will use phpMyAdmin to manage the contents of our database. Navigate to the search database you created. Currently, there is nothing in the database and so we should see something similar to below.

newtable.PNG

Let's create a new table. Name the table 'search' and set it to include 5 columns. Fill in the details of those columns as shown below:

tablepreview.PNG

The title column will contain the name of the search engine result and the link column will contain its URL. The description column will contain all the terms which will load up the database entry when searched for. The preview column will contain a brief passage of text that will be displayed along with each search result and tells the user about the webpage the search result is linked to. Lastly, the ID column will automatically assign an identifying number to each new database entry.

Inputting search results

Now we have created our search engine database we can begin adding some results. To do this, navigate to the 'search' table in phpMyAdmin and press the insert button which has been highlighted below.

insertresult.png

Fill in each entry similar to the example below. You can leave the ID section blank because it will autocomplete itself when the entry is added to the database. In this example, our description is simply 'Dragon's blood tree' because we will use that as a test search term later just to see our 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 for that search result. This will ensure when the user later comes to use your search engine that if they search for a term or phrase which can be found on a given webpage it will be included in the search results.

newentry.PNG

If you ever need to edit a database entry then you can do this by going back to the 'search' table and pressing the 'edit' button on the entry you would like to change.

editentry.png

Using PHP to retrieve search results

The results of our searches will be displayed on a PHP: Hypertext Preprocessor (PHP) webpage. PHP is useful because it can display regular webpage content like HTML but also interact with SQL databases. The PHP page we will design will retrieve and display entries from the SQL database which match the user's query.

In your code editor, create a new PHP file and save it in the same folder as your main HTML files using the filename 'search.php'. To begin with, input into the PHP file any code you would put into a typical HTML document e.g. the contents of the head and body elements. There are one or two things you may want to tweak though. For example, you could set the title to 'Search results':

<!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>

We will now introduce into the body element the PHP code which will retrieve the results from the SQL database. First and foremost, we will need to instruct the PHP page to log in to the SQL database. An example of this code is shown below. It includes some code at the end which will display an error message if for whatever reason the connection to the SQL database fails.

<?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();
	}
?>

Replace LOCALHOST, USERNAME, PASSWORD and DATABASE with the details of your specific SQL database. We created a username, password and database earlier and 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

Now our PHP page is connected to the SQL database, we can write the code which will retrieve the search results. The PHP code to do this is included below with comments. The code for the full PHP script without comments can be found further down the page if you want to copy and paste any of the code:

    $query = mysqli_real_escape_string($con, $_GET['search']); <!––'mysqli_real_escape_string' converts any special characters like punctuation into a format that it is understandable for the SQL database. '$_GET' retrieves the user's query.––>
    $up_query = htmlspecialchars($_GET['search']); <!––Converts any special characters like punctuation in the search query into HTML entities so they can be displayed as text and not interpreted as code.––>
	 
    $min_length = 2;
     
    if(strlen($query) >= $min_length){ <!–– The SQL database will be searched if the query is at least two characters long ––>
        
		<!––The following code will scan the description column of each entry in the SQL search database for any entries which match the user's query––>
		
        $raw_results = mysqli_query($con,"SELECT * FROM search
			WHERE (`description` LIKE '%$query%')") or die(mysqli_error());

        if(mysqli_num_rows($raw_results) > 0){
			
			echo "<h2>Search results for: $up_query</h2>";
             
            while($results = mysqli_fetch_array($raw_results)){
				$site_title=$results['title'];
				$site_link=$results['link'];
				$site_titlecaps=strtoupper($site_title);
				$site_preview=$results['preview'];
				
					<!––Below we display any matching search results.––>
					 
					 echo "<div class='search_result'> <!––We assign the search results the class 'search_result' so we can style them––>						
						<h3 style='color:RoyalBlue;'><a href='$site_link' style='color:RoyalBlue;'>$site_titlecaps</a></h3> <!––This code displays the name of the search result (drawn from the title column of the SQL database) and links to the relevant webpage. The colour of the link has been set to royal blue but you can change this.––>
						<article><a href='$site_link'>$site_preview</a></article> <!––A short description of the search result will be drawn from the 'preview' column of the SQL database will be displayed here and will link to the target webpage.––>				
						</div>";
            }
             
        }
        else{
            echo "<h2>NO RESULTS FOUND FOR YOUR SEARCH &quot;$up_query&quot;</h2>"; <!––This message will display if the search does not return any results.––>
        }
         
    }
    else{ 
        echo "<h2>Minimum search length is $min_length characters.</h2>"; <!––An error message will display if the user's query is only a single character long.––>
    }

The complete PHP file should look something like this:

<!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>
<?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();
	}
	
	    $query = mysqli_real_escape_string($con, $_GET['search']);
    $up_query = htmlspecialchars($_GET['search']);
	 
    $min_length = 2;
     
    if(strlen($query) >= $min_length){
		
        $raw_results = mysqli_query($con,"SELECT * FROM search
			WHERE (`description` LIKE '%$query%')") or die(mysqli_error());

        if(mysqli_num_rows($raw_results) > 0){
			
			echo "<h2>Search results for: $up_query</h2>";
             
            while($results = mysqli_fetch_array($raw_results)){
				$site_title=$results['title'];
				$site_link=$results['link'];
				$site_titlecaps=strtoupper($site_title);
				$site_preview=$results['preview'];
					 
					 echo "<div class='search_result'>					
						<h3 style='color:RoyalBlue;'><a href='$site_link' style='color:RoyalBlue;'>$site_titlecaps</a></h3>
						<article><a href='$site_link'>$site_preview</a></article>				
						</div>";
            }
             
        }
        else{
            echo "<h2>NO RESULTS FOUND FOR YOUR SEARCH &quot;$up_query&quot;</h2>";
        }
         
    }
    else{ 
        echo "<h2>Minimum search length is $min_length characters.</h2>";
    }
?>
</body>
</html>

We can style our search results using the 'main.css' stylesheet:

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

The 'color' property determines what colour the text of the search results will be, while the 'text-align' property ensures the search results line up down the center of the webpage.

<<< Previous

Next >>>