PHP Search Engine Script for MySQL Database using AJAX

Hi this time I have come up with an interesting topic - Creating PHP Search Engine Script for MySQL Database. We are going to employ AJAX technology to send the HTTP request to the web service and show up instant suggestions while the user type in the search box which is quite similar to Google instant search. Accessing database server through AJAX and maintaining minimal server load is a real challenge to face.

If you think creating search engine scripts are complex and will have several hundreds of lines of code, then you are going to be surprised. Just with few lines of code, we can bring up a fully workable search engine solution in php. I’ll try to keep the process as simple as possible for any newbie to follow easily.

Twitter Typeahead JavaScript Library

I’m going to use twitter’s typeahead js library which will take care of all ajax communications to the web server. This is a clean and neat plug-in explicitly designed for this sort of purpose. Go here and here and download typeahead.js and jquery library, extract and move the files to your root folder.

Designing the MySQL Database for PHP Search Engine

Next we need to build a proper database with some dummy data to use as an example. Run this sql command to create a library database with some data.

CREATE DATABASE `library`;
USE `library`;

CREATE TABLE IF NOT EXISTS `books` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `author` varchar(30) NOT NULL,
  `isbn` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6;

INSERT INTO `tbl_books` (`id`, `title`, `author`, `isbn`) VALUES
(1, 'Learning PHP, MySQL & JavaScript', 'Robin Nixon', 'ISBN-13: 978-1491918661'),
(2, 'PHP and MySQL for Dynamic Web Sites', 'Larry Ullman', 'ISBN-13: 978-0321784070'),
(3, 'PHP Cookbook', 'David Sklar', 'ISBN-13: 978-1449363758'),
(4, 'Programming PHP', 'Kevin Tatroe', 'ISBN-13: 978-1449392772'),
(5, 'Modern PHP: New Features and Good Practices', 'Josh Lockhart', 'ISBN-13: 978-1491905012');

Having the required libraries and sample database in place, let's move on to the coding part.

Creating PHP Search Engine Script for MySQL Database

We need to create two files here. One thing is the frontend index.html - this act as the user interface which contains a search box and allows the user to search for the books by their title. The second one is the backend php script search.php which accesses the mysql database and returns the book titles that matches the search string. The response to the HTTP GET request will be in JSON and it will be shown under the search box like Google instant suggestion.

index.html

<html>
<head>
    <title>AJAX PHP Search Engine Script for MySQL Database</title>
    <style type="text/css">
    .se-example    {
        font-family: sans-serif;
        position: relative;
        margin: 100px;
    }
    .typeahead {
        background-color: #FFFFFF;
    }
    .typeahead:focus {
        border: 1px solid #999999;
    }
    .tt-query {
        box-shadow: 0 1px 1px rgba(0, 0, 0, 0.075) inset;
    }
    .tt-hint {
        color: #999999;
    }
    .typeahead, .tt-query, .tt-hint {
        border: 1px solid #CCCCCC;
        border-radius: 4px;
        font-size: 16px;
        height: 38px;
        line-height: 30px;
        outline: medium none;
        padding: 8px 12px;
        width: 396px;
    }
    .tt-dropdown-menu {
        background-color: #FFFFFF;
        border: 1px solid rgba(0, 0, 0, 0.2);
        border-radius: 4px;
        box-shadow: 0 5px 10px rgba(0, 0, 0, 0.2);
        margin-top: 12px;
        padding: 8px 0;
        width: 422px;
    }
    .tt-suggestion {
        font-size: 16px;
        line-height: 24px;
        padding: 3px 20px;
    }
    .tt-suggestion p {
        margin: 0;
    }
    .tt-suggestion.tt-is-under-cursor {
        background-color: #999999;
        color: #FFFFFF;
    }
    </style>
</head>
<body>
    <div class="se-example">
        <input id="searchbox" type="text" class="typeahead tt-query" autocomplete="off" spellcheck="false" placeholder="Search for Book Name..."/>
    </div>

    <script src="js/jquery-1.10.2.js"></script>
    <script src="js/typeahead.js"></script>
    <script>
    $(document).ready(function(){
        $('#searchbox').typeahead({
            remote:'search.php?st=%QUERY',
            limit : 5
        });
    });
    </script>
</body>
</html>

The method typeahead() will turn any textbox into an instant search box and we pass only two options to the function.

The remote option will send the GET Request (AJAX call) to the search.php script with the search string as the query parameter. %QUERY will contain the user search string.

The limit option will limit the maximum number of suggestions pops up below the search box. Here it will suggest 5 book titles at the most.

Also note that it is important to set autocomplete="off" for the input - this will turn off browser’s auto complete option and keep this from messing up with our search engine suggestions.

The next thing is to create the php search engine script for mysql database. This php script will search for all the book titles containing the given query string and return it in the form of json.

search.php

<?php
$str = $_GET['st'];
$connection = mysqli_connect("localhost", "username", "password", "library");
$sql = "select title from books where title LIKE '%{$str}%'";
$result = mysqli_query($connection, $sql);

