How to 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;
Output:
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:
CREATE TABLE fruit
(id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
color VARCHAR(20) NOT NULL,
PRIMARY KEY (id))
ENGINE = InnoDB;
Output:
Query OK, 0 rows affected (0.028 sec)
Check the table’s structure:
DESC fruit;
Output:
+-------+-------------+------+-----+---------+----------------+
| 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')
Output:
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;
Output:
+----+-----------+--------+
| 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.
<main>
<form action="searchdb.php" method="post">
<input
type="text"
placeholder="Enter your search term"
name="search"
required>
<button type="submit" name="submit">Search</button>
</form>
</main>
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;
}
input,
button {
padding: 0.2em;
}
The HTML should be like the next image in your web browser.
Create the PHP Code
The PHP code will process the form submission. Here is a summary of how the code works:
- Check the user’s submitted form.
- Connect to the database.
- Escape the search string and trim all-white space.
- Check for invalid characters like
<or-(without quotes). - Perform the search via a prepared statement.
- 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.
<?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";
exit();
}
// 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";
exit();
}
// 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);
$prepared_stmt->execute();
// Fetch the result
$result = $prepared_stmt->get_result();
if ($result->num_rows === 0) {
// No match found
echo "No match found";
// Kill the script
exit();
} 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!";
exit();
}
?>
The next image contains the result for the search string pine. It returns the fruit pineapple and its color.
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.
LinkedIn