dxenaretionx dxenaretionx - 2 months ago 10
MySQL Question

Search Filtering with PHP/MySQL

I,m trying to create a search/ filtering option in my blood donar application. Where donar can be searched by sex,name,blood group or with selecting all Here are 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

Answer

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($by_name !="") {
      $conditions[] = "name='$by_name'";
    }
    if($by_sex !="") {
      $conditions[] = "sex='$by_sex'";
    }
    if($by_group !="") {
      $conditions[] = "blood_group='$by_group'";
    }
    if($by_level !="") {
      $conditions[] = "e_level='$by_level'";
    }

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

    $result = mysql_query($sql);

    return $result;
}