CODEWITHSUNDEEP
phpmysqlfilter
Bhumik kewlani
Bhumik kewlani

Reputation: 35

How to filter Multiple Columns in mysql , correctly

enter image description here

This is the table ^^

I can filter data by entering all values in all search boxes.

The Query I am using is : 

SELECT * FROM `student` WHERE `cpi` >= '$valueToSearch' AND `sem` = '$valueToSearch2' AND `choice` = '$valueToSearch3';

But when I dont enter values in all search boxes, (search with single filter) table returns nothing.

I used the OR keyword but its also not helping: 

SELECT * FROM `student` WHERE `cpi` >= '$valueToSearch' OR `sem` = '$valueToSearch2' OR `choice` = '$valueToSearch3';

Upvotes: 0

Views: 3365

Answers (5)

Bhumik kewlani
Bhumik kewlani

Reputation: 35

Thanks @Egg for your query and all others for the Quick response..

Here is what I tried. It might be confusing and longer way but that's what worked for me. 

$condition='';

if (!empty($valueToSearch)) {
    $condition = " `cpi` >= '$valueToSearch'";
}

if (!empty($valueToSearch2)) {
    $condition = " `sem` >= '$valueToSearch2'";
}

if (!empty($valueToSearch3)) {
    $condition = "  `choice` >= '$valueToSearch3'";
}

if (!empty($valueToSearch) && !empty($valueToSearch2)) {
    $condition = " `cpi` >= '$valueToSearch' AND `sem` >= '$valueToSearch2'";
}

if (!empty($valueToSearch2) && !empty($valueToSearch3)) {
    $condition = " `sem` >= '$valueToSearch2' AND `choice` >= '$valueToSearch3'";
}

if (!empty($valueToSearch) && !empty($valueToSearch3)) {
    $condition = " `cpi` >= '$valueToSearch' && `choice` >= '$valueToSearch3'";
}


$query = "SELECT * FROM student WHERE ". $condition ;

Upvotes: 0

JYoThI
JYoThI

Reputation: 12085

Try this one:

$condition='';

    if (!empty($valueToSearch))
  {

      $condition. = "and cpi >= '$valueToSearch'";
   }

  if (!empty($valueToSearch2))
  {

      $condition. = " and  cpi >= '$valueToSearch2'";

  }

 if (!empty($valueToSearch3))
  {

      $condition. = " and cpi >= '$valueToSearch3'";

  }

    $ss = "1=1"; //Or what ever you want to put


$sql = "SELECT * FROM `student` WHERE ".$ss."".$condition ;

Upvotes: 0

Tulio Troncoso
Tulio Troncoso

Reputation: 306

You want to build your query in a way that puts in only the clauses for which you have a filter. If you don't want to filter by Semester, then it shouldn't be provided in the query.

If you're using Laravel or a similar framework, it should have a Query Builder which would be incredibly useful for things like this . If not, you can try your version of the following: 

// Init empty array for clauses
$clauses = [];

// Set cpi clause, if any
if ( isset($valueToSearch) ) {
    $clauses[] = "cpi >= $valueToSearch";
}

// Set semester clause, if any
if ( isset($valueToSearch2) ) {
    $clauses[] = "sem = $valueToSearch2";
}

// Set choice clause, if any
if ( isset($valueToSearch3) ) {
    $clauses[] = "choice = $valueToSearch3";
}

// Guard against empty $clauses array
if ( ! empty($clauses) ) {
    $query = "SELECT * FROM `student` WHERE " . implode( ' AND ', $clauses ) . ";";
}
else {
    // Handle empty $clauses array
}

Upvotes: 5

Rahul Vyas
Rahul Vyas

Reputation: 221

<?php 

$condition = '1=1';

$valueToSearch = 'x';
$valueToSearch2 = 'x';
$valueToSearch3 = 'x';

if (isset($valueToSearch)){
    $condition = $condition . " OR cpi >= '$valueToSearch' ";
} 

if (isset($valueToSearch2)){
    $condition =  $condition . " OR sem = '$valueToSearch2' ";
} 

if (isset($valueToSearch3)){
    $condition =  $condition . " OR choice = '$valueToSearch3' ";
} 


echo $sql = "SELECT * FROM `student` WHERE ".$condition ;


?>

Upvotes: 0

Egg
Egg

Reputation: 1769

Just check each variable has a value before you add it to the SQL query; otherwise you're searching WHERE field=''.

For example:

$sql = "SELECT * FROM student WHERE 1=1";
if (!empty($valueToSearch)) $sql .= " AND cpi >= '$valueToSearch'";
if (!empty($valueToSearch2)) $sql .= " AND sem = '$valueToSearch2'";
if (!empty($valueToSearch3)) $sql .= " AND choice = '$valueToSearch2'";

Starting with WHERE 1=1 allows you to always append with AND field=var.

If no values are submitted then you only search WHERE 1=1; as 1 always equals 1 you'll get all rows.

Upvotes: 0

Related Questions