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

Answers


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;
}

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'";
}
...

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


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;
    }

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;
}

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;
}

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;
}

Need Your Help

Very slow BrowserSync server startup with Gulp

javascript node.js gulp browser-sync

I'm using BrowserSync in server mode (using its built-in static server) with GulpJS on a local project, and everything seems to be working correctly except that the BrowserSync server is very slow to

Can a Jenkins build be failed for coverage going down?

maven build jenkins

I've seen a number of posts on making a Maven-backed Jenkins build fail for a given project if a coverage threshold isn't met i.e. coverage must be at least 80% or the build fails.