PHP MySQL Ajax Search

Previous Page Next Page

Ajax Live Database Search

You can implement a simple live database search feature using Ajax and PHP. As you type characters into the search input box, the results will be displayed instantly.

In this tutorial, we will create a live search box that searches the countries table and displays the results asynchronously. First, we need to create the table.

Step 1: Creating the Database Table

Run the following SQL query to create the countries table in your MySQL database.

Example

Download

CREATE TABLE countries (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);

After creating the table, you should populate it with data using the SQL INSERT statement. Alternatively, you can download a prepopulated countries table by clicking the download button and importing it into your MySQL database.

For detailed information about the syntax for creating tables in MySQL database systems, please refer to the tutorial on SQL CREATE TABLE statement.

Step 2: Creating the Search Form

Next, let's create a simple web interface that allows users to perform live searches for country names from our countries table, similar to autocomplete or typeahead functionality.

Create a PHP file named "search-form.php" and insert the following code into it.

Example

Download

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Live MySQL Database Search</title>
<style>
body{
font-family: Arail, sans-serif;
}
/* Formatting search box */
.search-box{
width: 300px;
position: relative;
display: inline-block;
font-size: 14px;
}
.search-box input[type="text"]{
height: 32px;
padding: 5px 10px;
border: 1px solid #CCCCCC;
font-size: 14px;
}
.result{
position: absolute;        
z-index: 999;
top: 100%;
left: 0;
}
.search-box input[type="text"], .result{
width: 100%;
box-sizing: border-box;
}
/* Formatting result items */
.result p{
margin: 0;
padding: 7px 10px;
border: 1px solid #CCCCCC;
border-top: none;
cursor: pointer;
}
.result p:hover{
background: #f2f2f2;
}
</style>
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>
<script>
$(document).ready(function(){
$('.search-box input[type="text"]').on("keyup input", function(){
/* Get input value on change */
var inputVal = $(this).val();
var resultDropdown = $(this).siblings(".result");
if(inputVal.length){
$.get("backend-search.php", {term: inputVal}).done(function(data){
// Display the returned data in browser
resultDropdown.html(data);
});
} else{
resultDropdown.empty();
}
});

// Set search input value on click of result item
$(document).on("click", ".result p", function(){
$(this).parents(".search-box").find('input[type="text"]').val($(this).text());
$(this).parent(".result").empty();
});
});
</script>
</head>
<body>
<div class="search-box">
<input type="text" autocomplete="off" placeholder="Search country..." />
<div class="result"></div>
</div>
</body>
</html>

Whenever the content of the search input changes or a keyup event occurs, the jQuery code (from line 47 to 67) sends an Ajax request to the "backend-search.php" file. This file retrieves records from the countries table that match the searched term. These records are then inserted into a <div> by jQuery and displayed in the browser.

Step 3: Processing Search Query in Backend

Below is the source code of our "backend-search.php" file, which handles the database search based on the query string sent by the Ajax request and sends the results back to the browser.

Example

Procedural Object Oriented PDO

Download

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");

// Check connection
if($link === false){
die("ERROR: Could not connect. " . mysqli_connect_error());
}

if(isset($_REQUEST["term"])){
// Prepare a select statement
$sql = "SELECT * FROM countries WHERE name LIKE ?";

if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "s", $param_term);

// Set parameters
$param_term = $_REQUEST["term"] . '%';

// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
$result = mysqli_stmt_get_result($stmt);

// Check number of rows in the result set
if(mysqli_num_rows($result) > 0){
// Fetch result rows as an associative array
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
echo "<p>" . $row["name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
}

// Close statement
mysqli_stmt_close($stmt);
}

// close connection
mysqli_close($link);
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");

// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}

if(isset($_REQUEST["term"])){
// Prepare a select statement
$sql = "SELECT * FROM countries WHERE name LIKE ?";

if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("s", $param_term);

// Set parameters
$param_term = $_REQUEST["term"] . '%';

// Attempt to execute the prepared statement
if($stmt->execute()){
$result = $stmt->get_result();

// Check number of rows in the result set
if($result->num_rows > 0){
// Fetch result rows as an associative array
while($row = $result->fetch_array(MYSQLI_ASSOC)){
echo "<p>" . $row["name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
}

// Close statement
$stmt->close();
}

// Close connection
$mysqli->close();
?>

<?php
/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}

// Attempt search query execution
try{
if(isset($_REQUEST["term"])){
// create prepared statement
$sql = "SELECT * FROM countries WHERE name LIKE :term";
$stmt = $pdo->prepare($sql);
$term = $_REQUEST["term"] . '%';
// bind parameters to statement
$stmt->bindParam(":term", $term);
// execute the prepared statement
$stmt->execute();
if($stmt->rowCount() > 0){
while($row = $stmt->fetch()){
echo "<p>" . $row["name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
}  
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}

// Close statement
unset($stmt);

// Close connection
unset($pdo);
?>

The SQL SELECT statement uses the LIKE operator (line 16) to find matching records in the countries database table. We've implemented prepared statements to enhance search performance and protect against SQL injection attacks.

Note: It's crucial to filter and validate user input before using it in a SQL statement. You can utilize PHP's mysqli_real_escape_string() function to escape special characters in user input, ensuring that it creates a safe SQL string and protects against SQL injection.

Previous Page Next Page