homepage Welcome to WebmasterWorld Guest from 54.204.231.110
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

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




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

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




msg:4131114
 11:30 pm on May 11, 2010 (gmt 0)

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




msg:4131126
 12:22 am on May 12, 2010 (gmt 0)

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




msg:4131427
 3:41 pm on May 12, 2010 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved