2 Steps to Search and Display Results From Database (PHP MySQL)

Welcome to a tutorial on how to search and display results from the database using PHP and MySQL. Want to add a search box to your website? Well, it actually isn’t that difficult.

In the simplest design, a “search and display results” only involves 2 steps.

  1. Create a simple HTML search form – With a text box, submit button, and pointed to a PHP search script.
  2. In the PHP search script, do a SELECT * FROM `TABLE` WHERE `FIELD` LIKE '%SEARCH%' SQL query and output the results in HTML.

But just how is this done exactly? Let us walk through an example in this guide – Read on!

ⓘ I have included a zip file with all the source code at the start of this tutorial, so you don’t have to copy-paste everything… Or if you just want to dive straight in.

 

 

REAL QUICK TUTORIAL

 

TABLE OF CONTENTS

Download & Notes Dummy Database Search Example
AJAX Search Useful Bits & Links The End

 

DOWNLOAD & NOTES

Firstly, here is the download link to the example code as promised.

 

EXAMPLE CODE DOWNLOAD

Click here to download the source code, I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

QUICK NOTES

  • Create a dummy database and import 0-users.sql.
  • Change the database settings in 2-search.php to your own.
  • Launch 1-form.php or 3-ajax-search.html in the browser.

If you spot a bug, please feel free to comment below. I try to answer questions too, but it is one person versus the entire world… If you need answers urgently, please check out my list of websites to get help with programming.

 

 

DUMMY DATABASE

Before we go into the example code, let us start by creating a dummy user table.

 

DUMMY USERS TABLE

0-users.sql
CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `name` (`name`);
 
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
Field Description
id Primary key, the user ID.
name Indexed, the full name of the user.
email Unique, email of the user, and just so that they cannot register twice.

 

 

DUMMY DATA

0-users.sql
INSERT INTO `users` (`id`, `name`, `email`) VALUES
(1, 'John Doe', 'john@doe.com'),
(2, 'Jane Doe', 'jane@doe.com'),
(3, 'Apple Doe', 'apple@doe.com'),
(4, 'Beck Doe', 'beck@doe.com'),
(5, 'Charlie Doe', 'charlie@doe.com'),
(6, 'Charles Doe', 'charles@doe.com'),
(7, 'Dion Doe', 'dion@doe.com'),
(8, 'Dee Doe', 'dee@doe.com'),
(9, 'Emily Doe', 'emily@doe.com'),
(10, 'Ethan Doe', 'ethan@doe.com');

 

 

STEP 1) HTML SEARCH FORM

1-form.php
<!-- (A) SEARCH FORM -->
<form method="post" action="1-form.php">
  <h1>SEARCH FOR USERS</h1>
  <input type="text" name="search" required/>
  <input type="submit" value="Search"/>
</form>

<?php
// (B) PROCESS SEARCH WHEN FORM SUBMITTED
if (isset($_POST['search'])) {
  // (B1) SEARCH FOR USERS
  require "2-search.php";

  // (B2) DISPLAY RESULTS
  if (count($results) > 0) {
    foreach ($results as $r) {
      printf("<div>%s - %s</div>", $r['name'], $r['email']);
    }
  } else { echo "No results found"; }
}
?>

No rocket science here – This should be very straightforward.

  • A – The top half is nothing but a simple HTML search form. Only has one text field and submits to itself.
  • B – The bottom PHP segment does the actual search.
    • B1 – if (isset($_POST['search'])) { require "2-search.php"; }. This basically includes the PHP search script only when the HTML search form is submitted.
    • B2 – Finally, output the search results in HTML.

 

 

STEP 2) PHP SEARCH SCRIPT

2-search.php
<?php
// (A) DATABASE CONFIG - CHANGE TO YOUR OWN!
define('DB_HOST', 'localhost');
define('DB_NAME', 'test');
define('DB_CHARSET', 'utf8');
define('DB_USER', 'root');
define('DB_PASSWORD', '');

// (B) CONNECT TO DATABASE
try {
  $pdo = new PDO(
    "mysql:host=".DB_HOST.";charset=".DB_CHARSET.";dbname=".DB_NAME,
    DB_USER, DB_PASSWORD, [
       PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]
  );
} catch (Exception $ex) { exit($ex->getMessage()); }