$array = array();
while($row = mysqli_fetch_assoc($result)) {
    $array[] = $row['title'];
}
echo json_encode($array);
?>

Here we convert the mysql resultset to json with the help of the function json_encode() and return it to the front-end.

That’s it! We have created a simple and efficient AJAX & PHP search engine. Now run index.html file in the browser and you can see a search box like this,

PHP AJAX Search Engine

Type something inside the box and you will be shown up with instant suggestions.

ajax php search engine for mysql database

You can increase or decrease the typeahead limit option to display more or less suggestions at the bottom.

Also Read:

That was all about building AJAX PHP Search Engine Script for MySQL Database. I hope this will be useful to you. Let me know your queries through comments.

6 comments:

dexmarkAugust 05, 2016 10:46 PM
I am not sure what is the thing that I am doing wrong, but it doesn't work. All that I changed was DB settings. When I type manual address /search.php?st=php I do get results, so connection is working, but there is nothing in search box. Also checked if jQuery has loaded, and I god positive response. Not even sure where to look for errors anymore. Any suggestions?
UnknownAugust 18, 2016 12:47 AM
I also have the same problem. Here is my code ...It dosn't work. I don't know whats the problem here.

index.php file here

(these two files are included)
(tags are not allowed in comments so i just add the code here)
https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"
js/typeahead.min.js"

$(document).ready(function(){
$('input.typeahead').typeahead({
name: 'countries',
prefetch: 'country.php?key=%QUERY',
limit: 10
});
});

.bs-example{
font-family: sans-serif;
position: relative;
margin: 100px;
}
.typeahead, .tt-query, .tt-hint {
border: 2px solid #CCCCCC;
border-radius: 8px;
font-size: 24px;
height: 30px;
line-height: 30px;
outline: medium none;
padding: 8px 12px;
width: 396px;
}
.typeahead {
background-color: #FFFFFF;
}
.typeahead:focus {
border: 2px solid #0097CF;
}
.tt-query {
box-shadow: 0 1px 1px rgba(0, 0, 0, 0.075) inset;
}
.tt-hint {
color: #999999;
}
.tt-dropdown-menu {
background-color: #FFFFFF;
border: 1px solid rgba(0, 0, 0, 0.2);
border-radius: 8px;
box-shadow: 0 5px 10px rgba(0, 0, 0, 0.2);
margin-top: 12px;
padding: 8px 0;
width: 422px;
}
.tt-suggestion {
font-size: 24px;
line-height: 24px;
padding: 3px 20px;
}
.tt-suggestion.tt-is-under-cursor {
background-color: #0097CF;
color: #FFFFFF;
}
.tt-suggestion p {
margin: 0;
}

class="bs-example"

type="text" class="typeahead tt-query" autocomplete="off" spellcheck="false"

country.php file here.

UnknownAugust 18, 2016 12:52 AM
Here is my code. Can you tell me where is the problem. Its not working.

index.php file.(tags are not allowed, so only code here)

script src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"
script src="js/typeahead.min.js"
script type="text/javascript"
$(document).ready(function(){
$('input.typeahead').typeahead({
name: 'countries',
prefetch: 'country.php?key=%QUERY',
limit: 10
});
});
/script
style type="text/css"
.bs-example{
font-family: sans-serif;
position: relative;
margin: 100px;
}
.typeahead, .tt-query, .tt-hint {
border: 2px solid #CCCCCC;
border-radius: 8px;
font-size: 24px;
height: 30px;
line-height: 30px;
outline: medium none;
padding: 8px 12px;
width: 396px;
}
.typeahead {
background-color: #FFFFFF;
}
.typeahead:focus {
border: 2px solid #0097CF;
}
.tt-query {
box-shadow: 0 1px 1px rgba(0, 0, 0, 0.075) inset;
}
.tt-hint {
color: #999999;
}
.tt-dropdown-menu {
background-color: #FFFFFF;
border: 1px solid rgba(0, 0, 0, 0.2);
border-radius: 8px;
box-shadow: 0 5px 10px rgba(0, 0, 0, 0.2);
margin-top: 12px;
padding: 8px 0;
width: 422px;
}
.tt-suggestion {
font-size: 24px;
line-height: 24px;
padding: 3px 20px;
}
.tt-suggestion.tt-is-under-cursor {
background-color: #0097CF;
color: #FFFFFF;
}
.tt-suggestion p {
margin: 0;
}
/style
head
body
div class="bs-example"
input type="text" class="typeahead tt-query" autocomplete="off" spellcheck="false"
/div
/body
/html

country.php

jNovember 07, 2016 6:28 AM
This is exactly what I was looking for, is there any wya to convert the search.php to use sqlite? been trying but unsuccessful..
jNovember 07, 2016 6:34 AM
This is exactly what I was looking for, I've been trying to augment the search.php to use sqlite but unsuccessful, any suggestions?