PHP Search Using Mysql

Posted on by Alimon Karim

Searching data from mysql database table , at first we have to prepare  simple html design. Let see below html code to create a simple search field and a table.

1) Create Layout

<!DOCTYPE html>
<html lang="en">
<head>
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
     <meta charset="UTF-8">
     <title>PHP Search</title>
</head>
<body>
<div class="container">
   <div class="row">
   <div class="col-md-8 col-md-offset-2" style="margin-top: 5%;">
   <div class="row">
   <form action="" method="GET"> 
     <div class="col-md-6">
        <input type="text" name="search" class='form-control' placeholder="Search By Name" value="" > 
     </div>
     <div class="col-md-6 text-left">
        <button class="btn">Search</button>
     </div>
   </form>
   <br>
   <br>
   </div>

   <table class="table table-bordered">
     <tr>
        <th>Name</th>
        <th>Amount</th>
       <th>City</th>
     <tr>
     <tr>
       <td>Kamal</td>
       <td>10</td>
       <td>Dhaka</td>
     </tr>
    </table>

    </div>
    </div>
</div>
</body>
</html>

You will get below output for this html code.

2) Create Database Table

Example we have a database table call friends, and this table has five fields, Like below Image


Sql :

CREATE TABLE `friends` (
`id` smallint(5) NOT NULL,
`name` varchar(25) NOT NULL,
`amount` float NOT NULL,
`city` varchar(20) NOT NULL,
`created` datetime NOT NULL
);
ALTER TABLE `friends`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `friends`
  MODIFY `id` smallint(5) NOT NULL AUTO_INCREMENT;
COMMIT;

3) Fetch all data from table.

Now it is time to fetch all data from friends table. There has 2 parts.
First one connection with database.
2nd one is fetch data from table.
Example our database name is tutorial. So we can create a simple database connection like below.

$conn  =  new mysqli("localhost","root","your_password","tutorial");

Now fetch data we can write below sql.

$sql = "SELECT * FROM friends";

For execute sql we have to write below code

$result = $conn->query($sql);

Now our html code will change like below.

<body>
<div class="container">
<div class="row">
<div class="col-md-8 col-md-offset-2" style="margin-top: 5%;">
<div class="row">
//database connection and fetch result
<?php 
    $conn = new mysqli('localhost', 'root', '', 'tutorial');
    $sql = "SELECT * FROM friends";
    $result = $conn->query($sql);
?>

<form action="" method="GET"> 
<div class="col-md-6">
<input type="text" name="search" class='form-control' placeholder="Search By Name" value="" > 
</div>
<div class="col-md-6 text-left">
<button class="btn">Search</button>
</div>
</form>

<br>
<br>
</div>

<table class="table table-bordered">
  <tr>
     <th>Name</th>
     <th>Amount</th>
     <th>City</th>
  </tr>
  <?php while( $row = $result->fetch_object() ): ?>
  <tr>
     <td><?php echo $row->name ?></td>
     <td><?php echo $row->amount ?></td>
     <td><?php echo $row->city ?></td>
  </tr>
  <?php endwhile; ?>
</table>
</div>
</div>
</div>

Output we will get below image.

4) Search Data.

Here our form is in same page , and method we used GET. So we can change our code like below for search.

<?php 
$conn   = new mysqli('localhost', 'root', '', 'tutorial');
if(isset($_GET['search'])){
     $searchKey = $_GET['search']; // grab keyword
     $sql    = "SELECT * FROM friends WHERE name LIKE '%$searchKey%'";
}else
    $sql    = "SELECT * FROM friends";
			
    $result = $conn->query($sql);
?>

Here, We grab the search key using  $_GET, then applied search key in sql.
Now in search field if we want to keep search keyword. We have apply below code in search field.

<input type="text" name="search" class='form-control' placeholder="Search By Name" value=<?php echo @$_GET['search']; ?> >

So our final code will

<!DOCTYPE html>
<html lang="en">
<head>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <meta charset="UTF-8">
  <title>PHP Search</title>
</head>
<body>
<div class="container">
   <div class="row">
   <div class="col-md-8 col-md-offset-2" style="margin-top: 5%;">
   <div class="row">

   <?php 

     $conn = new mysqli('localhost', 'root', '', 'tutorial');
     if(isset($_GET['search'])){
        $searchKey = $_GET['search'];
        $sql = "SELECT * FROM friends WHERE name LIKE '%$searchKey%'";
     }else
     $sql = "SELECT * FROM friends";
     $result = $conn->query($sql);
   ?>

   <form action="" method="GET"> 
     <div class="col-md-6">
        <input type="text" name="search" class='form-control' placeholder="Search By Name" value=<?php echo @$_GET['search']; ?> > 
     </div>
     <div class="col-md-6 text-left">
      <button class="btn">Search</button>
     </div>
   </form>

   <br> 
   <br>
</div>

<table class="table table-bordered">
  <tr>
     <th>Name</th>
     <th>Amount</th>
     <th>City</th>
  </tr>
  <?php while( $row = $result->fetch_object() ): ?>
  <tr>
     <td><?php echo $row->name ?></td>
     <td><?php echo $row->amount ?></td>
     <td><?php echo $row->city ?></td>
  </tr>
  <?php endwhile; ?>
</table>
</div>
</div>
</div>
</body>
</html>

Final Output :