Advanced Search Using PHP and MySQL

5 min readNov 21, 2022

For advance search, there are simple processes to be carried out. In this method, the form for the advanced search and the result is shown on the same page.

The process consists of dynamic queries in MySQL. The form is written in simple HTML and given a style. The result table is written in PHP and a style is given.

To start the work let's create a file called “dynamic_query.php”. Now let's create a simple form on the page.

<h2>Dynamic Query Example</h2>
<div>
<form enctype="multipart/form-data"  action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']) ?>" method='POST'>
  <label for="id">ID</label><br>
  <input type="text" id="id" name="id" ><br>

  <label for="name">First name:</label><br>
  <input type="text" id="name" name="name" ><br>

  <label for="age">Age</label><br>
  <input type="text" id="age" name="age" ><br><br>

   <label for="gender">Gender</label><br>
   <select name="gender">
        <option name="0">Choose Gender</option>
        <option name="M">M</option>
        <option name="F">F</option>
    </select>
  <input type="submit" value="Submit" name="submit_button">
</form>
</div>

In the above code, the below line is used to display the result on the same page.

action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']) ?

Now let's give some styles to the form.

<style>
input[type=text], select {
  width: 100%;
  padding: 12px 20px;
  margin: 8px 0;
  display: inline-block;
  border: 1px solid #ccc;
  border-radius: 4px;
  box-sizing: border-box;
}

input[type=submit] {
  width: 100%;
  background-color: #4CAF50;
  color: white;
  padding: 14px 20px;
  margin: 8px 0;
  border: none;
  border-radius: 4px;
  cursor: pointer;
}

input[type=submit]:hover {
  background-color: #45a049;
}

div {
  border-radius: 5px;
  width: 50%;
  margin-left:100px;
  background-color: #f2f2f2;
  padding: 20px;
}
#person_table {
  font-family: Arial, Helvetica, sans-serif;
  border-collapse: collapse;
  width: 70%;
  margin-left:100px;
}

#person_table td, #customers th {
  border: 1px solid #ddd;
  padding: 8px;
}
#person_table tr:nth-child(even){background-color: #f2f2f2;}

#person_table th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #04AA6D;
  color: white;
}
</style>

With the style and form code, the output of the form can be seen below.

Now let’s create PHP code for the click of submit button

<?php

 if(array_key_exists('submit_button', $_POST)) {


           $gender = filter_input(INPUT_POST, 'gender', FILTER_SANITIZE_STRING);
           $id = $_POST['id'];
           $name = $_POST['name'];
           $age = $_POST['age'];
           
 }

In the above code $gender = filter_input(INPUT_POST, ‘gender’, FILTER_SANITIZE_STRING);

The variable $gender takes the selected option value from the gender input type.

And $id = $_POST[‘id’];

$id is the variable that takes value from the id input type, the same for $name and $age.

Now let's create a function where the values of the form(id, name, age, gender)can be sent and a table is created.

function showResult($gender,$id,$name,$age)
 {

     // to write all the process for database connection, and table 
 }
?>

Now let's call this function where the submit button is called

showResult($gender,$id,$name,$age);

Now let's create a database connection in the “showResult” function

function showResult($gender,$id,$name,$age)
 {

          $dbhost = 'localhost';
          $dbuser = 'root';
          $dbpass = '';
          $dbname = 'test_table';

          $conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);

          if(! $conn ) {
             die('Could not connect: ' . mysqli_error($conn));
          }
}

After the database connection is done now we can check for each input type for the values and send the SQL query one by one. During this time the dynamic query is formed.

$condition = "";
$sql = "select * from person where 1";

Here, the variable condition is used to create queries for conditions for each input type, and variable SQL is the query that will be updated in each change in input type.

Now let's create conditions for all the input types

if($gender!=0){
    $condition = " and gender='$gender'";
 }
if($id){
    $condition .=" and id=$id";
}if($name){
    $condition .=" and  name='$name'";
}
if($age){
    $condition .=" and  age=$age";
 }

Here every time the input type is changed the function will be executed and the condition will be checked. If the condition is true then a matching SQL query will be added in the $condion variable. Like if the search is based on id then

if($id){
    $condition .=" and id=$id";
}

this condition will be checked and the variable will be updated. If the search is based on more than one input then the $condition variable will be updated and similarly $sql variable will be updated which is shown below.

 $sql1  = $sql.$condition;

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

Now we have updated the SQL query in the $sql1 variable. The §result variable is responsible to execute the query.

Now if the result has more than 1 row then a table must be created. So lets check for the row numbers in a condition where table will be created.

  if(mysqli_num_rows($result) >0)
 {
}

Now let's create a table if the condition is true.

