CODEWITHSUNDEEP
phpmysqldatabase
dxenaretionx
dxenaretionx

Reputation: 551

Search Filtering with PHP/MySQL

I'm trying to create a search/filtering option in my blood donor application. Where donor can be searched by sex, name, blood group or by selecting all three. Here is my code

function search_donar($_POST) {

        $by_name = $_POST['by_name'];
        $by_sex = $_POST['by_sex'];
        $by_group = $_POST['by_group'];
        $by_level = $_POST['by_level'];

        $search_query = "SELECT * FROM donar WHERE";
        if($by_name !="") {
          $search_query .= " name='$by_name'";
        }
        if($by_sex !="") {
          $search_query .= " sex='$by_sex'";
        }
        if($by_group !="") {
          $search_query .= " blood_group='$by_group'";
        }
        if($by_level !="") {
          $search_query .= " e_level='$by_level'";
        }
        $search_query;
        $result = mysql_query($search_query);

        return $result;
    }

And here is the html

if(isset($_POST['submit'])) {

    $retrived_result = $donar->search_donar($_POST);

}

   <form action="" method="post">
    <table width="100%" border="0" style="border:none;">
      <tr>
        <td><label>Name:&nbsp;</label><input type="text" name="by_name" /></td>
        <td><label>Sex:&nbsp;</label><input type="text" name="by_sex" /></td>
        <td><label>Blood Group:&nbsp;</label><input type="text" name="by_group" /></td>
        <td><label>Level:&nbsp;</label><input type="text" name="by_level" /></td>
        <td><input class="button" type="submit" name="submit" value="Search" /></td>
      </tr>
    </table>
    </form>

Single filtering works very fine. But To filter with all I used AND , but it gives me error. Can anyone help ?

Thanks in advance

Upvotes: 16

Views: 85638

Answers (7)

kashif iqbal
kashif iqbal

Reputation: 11

function search_donar($_POST) {

    $by_name = $_POST['by_name'];
    $by_sex = $_POST['by_sex'];
    $by_group = $_POST['by_group'];
    $by_level = $_POST['by_level'];

    $search_query = "SELECT * FROM donar WHERE";

    $and = '';
    if(count($_POST) > 1) {
        $and ='AND';
    }
    if($_POST[0]) {
        $and ='';
    }

    if($by_name !="") {
        $search_query .= $and." name='$by_name'";
    }
    if($by_sex !="") {
        $search_query .= $and." sex='$by_sex'";
    }
    if($by_group !="") {
        $search_query .= $and." blood_group='$by_group'";
    }
    if($by_level !="") {
        $search_query .= $and." e_level='$by_level'";
    }

    if(count($_POST) == 0) {
        $search_query .= " 1 ";
    }

    $search_query;
    $result = mysql_query($search_query);

    return $result;
}

Upvotes: 0

Sreenath
Sreenath

Reputation: 510

I'd do in this way.

function search_donar($_POST) {

    $by_name = $_POST['by_name'];
    $by_sex = $_POST['by_sex'];
    $by_group = $_POST['by_group'];
    $by_level = $_POST['by_level'];

    $search_query = "SELECT * FROM donar WHERE 1 = 1";
    if($by_name !="") {
      $search_query .= " AND name='$by_name'";
    }
    if($by_sex !="") {
      $search_query .= " AND sex='$by_sex'";
    }
    if($by_group !="") {
      $search_query .= " AND blood_group='$by_group'";
    }
    if($by_level !="") {
      $search_query .= " AND e_level='$by_level'";
    }
    $result = mysql_query($search_query);
    return $result;
}

Upvotes: 0

Rejinderi
Rejinderi

Reputation: 11844

Like all the other post you will need to append all the conditions with AND like so. This is the cleanest answer so far. Remember to real escape your strings though use the mysqli OOP way instead of the old mysql. Just a suggestion.

Heres an example of a typical query.

The correct way:

SELECT * FROM donar WHERE name='dxenaretionx' AND sex='M';

The way you are doing it

SELECT * FROM donar WHERE name='dxenaretionx' sex='M';

Code:

