Simple Search Feature with PHP and MySQL

Simple Search Feature with PHP and MySQL. Hello, in this post I would like to explain about implementation "search" sql query in your web application. Another thing to note in this activity is a connection to the data base, displaying data, and search data. To access the data base I using PDO method (I just like this method 😊 ).

Data Base

Create data base and table. I have sample like this:

data base: dbtest, table:tbl_members

CREATE TABLE `tbl_members` (
  `user_id` int(5) NOT NULL,
  `first_name` varchar(25) NOT NULL,
  `last_name` varchar(25) NOT NULL,
  `email` varchar(50) NOT NULL,
  `position` varchar(25) NOT NULL,
  `office` varchar(25) NOT NULL
)

or you can use sql file which I attach on download link.

DB Connection (dbconfig.php)
configuration to connect data base.

<?php
$DB_host = "localhost";
$DB_user = "root"; //your db username
$DB_pass = ""; //your db password 
$DB_name = "dbtest"; //your database

try{
$DB_con = new PDO("mysql:host={$DB_host};dbname={$DB_name}",$DB_user,$DB_pass);
$DB_con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e){
echo $e->getMessage();
}
include_once 'class.view.php';
$view = new view($DB_con);
?>

Displaying data
1. class.view.php
to process instructions from query (in index.php)

<?php
class view{
 private $db;
 function __construct($DB_con){
  $this->db = $DB_con;
 }

 public function dataview($query){
   $stmt = $this->db->prepare($query);
   $stmt->execute();
   if($stmt->rowCount()>0){
      while($row=$stmt->fetch(PDO::FETCH_ASSOC)){ ?>
       <tr>
        <td><?php print($row['user_id']); ?></td>
        <td><?php print($row['first_name']); ?></td>
        <td><?php print($row['last_name']); ?></td>
        <td><?php print($row['email']); ?></td>
        <td><?php print($row['position']); ?></td>
        <td><?php print($row['office']); ?></td>
       </tr><?php
      } ?>
      <?php
   }else{
     ?>
      <tr><td colspan="16">No Data Found ...</td></tr>
     <?php
   }
 }
}

2. index.php
Displaying data

<div class="container">
 <div class="row">
  <form action="index.php" method="post">
   <div class="col-md-3 col-md-offset-8">
    <input type="text" name="search" class="form-control" placeholder="Enter First Name/Last Name">
   </div>
   <div class="col-md-1">
    <button class="btn btn-sm btn-info"><i class="glyphicon glyphicon-search"></i> &nbsp; Search</button>
   </div>
  </form>
 </div>
 <br>
 <div class="row">
  <table class="table table-bordered table-hover">          
   <tr>
    <th class="col-md-1"><center> Id</center></th>
    <th class="col-md-2"><center> First Name</center></th>
    <th class="col-md-2"><center> Last Name </center></th>
    <th class="col-md-3"><center> Email</center></th>
    <th class="col-md-2"><center> Position </center></th>
    <th class="col-md-2"><center> Office </center></th>
   </tr>
  <?php
   require_once 'dbconfig.php';
   $query = "SELECT * FROM tbl_members"; //show all data
   if(isset($_POST['search'])){ //botton search clicked
    $keyword=$_POST['search'];
    $query = "SELECT * FROM tbl_members 
        WHERE first_name like '%$keyword%' 
        OR last_name like '%$keyword%'"; //search
   }
   
   $view->dataview($query); //view data
  ?>
  </table> 
  <br>
 </div>
</div>

The following is the part where the query that displays all the data is changed into a query to display the data sought.

<?php
 require_once 'dbconfig.php';
 $query = "SELECT * FROM tbl_members"; //show all data
 if(isset($_POST['search'])){ //botton search clicked
  $keyword=$_POST['search'];
  $query = "SELECT * FROM tbl_members 
      WHERE first_name like '%$keyword%' 
      OR last_name like '%$keyword%'"; //search
 }
 
 $view->dataview($query); //view data
?>