PHP MySQL search database and display results


PHP MySQL search database and display results

Hi friends, in this tutorial you will learn how to perform a PHP MySQL search database and display results in a step-by-step process from scratch. We must know that database searching is very useful in any kind of application or in any dynamic website. Therefore, we can do this with the help of a PHP script and get the desired results based on the search input from HTML forms.

Steps to perform PHP MySQL search database and display results

Step 1:- Create a database in PHP Myadmin as shown in the below screenshot.

PHP MySQL search database and display results

Step 2:- Create a table in the database as shown in the below screenshot.

PHP MySQL search database and display results

Step 3:- Insert data in the table.

In this step, we will insert some data with the help of an SQL query so that we can search from these data and show the results later as shown below

PHP MySQL search database and display results
Query example for inserting data:- INSERT INTO employee_info (id, name, phone_no, age, department, created_at, updated_at) VALUES (NULL, 'ABC', '1234567891', '25', 'IT', current_timestamp(), '0000-00-00 00:00:00.000000');

Now, you can see the inserted data as shown below

PHP MySQL search database and display results

Step 4:- Create a PHP file and Make an HTML form

In this step, we will create an HTML form with a search element so that we can enter the search input as shown below

PHP MySQL search database and display results

Step 5:- Create a PHP file and establish the database connection as shown below

coonnect_test_db.php:-

<?php
$servername='localhost';
$username="root";
$password="";

try
{
	$con=new PDO("mysql:host=$servername;dbname=test_db",$username,$password);
	$con->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
	//echo 'connected';
}
catch(PDOException $e)
{
	echo '<br>'.$e->getMessage();
}
	
?>

Step 6:- Write a PHP script based on the search input.

<?php
include 'connect_test_db.php';
$searchErr = '';
$employee_details='';
if(isset($_POST['save']))
{
	if(!empty($_POST['search']))
	{
		$search = $_POST['search'];
		$stmt = $con->prepare("select * from employee_info where department like '%$search%' or name like '%$search%'");
		$stmt->execute();
		$employee_details = $stmt->fetchAll(PDO::FETCH_ASSOC);
		
	}
	else
	{
		$searchErr = "Please enter the information";
	}
   
}

?>

Below are the two examples of searching data from the database

  • If the search input is matched with the column value and the column has the single value then it will return the single row as shown in the below screenshot.
PHP MySQL search database and display results
  • If the search input is matched with the column value and the column has more than one value then it will return multiple rows as shown in the below screenshot.
PHP MySQL search database and display results

Complete Code:- phpsearch.php

<?php
include 'connect_test_db.php';
$searchErr = '';
$employee_details='';
if(isset($_POST['save']))
{
	if(!empty($_POST['search']))
	{
		$search = $_POST['search'];
		$stmt = $con->prepare("select * from employee_info where department like '%$search%' or name like '%$search%'");
		$stmt->execute();
		$employee_details = $stmt->fetchAll(PDO::FETCH_ASSOC);
		//print_r($employee_details);
		
	}
	else
	{
		$searchErr = "Please enter the information";
	}
   
}

?>
<html>
<head>
<title>ajax example</title>
<link rel="stylesheet" href="bootstrap.css" crossorigin="anonymous">
<!-- Optional theme -->
<link rel="stylesheet" href="bootstrap-theme.css" crossorigin="anonymous">
<style>
.container{
	width:70%;
	height:30%;
	padding:20px;
}
</style>
</head>

<body>
	<div class="container">
	<h3><u>PHP MySQL search database and display results</u></h3>
	<br/><br/>
	<form class="form-horizontal" action="#" method="post">
	<div class="row">
		<div class="form-group">
		    <label class="control-label col-sm-4" for="email"><b>Search Employee Information:</b>:</label>
		    <div class="col-sm-4">
		      <input type="text" class="form-control" name="search" placeholder="search here">
		    </div>
		    <div class="col-sm-2">
		      <button type="submit" name="save" class="btn btn-success btn-sm">Submit</button>
		    </div>
		</div>
		<div class="form-group">
			<span class="error" style="color:red;">* <?php echo $searchErr;?></span>
		</div>
		
	</div>
    </form>
	<br/><br/>
	<h3><u>Search Result</u></h3><br/>
	<div class="table-responsive">          
	  <table class="table">
	    <thead>
	      <tr>
	        <th>#</th>
	        <th>Employee Name</th>
	        <th>Phone No</th>
	        <th>Age</th>
	        <th>Department</th>
	      </tr>
	    </thead>
	    <tbody>
	    		<?php
		    	 if(!$employee_details)
		    	 {
		    		echo '<tr>No data found</tr>';
		    	 }
		    	 else{
		    	 	foreach($employee_details as $key=>$value)
		    	 	{
		    	 		?>
		    	 	<tr>
		    	 		<td><?php echo $key+1;?></td>
		    	 		<td><?php echo $value['name'];?></td>
		    	 		<td><?php echo $value['phone_no'];?></td>
		    	 		<td><?php echo $value['age'];?></td>
		    	 		<td><?php echo $value['department'];?></td>
		    	 	</tr>
		    	 		
		    	 		<?php
		    	 	}
		    	 	
		    	 }
		    	?>
	    	
	     </tbody>
	  </table>
	</div>
</div>
<script src="jquery-3.2.1.min.js"></script>
<script src="bootstrap.min.js"></script>
</body>
</html>

NOTE*

Download the bootstrap CSS and js files from google and include the path of the files in the href attribute of link tag and src attribute of the script tag respectively.

Also read, PHP MySQL ajax search autocomplete

CONCLUSION:- I hope this article will help you to understand the concept. If you have any doubt then please leave your comment below.


Leave a Reply

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

Theme of 96 THEME.