Selecting all (*) within WHERE

programming
1

I have a .php file with the following query:

$query = "SELECT '$q' FROM Votes WHERE (AGE='$age' AND RACE='$race' AND GENDER='$gender')";

I’m feeding these variables in from Flash. This option doesn’t allow the user to select all from the Age, Race or Gender categories.

I tried:



$age = $_POST["age"];

// 0 = all ages;

if($age == "0"){
$age = "*";
}

But that doesn’t work when I feed it into the query.

Is there a way to have an option to select all within the WHERE method?

2

You can use OR, like select … from … where xyz=‘something’ OR abc=‘something else’;

Another way is to make a custom query depending on the input.
Like,


$query = 'SELECT * FROM TBL WHERE ';
if ( $age )
{
$query .= "AGE='$age'";
}
//and same with other 2, just make sure you don't end up having a query like: "SELECT * FROM TBL WHERE"
//because WHERE will expect something, so you'll get errors.

I hope I understood your problem correctly. :slight_smile:

3

That’s great.

But what about the AND.

I’m worried that if I do:

if($age != “0”){
$query .= “AGE=’$age’ AND”;
}

if($gender != “0”){
$query .= “GENDER=’$gender’”;
}

If gender is 0 then I’m going to have a hanging AND. Will that break it?

4

Yes, it’ll break it, so you have to check that also using if. :slight_smile:

5

Thanks! You really helped me get on my feet here. I appreciate it.

6

np :pleased:

7

I’d be tempted to implode an array of conditions.

$query = "... WHERE (";
$conditions = [];

if ($age != '0') {
    $conditions[] = "AGE = '$age'";
}
if ($gender) {
    ...
}
...

$query .= implode(' AND ', $conditions).');';
echo $query;
8 

//assumes you've checked your input already
extract($_POST); //takes the $_POST array and creates separate vars using key names

$sql = 'SELECT * FROM table WHERE (';

if($age != '') {$ageCond = " age='$age'"; } else { $ageCond = '';}
if($race != '') {$raceCond = " race='$race'"; } else { $raceCond = '';}
if($gender != '') {$genderCond = " gender='$gender'";} else { $genderCond = '';}

if($ageCond != '') { $sql .= $ageCond; }
if(($raceCond != '' || $genderCond != '') && $ageCond != '') {$sql .= ' AND'; }
if($raceCond != '') {$sql .= $raceCond;}
if($raceCond != '' && $genderCond != '') { $sql .= ' AND'; }
if($genderCond != '') {$sql .= $genderCond;}

$sql .= ')';

That might work. There is undoubtedly an easier way, but this this is the first thing that comes to mind. Please let me know how it works out.

9

^ My way?

$query = "SELECT * FROM `Table` WHERE ("; 
$conditions = [];

if ($age != '') $conditions[] = "`Age` = '$age'";
if ($ace != '') $conditions[] = "`Race` = '$race'";
if ($gender != '') $conditions[] = "`Gender` = '$gender'";

$query .= implode(' AND ', $conditions).');';
10

He already got it to work, but using array is a good way also. :slight_smile:

11

For the love of god, wrap your variables inside mysql_real_escape_string before you stick them in your queries!

12

Another easy way is just to use a little case statement trick and just pass through both the variables each time - for instance, assuming that ‘zero’ means the var should be ignored

SELECT * FROM
Table
WHERE
SomeField = CASE @SomeVar WHEN 0 THEN SomeField ELSE @SomeVar END
AND
SomeOtherField = CASE @SomeOtherVar WHEN 0 THEN SomeOtherField ELSE @SomeOtherVar END

This simply checks the value of the variable - when it’s 0 then it compares each data field against itself instead of the variable (effectively ignoring the filter as a field always equals itself)