if(mysqli_num_rows($result) >0)
          {
           $table = '

            <table id="person_table">
            <thead>
                    <tr>
                             <th>ID</th>
                             <th>Name</th>
                             <th>Age</th>
                             <th>Gender</th>
                    </tr>
            </thead>
             <tbody>
           ';
           while($row = mysqli_fetch_array($result))
           {
            $table .= '
             <tr>
                  <td>'.$row["id"].'</td>
                   <td>'.$row["name"].'</td>
                   <td>'.$row["age"].'</td>
                   <td>'.$row["gender"].'</td>
              </tr>
            ';
           }
           $table .= '</tbody></table>';
           echo $table;
          }
 }

The table has id as “person_table”. So we can style this table as following


#person_table {
  font-family: Arial, Helvetica, sans-serif;
  border-collapse: collapse;
  width: 70%;
  margin-left:100px;
}

#person_table td, #customers th {
  border: 1px solid #ddd;
  padding: 8px;
}
#person_table tr:nth-child(even){background-color: #f2f2f2;}

#person_table th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #04AA6D;
  color: white;
}

The output of the table can be seen as following

In this way, we can have advance query can be done in PHP with MySQL.

The detailed code for the above example can be found on GitHub in the following link:

advance_search_php_mysql

Or below

<!DOCTYPE html>
<html>
<body>

<h2>Dynamic Query Example</h2>
<style>
input[type=text], select {
  width: 100%;
  padding: 12px 20px;
  margin: 8px 0;
  display: inline-block;
  border: 1px solid #ccc;
  border-radius: 4px;
  box-sizing: border-box;
}

input[type=submit] {
  width: 100%;
  background-color: #4CAF50;
  color: white;
  padding: 14px 20px;
  margin: 8px 0;
  border: none;
  border-radius: 4px;
  cursor: pointer;
}

input[type=submit]:hover {
  background-color: #45a049;
}

div {
  border-radius: 5px;
  width: 50%;
  margin-left:100px;
  background-color: #f2f2f2;
  padding: 20px;
}
#person_table {
  font-family: Arial, Helvetica, sans-serif;
  border-collapse: collapse;
  width: 70%;
  margin-left:100px;
}

#person_table td, #customers th {
  border: 1px solid #ddd;
  padding: 8px;
}
#person_table tr:nth-child(even){background-color: #f2f2f2;}

#person_table th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #04AA6D;
  color: white;
}
</style>
<div>
<form enctype="multipart/form-data"  action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']) ?>" method='POST'>
  <label for="id">ID</label><br>
  <input type="text" id="id" name="id" ><br>

  <label for="name">First name:</label><br>
  <input type="text" id="name" name="name" ><br>

  <label for="age">Age</label><br>
  <input type="text" id="age" name="age" ><br><br>

   <label for="gender">Gender</label><br>
   <select name="gender">
        <option name="0">Choose Gender</option>
        <option name="M">M</option>
        <option name="F">F</option>
    </select>
  <input type="submit" value="Submit" name="submit_button">
</form>
</div>
<hr>

<?php

 if(array_key_exists('submit_button', $_POST)) {
 echo "check 1";

           $gender = filter_input(INPUT_POST, 'gender', FILTER_SANITIZE_STRING);
           $id = $_POST['id'];
           $name = $_POST['name'];
           $age = $_POST['age'];

         showResult($gender,$id,$name,$age);
 }
 function showResult($gender,$id,$name,$age)
 {

          $dbhost = 'localhost';
          $dbuser = 'root';
          $dbpass = '';
          $dbname = 'test_table';

          $conn = mysqli_connect($dbhost, $dbuser, $dbpass,$dbname);

          if(! $conn ) {
             die('Could not connect: ' . mysqli_error($conn));
          }

         $condition = "";
         $sql = "select * from person where 1";

         if($gender!=0){

                $condition = " and gender='$gender'";
         }


         if($id){

            $condition .=" and id=$id";
         }if($name){
          $condition .=" and  name='$name'";
         }
         if($age){
        $condition .=" and  age=$age";
       }

         else{}
         $sql1  = $sql.$condition;

         $result = $conn->query($sql1);
         if(mysqli_num_rows($result) >0)
          {
           $table = '

            <table id="person_table">
            <thead>
                    <tr>
                             <th>ID</th>
                             <th>Name</th>
                             <th>Age</th>
                             <th>Gender</th>
                    </tr>
            </thead>
             <tbody>
           ';
           while($row = mysqli_fetch_array($result))
           {
            $table .= '
             <tr>
                  <td>'.$row["id"].'</td>
                   <td>'.$row["name"].'</td>
                   <td>'.$row["age"].'</td>
                   <td>'.$row["gender"].'</td>
              </tr>
            ';
           }
           $table .= '</tbody></table>';
           echo $table;
          }
 }
?>

</body>
</html>

Advanced Search Using PHP and MySQL

Written by Jagriti Srivastava