IF statement in a MySQL Select Command

[JordashTalon]

Hey,

For one of my projects I need to change what Categories and Sub Categories are selected in a Table depending on whether or not a Sub Category or difficulty is selected basically I need to do something like this:

$result = mysql_query(SELECT * FROM table WHERE category='$thecategory');

ok so that works so far but I need to be able to detect whether or not a Sub Category was selected which I do by passing a variable like Sub Category through the GET function.

so

$subCategory = $_GET['subcategory']

then I would like to update my SQL command like this but don't know how to properly:

$result = mysql_query(SELECT * FROM table WHERE category='$thecategory' if($subCategory){AND subCategory='$subCategory'});

I hope that makes sense, basically I need to detect if they've chosen a Sub Category and if they have only select things that are in the Main Category and the Sub Category. Thanks for any help.

Jordan Garn

[Mittineague]

You could do something like

$query = "SELECT * FROM table WHERE category='" . $thecategory . "'";
$subCategory = $_GET['subcategory'];//sanitize before using
if($subCategory){
$query .= " AND subCategory='" . $subCategory "'";
}
$result = mysql_query($query); 

[JordashTalon]

Ok, good idea, i'll try and implement that, basically use PHP to generate the string I see

[Jake Arkinstall]

I'd recommend verification before assuming the variable exists.

$query = "SELECT * FROM table WHERE category='{$thecategory}'"; 
if(array_key_exists('subCategory', $GET)){ 
    $subCategory = mysql_real_escape_string($_GET['subCategory']);
    $query .= " AND subCategory='{$subCategory}'"; 
} 
$result = mysql_query($query); 

[JordashTalon]

Yeah, good point, makes for cleaner PHP code

[aguroyz]

try this..

$query2 = empty($_GET['subcat'])? ' ' : 'AND subcat ={$_GET[subcat]} ' //but clean the $_GET first before appending...

$query = "SELECT * FROM table WHERE category='$thecategory'.$query2."

I never really checked it though.. but i tried it before.

[jagat_21]

Hi,

You can try this also.

$result = mysql_query("SELECT * FROM table WHERE category='{$thecategory}' AND IF( '{$subCategory}' <> '' , subCategory = '{$subCategory}','')");

[jagat_21]

Another way 2 do the same thing ::

$result = mysql_query("SELECT * FROM table WHERE category='{$thecategory}' AND IF( '{$subCategory}' IS NOT NULL, subCategory = '{$subCategory}','')");