Set Up a Search System With PHP and MySQL

Habdul Hazeez Jan 06, 2022
  1. Setup a Database
  2. Create the HTML Code
  3. Create the PHP Code
Set Up a Search System With PHP and MySQL

This tutorial will teach you to create a search system with PHP and MySQL. You will learn how to set up the HTML, the MySQL database, and the PHP backend. In the PHP code, you’ll learn to use a prepared statement with the LIKE operator in SQL.

Setup a Database

Download and install the XAMPP server. It comes with MySQL. Launch the shell in the XAMPP control panel. Log in to the MySQL shell with the following command:

# This login command assumes that the
# password is empty and the user is "root"
mysql -u root -p

Use the following SQL query to create a database called fruit_db.

CREATE database fruit_db;


Query OK, 1 row affected (0.001 sec)

You will need sample data that you can use. So, execute the following SQL on the fruit_db database:

 color VARCHAR(20) NOT NULL,


Query OK, 0 rows affected (0.028 sec)

Check the table’s structure:

DESC fruit;


| Field | Type        | Null | Key | Default | Extra          |
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
| color | varchar(20) | NO   |     | NULL    |                |

Once the table is set up, use the following SQL to insert a sample data:

INSERT INTO fruit (id, name, color) VALUES (NULL, 'Banana', 'Yellow'), (NULL, 'Pineapple', 'Green')


Query OK, 2 rows affected (0.330 sec)
Records: 2  Duplicates: 0  Warnings: 0

Confirm the data existence with the following SQL:

SELECT * FROM fruit;


| id | name      | color  |
|  1 | Banana    | Yellow |
|  2 | Pineapple | Green  |

Create the HTML Code

The HTML code for the search system is an HTML form. The form has a single form input and a submit button. A required attribute in the form input ensures the users enter something in the form.

The next code block is the HTML code for the search form.

	<form action="searchdb.php" method="post">
			placeholder="Enter your search term"
		<button type="submit" name="submit">Search</button>

The following CSS makes the form more presentable.

* {
	margin: 0;
	padding: 0;
	box-sizing: border-box;

body {
	display: grid;
	align-items: center;
	place-items: center;
	height: 100vh;

main {
	width: 60%;
	border: 2px solid #1560bd;
	padding: 2em;
	display: flex;
	justify-content: center;

button {
	padding: 0.2em;

The HTML should be like the next image in your web browser.

search php in mysql - HTML Search form

Create the PHP Code

The PHP code will process the form submission. Here is a summary of how the code works:

  1. Check the user’s submitted form.
  2. Connect to the database.
  3. Escape the search string and trim all-white space.
  4. Check for invalid characters like < or - (without quotes).
  5. Perform the search via a prepared statement.
  6. Return the results.

The next code block is the complete PHP code to perform the search. Save the code in a file called searchdb.php.

    if (isset($_POST['submit'])) {
        // Connect to the database
        $connection_string = new mysqli("localhost", "root", "", "fruit_db");
        // Escape the search string and trim
        // all whitespace
        $searchString = mysqli_real_escape_string($connection_string, trim(htmlentities($_POST['search'])));

        // If there is a connection error, notify
        // the user, and Kill the script.
        if ($connection_string->connect_error) {
            echo "Failed to connect to Database";

        // Check for empty strings and non-alphanumeric
        // characters.
        // Also, check if the string length is less than
        // three. If any of the checks returns "true",
        // return "Invalid search string", and
        // kill the script.
        if ($searchString === "" || !ctype_alnum($searchString) || $searchString < 3) {
            echo "Invalid search string";

        // We are using a prepared statement with the
        // search functionality to prevent SQL injection.
        // So, we need to prepend and append the search
        // string with percent signs
        $searchString = "%$searchString%";

        // The prepared statement
        $sql = "SELECT * FROM fruit WHERE name LIKE ?";

        // Prepare, bind, and execute the query
        $prepared_stmt = $connection_string->prepare($sql);
        $prepared_stmt->bind_param('s', $searchString);

        // Fetch the result
        $result = $prepared_stmt->get_result();

        if ($result->num_rows === 0) {
            // No match found
            echo "No match found";
            // Kill the script

        } else {
            // Process the result(s)
            while ($row = $result->fetch_assoc()) {
                echo "<b>Fruit Name</b>: ". $row['name'] . "<br />";
                echo "<b>Fruit Color</b>: ". $row['color'] . "<br />";

            } // end of while loop
        } // end of if($result->num_rows)

    } else { // The user accessed the script directly

        // Tell them nicely and kill the script.
        echo "That is not allowed!";

The next image contains the result for the search string pine. It returns the fruit pineapple and its color.

search php in mysql - search result for the string pine

Habdul Hazeez avatar Habdul Hazeez avatar

Habdul Hazeez is a technical writer with amazing research skills. He can connect the dots, and make sense of data that are scattered across different media.


Related Article - PHP MySQL