Watch out! This tutorial is over 6 years old. Please keep this in mind as some code snippets provided may no longer work or need modification to work on current systems.
Tutorial Difficulty Level You can create a simple live database search functionality utilizing the Ajax and PHP, where the search results will be displayed as you start typing some character in search input box.
In this tutorial we’re going to create a live search box that will search the countries table and show the results asynchronously. But, first of all we need to create this table.
Step 1: Creating the Database Table
Execute the following SQL query to create the countries table in your MySQL database.
CREATE TABLE countries (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
After creating the table, you need to populate it with some data using the SQL INSERT statement. Something like this:
CREATE TABLE IF NOT EXISTS `countries` ( `id` int(3) NOT NULL AUTO_INCREMENT, `name` varchar(35) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=130 ; INSERT INTO `countries` (`id`, `name`) VALUES (1, 'Afghanistan'), (2, 'Albania'), (3, 'Algeria'), (4, 'Angola'), (5, 'Argentina'), (6, 'Armenia'), (7, 'Australia'), (8, 'Austria'), (9, 'Bahrain'), (10, 'Bangladesh'), (11, 'Belarus'), (12, 'Belgium'), (13, 'Bhutan'), (14, 'Bolivia'), (15, 'Bosnia & Herzegovina'), (16, 'Botswana'), (17, 'Brazil'), (18, 'Bulgaria'), (19, 'Cambodia'), (20, 'Cameroon'), (21, 'Canada'), (22, 'Chile'), (23, 'China'), (24, 'Colombia'), (25, 'Costa Rica'), (26, 'Croatia'), (27, 'Cuba'), (28, 'Cyprus'), (29, 'Czech Republic'), (30, 'Denmark'), (31, 'Ecuador'), (32, 'Egypt'), (33, 'Estonia'), (34, 'Ethiopia'), (35, 'Fiji'), (36, 'Finland'), (37, 'France'), (38, 'Germany'), (39, 'Ghana'), (40, 'Greece'), (41, 'Greenland'), (42, 'Guinea'), (43, 'Guyana'), (44, 'Haiti'), (45, 'Honduras'), (46, 'Hong Kong'), (47, 'Hungary'), (48, 'Iceland'), (49, 'India'), (50, 'Indonesia'), (51, 'Iran'), (52, 'Iraq'), (53, 'Ireland'), (54, 'Israel'), (55, 'Italy'), (56, 'Japan'), (57, 'Jersey'), (58, 'Jordan'), (59, 'Kazakhstan'), (60, 'Kenya'), (61, 'Kuwait'), (62, 'Kyrgyzstan'), (63, 'Lebanon'), (64, 'Liberia'), (65, 'Libya'), (66, 'Lithuania'), (67, 'Luxembourg'), (68, 'Macedonia'), (69, 'Madagascar'), (70, 'Malaysia'), (71, 'Maldives'), (72, 'Mali'), (73, 'Mauritius'), (74, 'Mexico'), (75, 'Monaco'), (76, 'Mongolia'), (77, 'Morocco'), (78, 'Namibia'), (79, 'Nepal'), (80, 'Netherlands'), (81, 'New Zealand'), (82, 'Nigeria'), (83, 'North Korea'), (84, 'Norway'), (85, 'Oman'), (86, 'Pakistan'), (87, 'Panama'), (88, 'Papua New Guinea'), (89, 'Paraguay'), (90, 'Peru'), (91, 'Philippines'), (92, 'Poland'), (93, 'Portugal'), (94, 'Qatar'), (95, 'Romania'), (96, 'Russia'), (97, 'Rwanda'), (98, 'Saudi Arabia'), (99, 'Serbia'), (100, 'Singapore'), (101, 'Slovakia'), (102, 'Slovenia'), (103, 'South Africa'), (104, 'South Korea'), (105, 'Spain'), (106, 'Sri Lanka'), (107, 'Sudan'), (108, 'Sweden'), (109, 'Switzerland'), (110, 'Syria'), (111, 'Taiwan'), (112, 'Tajikistan'), (113, 'Tanzania'), (114, 'Thailand'), (115, 'Tunisia'), (116, 'Turkey'), (117, 'Turkmenistan'), (118, 'Uganda'), (119, 'Ukraine'), (120, 'United Arab Emirates'), (121, 'United Kingdom'), (122, 'United States'), (123, 'Uruguay'), (124, 'Uzbekistan'), (125, 'Venezuela'), (126, 'Vietnam'), (127, 'Yemen'), (128, 'Zambia'), (129, 'Zimbabwe');
Step 2: Creating the Search Form
Now, let’s create a simple web interface that allows user to live search the names of countries available in our countries table, just like an autocomplete or typeahead.
Create a PHP file named “search-form.php” and put the following code inside of it:
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>PHP Live MySQL Database Search</title> <style type="text/css"> 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-1.12.4.min.js"></script> <script type="text/javascript"> $(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>
Every time the content of search input is changed or keyup event occur on search input the jQuery code (line no-47 to 67) sent an Ajax request to the “backend-search.php” file which retrieves the records from countries table related to the searched term. Those records later will be inserted inside a <div> by the jQuery and displayed on the browser.
Step 3: Processing Search Query in Backend
And here’s the source code of our “backend-search.php” file which searches the database based on query string sent by the Ajax request and send the results back to browser.
<?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); ?>
If you’d prefer to use PHP Data Objects (PDO), then this would be the code:
<?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 is used in combination with the LIKE operator (line no-16) to find the matching records in countries database table. We’ve implemented the prepared statement for better search performance as well as to prevent the SQL injection attack.
Always filter and validate user input before using it in a SQL statement. You can also use PHP
mysqli_real_escape_string() function to escape special characters in a user input and create a legal SQL string to protect against SQL injection. 