Search form in PHP using mysqli

by Sam  


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.



Leave a Reply

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


  1. sabtaji says:

    i want view search by Id how create it boss

  2. Jason says:

    there is an error when i changed it into like statement

  3. admin says:

    Hello Jason, what was the error?

  4. soren says:

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

    1. Sam says:

      Hi Soren, How about htmlspecialchars and mysqli_real_escape_string?

  5. Jomarie says:

    i cant execute search. it wont show the result

  6. simon says:

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

  7. pene mas grande says:

    Hello mates, its enormous article regarding cultureand fully explained, keep it
    up all the time.

Need of a Website?

Maybe you're planning to build a landing page for your website. You might also have a plan to create a web application. I can offer you with an affordable but with good quality services. Don't hesitate to submit your specifications by clicking the button below.

Get Quote