PHP multiple search query returns all rows

Question

The problem is that i'm trying to get specific search result but it, gives all database rows, not the filtered result. how can i avoid this?

require_once 'db/connect.php';

if(isset($_GET['submit'])){
    $doctor = $db->escape_string($_GET['doctor']);
    $specialization = $db->escape_string($_GET['specialization']);
    $hospital = $db->escape_string($_GET['hospital']);

    $query = $db->query("
         SELECT docname, specialization,hospital
         From doctor
         WHere docname Like '%{$doctor}%' or
         specialization like '%{$specialization}%' or
         hospital like '%{$hospital}%'
        ");
?>




<div class="result-count">
    Found <?php echo $query->num_rows; ?> results.
</div>

<?php

if($query -> num_rows){
    while($r = $query->fetch_object()){
        ?>
          <div class="result-count">
             <a href="#"><?php  echo $r->docname; ?></a>
          </div>
        <?php
    }
}
}

sidenote: given what you posted and if it's your real code, you should be getting a parse error with the way you braced the conditional statement. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 4, 2016 at 12:57
@Fred-ii- i didn't got any errors, but it gives all the database results — Mel Rossi
– Mel Rossi, Commented Nov 4, 2016 at 12:59
You can avoid it by writing an SQL query that is giving you the desired result. — Your Common Sense
– Your Common Sense, Commented Nov 4, 2016 at 13:02

Your Common Sense · Accepted Answer · 2016-11-04 13:08:52Z

1

You have to remove unused fields from WHERE clause. Otherwise the hospital like '%%' condition will effectively match every row in the table.

So in your case you should create the query on the fly, adding conditions for non-empty fields only.

And of course it just enormously helps to print the resulting query out. It will not only give you an idea, what SQL you are running, but also make your question sensible.

answered Nov 4, 2016 at 13:08

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mel Rossi Over a year ago

how can i do that ?

Pancho Villa · Accepted Answer · 2016-11-04 15:50:14Z

Try this:

require_once 'db/connect.php';

if(isset($_GET['submit'])){
   $doctor = $db->escape_string($_GET['doctor']);
   $specialization = $db->escape_string($_GET['specialization']);
   $hospital = $db->escape_string($_GET['hospital']);
   $where = array();
   if (!empty($doctor)) {
        array_push($where, "docname like '%{$doctor}%'");
   }
   if (!empty($specialization)) {
        array_push($where, "specialization like '%{$specialization}%'");
   }
   if (!empty($hospital)) {
        array_push($where, "hospital like '%{$hospital}%'");
   }
$sql = "SELECT docname, specialization, hospital "
       . "FROM doctor";
if (!empty($where)) {
    $sql .= "WHERE " . implode(' OR ', $where);
}
$query = $db->query($sql);

?>

Collectives™ on Stack Overflow

PHP multiple search query returns all rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related