// (C) SEARCH
$stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name` LIKE ? OR `email` LIKE ?");
$stmt->execute(["%".$_POST['search']."%", "%".$_POST['search']."%"]);
$results = $stmt->fetchAll();
if (isset($_POST['ajax'])) { echo json_encode($results); }

Yep, the search script is also as simple as 1-2-3.

  • A & B – At the top half, we make a connection to the database. Remember to change the database settings to your own.
  • C – The bottom half runs a SELECT * FROM `TABLE` WHERE `FILED` LIKE '%SEARCH%' SQL search query on the database table. The search results $results will then be picked up in the above 1-search.php.

 

 

AJAX SEARCH

The above example works, but the stone age of the Internet is over… So how do we do a search without reloading the entire page? Introducing – AJAX (Asynchronous JavaScript and XML).

 

THE HTML

3-ajax-search.html
<!-- (A) SEARCH FORM -->
<form onsubmit="return fetch();">
  <h1>SEARCH FOR USERS</h1>
  <input type="text" id="search" required/>
  <input type="submit" value="Search"/>
</form>

<!-- (B) SEARCH RESULTS -->
<div id="results"></div>

Yep, the HTML for AJAX searches remain the same. Just take note that we are using Javascript to manage the form submission – <form onsubmit="return fetch();">

 

THE JAVASCRIPT

3-ajax-search.html
<script>
function fetch() {
  // (A) GET SEARCH TERM
  var data = new FormData();
  data.append('search', document.getElementById("search").value);
  data.append('ajax', 1);
 
  // (B) AJAX SEARCH REQUEST
  var xhr = new XMLHttpRequest();
  xhr.open('POST', "2-search.php");
  xhr.onload = function () {
    var results = JSON.parse(this.response),
    wrapper = document.getElementById("results");
    if (results.length > 0) {
      wrapper.innerHTML = "";
      for (let res of results) {
        let line = document.createElement("div");
        line.innerHTML = `${res['name']} - ${res['email']}`;
        wrapper.appendChild(line);
      }
    } else { wrapper.innerHTML = "No results found"; }
  };
  xhr.send(data);
  return false;
}
</script>

This can be a little intimidating at first, but take your time to slowly trace through.

  • A – All this does is essentially get the search term from the HTML form.
  • B – Submit the search term to the PHP search script via AJAX. Upon receiving the search results, show the results in <div id="results">.

 

 

USEFUL BITS & LINKS

That’s all for the search example, and here are a few extra bits that may be useful to you.

 

SEARCH SECURITY

A gentle reminder – This guide has only walked through the basics of a search system. A good system should have more security checks and stuff. For example, check if an administrator is signed in before processing a search for sensitive data. Also on the list, good to read up on CSRF and SQL injection. Links below.

 

INDEX THE SEARCHABLE FIELDS!

Take note that both the name and email fields in the above examples are indexed –  ADD UNIQUE KEY `email` (`email`) and ADD KEY `name` (`name`). Some of you beginners will be wondering about what this does.

To explain that, I will give an analogy of someone searching for a book in a library. Now, it will be easy to find if the library keeps a good catalog of the books they have. But just imagine that the library is just a big random mess of books… You will literally have to look through each and every book to find what you want.

It is the same here with the database, and indexing is creating a “catalog” for that selected field. While it does take up more hard disk space, but it will also speed up the searches. So when working with your projects in the future, please do remember to index the fields that will be used for searching.

 

LINKS & REFERENCES

 

YOUTUBE TUTORIAL

 

INFOGRAPHIC CHEAT SHEET

How To Search & Display Data From Database In PHP (click to enlarge)

 

THE END

Thank you for reading, and we have come to the end of this tutorial. I hope it has explained how to do PHP database searches for you, and if you have anything to share with this guide, please feel free to comment below. Good luck and happy coding!

24 thoughts on “2 Steps to Search and Display Results From Database (PHP MySQL)”

  1. Hi I’m really happy if you do a guide on a search system that shows the results accurately for example:
    If I say I write Dad, then only the results that appear are Dad, and not for example if I search for Dad, a Dads result will also appear.

  2. please how do i make my search to display what i search for, at the top then related ones at the bottom result? E.g i search for “html beginner”. it should display result with html beginner at the top result follow by related result like “advance html beginer” Or “start with html beginner” e.t.c

  3. Hi! The %search% and the rest of the code is secure against sql injections? If not what can i use to prevent it. Thanks 🙂

    1. I think it is better to learn by experimenting and answering for yourself. What is injection? Here is an easy example of a bad unprotected query:

      $search = "' OR 1=1 OR '"; // OR 1=1 will literally return all entries
      $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name`='$search'");
      $stmt->execute(null);
      $results = $stmt->fetchAll();
      print_r($results); // ALL USERS
      

      Now pass the search query through the PDO execute() function and see what happens.

      $search = "' OR 1=1 OR '";
      $stmt = $pdo->prepare("SELECT * FROM `users` WHERE `name`=?");
      $stmt->execute([$search]);
      $results = $stmt->fetchAll();
      print_r($results); // NOTHING
      

      Now try it for yourself, see if you can get the execute() function to return everything.

  4. How to add to search for another field like i have name email and mobile so the mobile not searching please give the code f

    1. 1) Add a new mobile column in the database table.
      2) 2-search.php > SELECT * FROM `users` WHERE `mobile` LIKE “%SEARCH%”
      3) 3-ajax-search.html > line.innerHTML = res[‘name’] + ” – ” + res[’email’] + ” – ” + res[‘mobile’];

      1. thanks a lot for the useful code and accurate explanation
        would you tell us how to use inner join in the 2-search to search from several tables

  5. Awesome!!! The ajax works fine in chrome and Firefox but doesn’t seems to work in IE 11.
    Do you happen to know the reason for that?

    Thanks

  6. Hi W.S Toh,
    Thank you for sharing the code.
    How can I add css style to res[‘name’] + ” – ” + res[’email’];?

    All the best,
    Hanna

  7. Dear can You do this for me ” When we hit Search Button, Result Displayed on HTML Table created with css” I just tried but i failed eveytime. Please

  8. Hi,
    thank you for this tutorial, but I miss to see in the code “require_once” instead of only “require”.

Leave a Comment

Your email address will not be published. Required fields are marked *