ignoring/excluding empty and null variables from select and where statement

[dalmar]

Hello,

i am strugling with dropdown list search of mysql. the drop down has 3 options and after the submit, if all 3 options are selected then my mysql query returns the results pretty well.The problem is when 1 or 2 options are selected,so there is/are empty variables, then there will be no results from mysql at all. here is my query:

$result = mysql_query("SELECT * FROM table WHERE (provider = '$provider') AND (period = $period) AND (price <= '$price') ORDER BY ABS(price) asc");

what i want is to skip/ignore a condition if its NULL and the variable is empty or not set.
i have tried with IS NULL and IFNULL functions but it didn't work, i think i am missing something.

i would really appreciate if i am pointed to the right direction or get an example.
thanks in advance

[LifeinAsia]

When you build your query string, you need to exclude any missing parameters. I don't know PHP, so I can't give you the exact implementation, but it should be something like:
$QueryString="SELECT * FROM table WHERE 1=1";
If Exists ($provider) $QueryString=$QueryString+" AND (provider = '$provider')";
If Exists ($period) $QueryString=$QueryString+" AND (period = '$period')";
If Exists ($price) $QueryString=$QueryString+" AND (price = '$price')";
$QueryString=$QueryString+" ORDER BY ABS(price)";
$result = mysql_query($QueryString);

[dalmar]

hi lifeinasia,
thank you very much. i have implemented the php part, it basically checks if the variable is not empty. althought i think i am closer to the solution now, if i select all of the options no results comes out, and obviusly no results if i select 1 or 2 options. here is the complete code.

$QueryString="SELECT * FROM table WHERE 1=1";

if (!empty($provider)) {
$QueryString=$QueryString+" AND (provider = '$provider')";
}

if (!empty($contract)) {
$QueryString=$QueryString+" AND (period = '$period')";
}

if (!empty($price)) {
$QueryString=$QueryString+" AND (price = '$price')";
}

$QueryString=$QueryString+" ORDER BY ABS($price)";
$result = mysql_query($QueryString);

thank you very much again

[LifeinAsia]

ECHO $QueryString and make sure it's what you are expecting.

Also, it may be obvious, but make sure the table actually does have data meeting the parameters selected. :)