Search form in PHP using mysqli

The mysqli extension, or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. The mysqli extension is included with PHP versions 5 and later.

Previously, we used mysql function in manipulating data from our database. But later on, in PHP 7, mysql has been deprecated and is no longer used.

Below is the simple snippet for mysqli search form in PHP.

1. Setup your database

Create a database named : samueldb and import the sql schema below.


CREATE TABLE `users` (
`user_id` int(12) NOT NULL,
`firstname` varchar(30) NOT NULL,
`lastname` varchar(30) NOT NULL,
`address` varchar(150) NOT NULL,
`contact` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `users` (`user_id`, `firstname`, `lastname`, `address`, `contact`) VALUES
(1, 'John ', 'Doe', 'New York', '238729837'),
(2, 'Samuel', 'No lastname', 'Earth', '28372983'),
(3, 'Peter', 'Pan', 'Oklahoma', 'none'),
(4, 'Vladimir', 'Kupti', 'Serbia', 'none');
ALTER TABLE `users`
ADD PRIMARY KEY (`user_id`);
ALTER TABLE `users`
MODIFY `user_id` int(12) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

2. Create a PHP file

After creating a PHP file, copy the PHP code below.


<?php
$localhost = "localhost";
$username = "root";
$password = "";
$dbname = "samueldb";
$con = new mysqli($localhost, $username, $password, $dbname);
if( $con->connect_error){
die('Error: ' . $con->connect_error);
}
$sql = "SELECT * FROM users";
if( isset($_GET['search']) ){
$name = mysqli_real_escape_string($con, htmlspecialchars($_GET['search']));
$sql = "SELECT * FROM users WHERE firstname ='$name'";
}
$result = $con->query($sql);
?>
<!DOCTYPE html>
<html>
<head>
<title>Basic Search form using mysqli</title>
<link rel="stylesheet" type="text/css"
href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<label>Search</label>
<form action="" method="GET">
<input type="text" placeholder="Type the name here" name="search">&nbsp;
<input type="submit" value="Search" name="btn" class="btn btn-sm btn-primary">
</form>
<h2>List of students</h2>
<table class="table table-striped table-responsive">
<tr>
<th>ID</th>
<th>First name</th>
<th>Lastname</th>
<th>Address</th>
<th>Contact</th>
</tr>
<?php
while($row = $result->fetch_assoc()){
?>
<tr>
<td><?php echo $row['user_id']; ?></td>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['address']; ?></td>
<td><?php echo $row['contact']; ?></td>
</tr>
<?php
}
?>
</table>
</div>
</body>
</html>

Let’s look deeper into the code.

a. Connect to the database

The first thing we should do is to connect to the database. We could not be able to retrieve data unless we are connected. If there is an error in your credentials, the script will stop and display an error message in line die('Error: ' . $con->connect_error);


$localhost = "localhost";
$username = "root";
$password = "";
$dbname = "samueldb";
$con = new mysqli($localhost, $username, $password, $dbname);

if( $con->connect_error){
die('Error: ' . $con->connect_error);
}

b. Retrieve data using mysql query.

$sql = "SELECT * FROM users";
if( isset($_GET['search']) ){
$name = mysqli_real_escape_string($con, htmlspecialchars($_GET['search']));
$sql = "SELECT * FROM users WHERE firstname ='$name'";
}
$result = $con->query($sql);

In order to add security on our query, we need to put mysqli_real_escape_string and htmlspecialchars as well. This will prevent attackers from injecting sql queries and adding js scripts. example, if the user will put quotes or html tags, the php script will return error.

From the sql query, we used equal (=) symbol to compare firstname. However, this is only limited to one student. If you desire to display records which contain a specific search string, you may prefer to use LIKE instead of equals example, "SELECT * FROM users WHERE firstname LIKE '%$name%'"

c. Display result in your browser


<?php
while($row = $result->fetch_assoc()){
?>
<tr>
<td><?php echo $row['user_id']; ?></td>
<td><?php echo $row['firstname']; ?></td>
<td><?php echo $row['lastname']; ?></td>
<td><?php echo $row['address']; ?></td>
<td><?php echo $row['contact']; ?></td>
</tr>
<?php
}
?>

If you have any suggestions, don’t hesitate to comment below.

7 Comments

  1. i want view search by Id how create it boss

  2. there is an error when i changed it into like statement

  3. Hello Jason, what was the error?

  4. escape special character like /-“”is missing. So that the user can not enter it inside searchbox.
    Prevent typing that I mean in searchbox.

    1. Hi Soren, How about htmlspecialchars and mysqli_real_escape_string?

  5. i cant execute search. it wont show the result

  6. this code produces all the rows in the the database even before any search is done.

Leave a Reply

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