3 Steps to Search & 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.

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

 

 

TABLE OF CONTENTS

 

 

EXAMPLE CODE DOWNLOAD

Source code on GitHub Gist

Just click on “download zip” or do a git clone. I have released it under the MIT license, so feel free to build on top of it or use it in your own project.

 

SORRY FOR THE ADS...

But someone has to pay the bills, and sponsors are paying for it. I insist on not turning Code Boxx into a "paid scripts" business, and I don't "block people with Adblock". Every little bit of support helps.

Buy Me A Coffee Code Boxx eBooks

 

 

 

TUTORIAL VIDEO

 

PART 1) THE DATABASE

1A) DUMMY USERS TABLE

CREATE TABLE `users` (
  `id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `name` (`name`);
 
ALTER TABLE `users`
  MODIFY `id` bigint(20) 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.

 

 

1B) DUMMY DATA

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');

 

PART 2) HTML SEARCH FORM

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

<?php
// (B) PROCESS SEARCH WHEN FORM SUBMITTED
if (isset($_POST["search"])) {
  // (B1) SEARCH FOR USERS
  require "3-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 "3-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.

 

 

PART 3) PHP SEARCH SCRIPT

<?php
// (A) DATABASE CONFIG - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");
 
// (B) CONNECT TO DATABASE
$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
]);

// (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.

 

 

EXTRA) AJAX SEARCH

EXTRA-1) THE HTML

<!-- (A) SEARCH FORM -->
<form id="form" onsubmit="return asearch();">
  <input type="text" name="search" placeholder="Search..." required>
  <input type="submit" value="Search">
</form>

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

What if we want to do an “AJAX search”, doing a search without reloading the entire page? The HTML remains the same, just take note that we are using Javascript to manage the form submission – <form onsubmit="return asearch();">

 

EXTRA-2) THE JAVASCRIPT

<script>
function ajsearch () {
  // (A) GET SEARCH TERM
  var data = new FormData(document.getElementById("form"));
  data.append("ajax", 1);
 
  // (B) AJAX SEARCH REQUEST
  fetch("3-search.php", { method:"POST", body:data })
  .then(res => res.json())
  .then(res => {
    var wrapper = document.getElementById("results");
    if (res.length > 0) {
      wrapper.innerHTML = "";
      for (let r of res) {
        let line = document.createElement("div");
        line.innerHTML = `${r["name"]} - ${r["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">.

 

 

 

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 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, 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

• SQL Injection & Prevention – Code Boxx
• Simple CSRF Protection Token – Code Boxx
• PHP Data Objects (Database) – PHP.net
• MySQL Indexes – Tutorials Point
• How To Do MySQL Search (Exact, Like, Fuzzy) – Code Boxx
• Simple AJAX Tutorial – Code Boxx
• How to Create A Table From Array in JS – Code Boxx
• How to Import SQL File In MySQL – Code Boxx