Php

Advanced search with PHP and MySQL

This tutorial help to create advanced search functionality with PHP and Mysql. We’ll create a PHP form that takes input and search into the MySQL table.

Advanced search provides more options to the end user to filter the search result.

Steps to implement advanced search PHP mysqli

  • Create a MySQL database and populate it with data
  • Create a search form using PHP.
  • Connect with the MySQL database.
  • Add security to the MySQL query by putting mysqli_real_escape_string.

Create A Database and MySQL table

Let’s create a 'test' database in the MySQL server. Create an employee table in the MySQL database.

Related Post
--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employees`
--

INSERT INTO `employees` (`id`, `name`, `age`, `salary`, `created_at`, `updated_at`) VALUES
(1, 'Tiger Nixon', 61, 320800, NULL, NULL),
(2, 'Garrett Winters', 63, 170750, NULL, NULL),
(3, 'Ashton Cox', 66, 86000, NULL, NULL),
(4, 'Cedric Kelly', 22, 433060, NULL, NULL),
(5, 'Airi Satou', 33, 162700, NULL, NULL),
(6, 'Brielle Williamson', 61, 372000, NULL, NULL),
(7, 'Herrod Chandler', 59, 137500, NULL, NULL),
(8, 'Rhona Davidson', 55, 327900, NULL, NULL),
(9, 'Colleen Hurst', 39, 205500, NULL, NULL),
(10, 'Sonya Frost', 23, 103600, NULL, NULL),
(11, 'Jena Gaines', 30, 90560, NULL, NULL),
(12, 'Quinn Flynn', 22, 342000, NULL, NULL),
(13, 'Charde Marshall', 36, 470600, NULL, NULL),
(14, 'Haley Kennedy', 43, 313500, NULL, NULL),
(15, 'Tatyana Fitzpatrick', 19, 385750, NULL, NULL),
(16, 'Michael Silva', 66, 198500, NULL, NULL),
(17, 'Paul Byrd', 64, 725000, NULL, NULL),
(18, 'Gloria Little', 59, 237500, NULL, NULL),
(19, 'Bradley Greer', 41, 132000, NULL, NULL),
(20, 'Dai Rios', 35, 217500, NULL, NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `employees`
--
ALTER TABLE `employees`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=58;
COMMIT;

HTML Form

We’ll create an HTML form to take inputs from the end user and submit form data. I have created the name, age, and salary inputs fields to get input from users.

<form class="advanced-search-form" method="post" action="index.php">
    <div class="form-group">
  <label for="">Employee Name</label>
  <input type="name" name="search[name]" value="<?php echo $name; ?>">
 </div>
 <div class="form-group">
  <label for="">Age</label>
  <input type="age" name="search[age]" value="<?php echo $age; ?>">
 </div>
 <div class="form-group">
  <label for="">Salary</label>
  <input type="salary" name="search[salary]" value="<?php echo $salary; ?>">
 </div>
 <div class="form-group">
  <input type="submit" value="Search">
 </div>
</form>

PHP code to submit data

Let’s create a PHP script that receives code from HTML form inputs and forms MySQL query with the advanced search condition. The code is:

$conn = mysqli_connect("localhost", "root", "", "test");
$queryCondition = " WHERE ";
if(!empty($_POST["search"])) {
foreach($_POST["search"] as $k=>$v){
 if(!empty($v)) {
     $v = mysqli_real_escape_string($v)
  if(!empty($queryCondition)) {
   $queryCondition .= " AND ";
  } else {
   $queryCondition .= " WHERE ";
  }
  
  $queryCondition .= $v ." like '%$v%'";
 }
}
$sql = "SELECT * FROM employees " . $queryCondition;
$result = mysqli_query($conn,$sql);

Recent Posts

Configure and Retrieve S3 Information Using Laravel PHP-AWS-SDK

This tutorial helps integrate a PHP SDK with Laravel. We'll install aws-php-sdk into laravel application and access all aws services… Read More

2 months ago

What is the Purpose of php_eol in PHP?

in this quick PHP tutorial, We'll discuss php_eol with examples. PHP_EOL is a predefined constant in PHP and represents an… Read More

8 months ago

Laravel Table Relationship Methods With Example

This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More

8 months ago

Exploring the Power of Laravel Eloquent Join?

We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More

9 months ago

Quick and Easy Installation of Laravel Valet

in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More

9 months ago

What is Laravel Soft Delete and How Does it Work?

I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More

9 months ago

Categories