Welcome to WebmasterWorld Guest from 54.162.227.136

Forum Moderators: open

Message Too Old, No Replies

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

     
11:12 pm on May 11, 2010 (gmt 0)

5+ Year Member



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
11:30 pm on May 11, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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);
12:22 am on May 12, 2010 (gmt 0)

5+ Year Member



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
3:41 pm on May 12, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month



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. :)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month