function search_donar($_POST) {
    $by_name = $_POST['by_name'];
    $by_sex = $_POST['by_sex'];
    $by_group = $_POST['by_group'];
    $by_level = $_POST['by_level'];

    //Do real escaping here

    $query = "SELECT * FROM donar";
    $conditions = array();

    if(! empty($by_name)) {
      $conditions[] = "name='$by_name'";
    }
    if(! empty($by_sex)) {
      $conditions[] = "sex='$by_sex'";
    }
    if(! empty($by_group)) {
      $conditions[] = "blood_group='$by_group'";
    }
    if(! empty($by_level)) {
      $conditions[] = "e_level='$by_level'";
    }

    $sql = $query;
    if (count($conditions) > 0) {
      $sql .= " WHERE " . implode(' AND ', $conditions);
    }

    $result = mysql_query($sql);

    return $result;
}

Upvotes: 38

Arif
Arif

Reputation: 1211

In this where you don't use validation, it is recomended not to use whether Field is EMPTY or NOT. Try below code, Hope it will work 

function search_donar($_POST) {

    $by_name = $_POST['by_name'];
    $by_sex = $_POST['by_sex'];
    $by_group = $_POST['by_group'];
    $by_level = $_POST['by_level'];

    $search_query = "SELECT * FROM donar WHERE name LIKE '%$by_name%' AND sex LIKE '%$by_sex%' AND blood_group LIKE '%$by_group%' AND  e_level LIKE '%$by_level%' ";

    $result = mysql_query($search_query);

    return $result;
}

Upvotes: 0

Er. Anurag Jain
Er. Anurag Jain

Reputation: 1793

There in Your code there is problem in query where condition . Here your query will be like select * from donar where by_name = "A" by_group = "N" there is No And/Or to make where condition properly. Please try code like given below.

$search_query = "SELECT * FROM donar";
$query_cond = "";

if($by_name !="") {
      $query_cond .= " name='$by_name'";
}
if($by_sex !="") {

      if(!empty($query_cond)){
          $query_cond .= " AND ";
       }

      $query_cond .= " sex='$by_sex'";
}

if($by_group !="") {

      if(!empty($query_cond)){
          $query_cond .= " AND ";
       }

      $query_cond .= " blood_group='$by_group'";
}

if($by_level !="") {

      if(!empty($query_cond)){
          $query_cond .= " OR ";
       }

      $query_cond .= " e_level='$by_level'";
 }

 if(!empty($query_cond)){
      $query_cond = " Where ".$query_cond;
      $search_query.$query_cond;
 }

Here in code First we take $query_cond variable empty and make condition according code. and manage AND operator according that. And in last if We found $query_cond not empty then add it to $select_query.

I hope it will be helpful for you.

thanks

Upvotes: 2

Prasath Albert
Prasath Albert

Reputation: 1457

Try like this:

function search_donar($_POST) {

        $by_name = $_POST['by_name'];
        $by_sex = $_POST['by_sex'];
        $by_group = $_POST['by_group'];
        $by_level = $_POST['by_level'];

        $isfirst=0;
        $search_query = "SELECT * FROM donar WHERE";
        if($by_name !="") {

          $search_query .= " name='$by_name'";
          $isfirst=1;
        }
        if($by_sex !="") {
            if($isfirst!=0)
            $search_query .= " AND ";
          $search_query .= " sex='$by_sex'";
          $isfirst=1;
        }
        if($by_group !="") {
            if($isfirst!=0)
            $search_query .= " AND ";
          $search_query .= " blood_group='$by_group'";
          $isfirst=1;
        }
        if($by_level !="") {
            if($isfirst!=0)
            $search_query .= " AND ";
          $search_query .= " e_level='$by_level'";
          $isfirst=1;
        }
        $result = mysql_query($search_query);

        return $result;
    }

Upvotes: 0

John Dvorak
John Dvorak

Reputation: 27277

The following code snippet:

$search_query = "SELECT * FROM donar WHERE";
if($by_name !="") {
  $search_query .= " name='$by_name'";
}
if($by_sex !="") {
  $search_query .= " sex='$by_sex'";
}

produces queries like

SELECT * FROM donar WHERE name='nowak' sex='m'

, which are not valid because there is no logical operator between the clauses. You need to add an 'AND'. To simplify code, you can generate conditions in the form of "true and a and b ...":

$search_query = "SELECT * FROM donar WHERE true";
if($by_name !="") {
  $search_query .= " AND name='$by_name'";
}
if($by_sex !="") {
  $search_query .= " AND sex='$by_sex'";
}
...

Upvotes: 2

Related Questions