How to Create a Simple Search on MySQL Table using PHP, MySQLi and AJAX

Submitted by nurhodelta_17 on Tuesday, August 29, 2017 - 14:52.

In this tutorial, I'll show you how to create a simple search on mysql table using PHP, MySQLi and AJAX. This tutorial does not include a good design but will give you an idea on how to search for rows in mysql table.

Creating our Database

First, we're going to create our database. This contains the data that we are going to search. 1. Open phpMyAdmin. 2. Click databases, create a database and name it as "ajax_search". 3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.

CREATE TABLE `user` (
  `userid` INT(11) NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(30) NOT NULL,
  `lastname` VARCHAR(30) NOT NULL,
PRIMARY KEY(`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Inserting Data into our Database

Next step is to insert example data into our database. This will serve as our sample data. 1. Click "ajax_search" database that we have created earlier. 2. Click SQL and paste the code below.

INSERT INTO `user` (`firstname`, `lastname`) VALUES
('neovic', 'devierte'),
('lee', 'ann'),
('julyn', 'divinagracia'),
('jaira', 'jacinto');

Creating our Connection

Next step is to create a database connection and save it as "conn.php". This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.

<?php
 
//MySQLi Procedural
$conn = mysqli_connect("localhost","root","","ajax_search");
if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
}
 
?>

Creating our Form and our AJAX code

Next, we create our search form and our ajax code. This page will show if there are rows that matches our search. We name this as "index.php".

<!DOCTYPE html>
<html>
<head>
<script>
function showResult(str) {
    if (str.length == 0) { 
        document.getElementById("search").innerHTML = "";
        return;
    } else {
        var xmlhttp = new XMLHttpRequest();
        xmlhttp.onreadystatechange = function() {
            if (this.readyState == 4 && this.status == 200) {
                document.getElementById("search").innerHTML = this.responseText;
                                
            }
        };
        xmlhttp.open("GET", "getresult.php?q=" + str, true);
        xmlhttp.send();
    }
}
</script>
</head>
<body>
 
<form> 
First name: <input type="text" onkeyup="showResult(this.value)">
<br><br>
Results Found: <div id="search"></div>
</form>
 
</body>
</html>

Creating our Result Code

Lastly, we create our result code that will process user input and return the result back to our index.php. We name the code as "getresult.php".

<?php
// get the q parameter from URL
$q = $_REQUEST["q"];
 
$hint = "";
 
include('conn.php');
 
$fname=array();
$query=mysqli_query($conn,"select * from `user`");
while($row=mysqli_fetch_array($query)){
        $fname[]=$row['firstname'];
}
 
// lookup all hints from array if $q is different from "" 
if ($q !== "") {
    $q = strtolower($q);
    $len=strlen($q);
    foreach($fname as $name) {
        if (stristr($q, substr($name, 0, $len))) {
            if ($hint === "") {
                $hint = $name;
            } else {
                $hint .="<br> $name";
            }
        }
    }
}
 
echo $hint === "" ? "no suggestion" : $hint;
?>

How to Create a Simple Search on MySQL Table using PHP, MySQLi and AJAX

Creating our Database

Inserting Data into our Database

Creating our Connection

Creating our Form and our AJAX code

Creating our Result Code

Comments

this is pretty bad

Add new comment