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.

 

 

QUICK SLIDES

 

TABLE OF CONTENTS

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

 

DOWNLOAD & NOTES

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

 

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, feel free to comment below. I try to answer short 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.

 

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.

 

 

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.

  1. The top half is nothing but a simple HTML search form. Only has one text field and submits to itself.
  2.  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 ajsearch();">
  <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 remains 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 ajsearch () {
  // (A) GET SEARCH TERM
  var data = new FormData();
  data.append("search", document.getElementById("search").value);
  data.append("ajax", 1);
 
  // (B) AJAX SEARCH REQUEST
  fetch("2-search.php", { method:"POST", body:data })
  .then(res => res.json()).then((results) => {
    var 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"; }
  });
  return false;
}
</script>

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

  1. All this does is essentially get the search term from the HTML form.
  2. 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

 

TUTORIAL VIDEO

 

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!

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

  1. Hi Dear
    Your post is very useful and thanks for this.
    How can we use hybrid commands OR and AND and LIKE in search query?
    I have this code and not working right:
    …………………………………………………….
    $sql = “SELECT * FROM `ard_post` WHERE post_dis_status=1 AND post_type=2 AND post_title LIKE ‘%$search%’ OR post_subtitle LIKE ‘%$search%’ OR post_text LIKE ‘%$search%’ OR post_writer LIKE ‘%$search%’OR post_sender LIKE ‘%$search%’ ORDER BY ID DESC”;
    …………………………………………………….

    Of course search is working well but the conditions of post_dis_status=1 AND post_type=2 does not apply and in the search page showed all of fields for example showed fields with post_dis_status=0.

  2. If I added city and state to the DB and indexed both, what do I add to the code to be able to search for either of those?
    I have added city and state to 2-search.php here,
    $stmt = $pdo->prepare(“SELECT * FROM `users` WHERE `name` LIKE ? OR `email` LIKE ?”) OR `city` LIKE ?”) OR `state` LIKE ?”);
    And to 3-ajax-search.html here,
    line.innerHTML = `${res[‘name’]} – ${res[’email’]} – ${res[‘city’]} – ${res[‘state’]}`;

    And it doesn’t work.

  3. Thanks
    Very useful, logical and well presented.
    Is the code difficult that would clear the previous result displayed, when clicking back into the search window? The reason I ask is when I get a ‘No result found’ and I repeat the search with variation, it would be reassuring that there is no such data rather than maybe a process error. Seeing the message ‘No result found’ reappear would be reliable indicator.
    Great job!

      1. I was just making the observation that if you keep using a search term that has not match in the DB nothing changed in the message – after clicking the Search button, leaving one wonder if it’s a problem with one’s code or just that all searches are turning up no match.

        However I note, refreshing the browser between search solves this issue.

      2. I think I catch what you mean now – The user does multiple searches and all of them show “No results found”. This may confuse the user, and the traditional “submit form and refresh page” is better in the sense that there is a page reload and “loading feedback”.

        I have an alternative –

        echo "No results were found for the search term: " . $_POST["search"]

        wrapper.innerHTML = "No results were found for the search term: " + document.getElementById("search").value

        Also, add “search took XX seconds” at the bottom if you wish.

  4. 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.

  5. 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

Leave a Comment

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