PHP showing table from database to website using advance search

Question

The program I have made contains only one search bar, so I can't filter more accurate or related tables.

I need one more search bar in order to enter the value in two search field by clicking post it will search from database and get most related once.

<?php

    if(isset($_POST['search']))
   {
     $valueToSearch = $_POST['valueToSearch'];
  // search in all table columns
 // using concat mysql function
 $query = "SELECT * FROM `included` WHERE CONCAT(`id`, `a`, `b`, 
 `c`,`c`,`d`,`e`,`f`,`g`,`h`,`i`) LIKE '%".$valueToSearch."%'";
   $search_result = filterTable($query);

  }
  else {
     $query = "SELECT * FROM `included`";
 $search_result = filterTable($query);
}

// function to connect and execute the query
 function filterTable($query)
 {
  $connect = mysqli_connect("localhost", "root", "", "hospitaldata");
  $filter_Result = mysqli_query($connect, $query);
 return $filter_Result;
 }

 ?>

      <!DOCTYPE html>
 <html>
 <head>
 <img src="nop.jpg">
 <title>PHP HTML TABLE DATA SEARCH</title>

     <style>
  table,tr,th,td{
   border:.3px solid blue;
 color:#000;
   font-family:sans-serif;
  }

div.relative {
 position: relative;
 top: -50px;
 width: 1400px;
 height: 100px;
     color: #0C3;
 font-family: "Arial Black", Gadget, sans-serif;
font-size: 24px;
} 

div.absolute {
 position: absolute;
top: 51px;
 right: 20;
 width: 1261px;
 height: 40px;
 color: #999;
 font-family: Verdana, Geneva, sans-serif;
 left: 65px;
  }
  input[type=text] {
 alignment-baseline:central;
 width: 130px;
 box-sizing: border-box;
  border: 2px solid #ccc;
  border-radius: 4px;
  font-size: 16px; 
  background-color: white;
  background-image:url('ds.jpg'); 


  padding: 12px 20px 12px 40px;
  -webkit-transition: width 0.4s ease-in-out;
  transition: width 0.4s ease-in-out;
 }

 input[type=text]:focus {
width: 50%;
 }
 table,tr,th,tr
 {
 border:1px solid blue;

 }

  </style>
  </style>
  </head>
  <body>
  <div class="relative"><h1 align="center">HOSPITAL</h1>
  <div class="absolute" align="center">Check provided points here</div>
  </div>

  <form action="index.php" method="post">
    <input type="text" name="valueToSearch" placeholder="Search..."><br> 

    <input type="submit" name="search" value=">>"><br><br>

    <table>
        <tr>

  <th>Building</th>
 <th>Floor</th>
  <th>zone</th>
  <th>Room no</th>
  <th>Room Type</th>
 <th>Room Name</th>
    <th>Types of Connection</th>
          <th>Suggested</th>
    <th>Provided</th>
        </tr>

 <!-- populate table from mysql database -->
         <?php while($row = mysqli_fetch_array($search_result)):?>
        <tr>

  <td><?php echo $row['a'];?></td>
   <td><?php echo $row['b'];?></td>
   <td><?php echo $row['c'];?></td>
   <td><?php echo $row['d'];?></td>
   <td><?php echo $row['e'];?></td>
   <td><?php echo $row['f'];?></td>
   <td><?php echo $row['g'];?></td>
   <td><?php echo $row['h'];?></td>
    <td><?php echo $row['i'];?></td>
        </tr>
        <?php endwhile;?>
    </table>
 </form>

 </body>
 </html>

You should NEVER pass a user input streigt into your sql query. Becouse if someone searches for `; DROP TABLE included;`, your table will be deleted. You should create a statement and then bind a parameter, as explained in http://stackoverflow.com/a/60496/1152471 — PKeidel, May 12 '17 at 07:31
Sorry, but I really don't understand what exactly you want to achive. Please update your question. Try to reduce your code to a minimum (css for example isn't needed I think) and give a more detailed explanation of your problem, what you have till now and what your goal is. — PKeidel, May 14 '17 at 14:12
i have already created a search field...now i want to put one more search field...these two search field have only one search button... — akhilrl, May 19 '17 at 01:25
how do these 2 fields differ? why is one field not enought? You could check which value is available via `if(isset($_POST['field1'])) { ...make something with that value...}` — PKeidel, May 19 '17 at 15:17
i have created a table it contains 4000 entries...when i search in single field it shows more than 30 entries so it become more difficult to find which was i looking for...i need 2 or 3 entries will show.....so i need one more search field...by typing two variables in two search field it will show only what i want..... — akhilrl, May 22 '17 at 04:49
Should there also be two search buttons? Or should one button send both values? — PKeidel, May 22 '17 at 14:06

score 0 · Answer 1 · answered May 24 '17 at 08:39

From your comments I understand the following:

you have a webpage with a search input field
you want a second input so your users can search more presice
one serach button should send both input fields

A really plain (and untested) version could look like this:

<form action="search.php" method="post">
  <label><input name="search1"/> First keyword</label>
  <label><input name="search2"/> Second keyword</label>
  <input type="submit" value="Serach"/>
</form>

<?php
// If the Search button was pressed
if(isset($_POST['search1'])) {
  $stmt = $pdo->prepare($query = "SELECT * FROM `included` WHERE CONCAT(`id`, `a`, `b`, `c`) LIKE '%:search1%' OR CONCAT(`d`, `e`) LIKE '%:search2%'");
  $stmt->execute(['search1' => $POST['search1'], 'search2' => $POST['search2'] :? 'default');
  foreach ($stmt as $row) {
    // do something with $row
  }
}

Of course you have to fit the SQL statement to your needs.

For infos about how to setup the $pdo database connection, please refer to the link: http://stackoverflow.com/a/60496/1152471

PHP showing table from database to website using advance search

1 Answers1