Select...where....

[murphy]

Quick question:

I am using this:

$result = mysql_query("SELECT * FROM mytable WHERE $sortby != '-' ORDER BY $sortby $sortorder,$sortby2 $sortorder2,$sortby3 $sortorder3");

But i want to also only choose results if another field ($Include) is not null...I tried this:

$result = mysql_query("SELECT * FROM mytable WHERE $sortby != '-' AND $Include IS NOT NULL ORDER BY $sortby $sortorder,$sortby2 $sortorder2,$sortby3 $sortorder3");

But that doesn't work...how do you do multiple WHEREs?

[Horus_Kol]

i can't see anything wrong with your syntax...

personally, I would do this:


$query = "SELECT * FROM mytable";
$query .= " WHERE ". $sortby ." != '-' AND ". $Include ." IS NOT NULL";
$query .= " ORDER BY ". $sortby ." ". $sortorder .",". $sortby2 ." ". $sortorder2 .",". $sortby3 ." ". $sortorder3;

echo $query; // only while debugging

$result = mysql_query($query, $cnxn); // I always specify the connection - good practice


that way, you can check what query is actually be sent to the MySQL database and check if the field names are correct

[murphy]

Ok, thanks ...but what is this: $cnxn ? I am coding inside an exisiting cms application and as such don't need to specify a connection to the db as every page is doing that already...

[Horus_Kol]

when you connect to a mysql database - the function returns a connection id... so you can do:


$cnxn = mysql_pconnect(...);


its good practice to specify this connection in all interactions with the database, because they otherwise assume the LAST connection made - which in some applications might not be the one you actually want to use.

mysql_pconnect()

[murphy]

This is weird...i tried changing the include column to 'no' instead of NULL and then did:

WHERE $sortby != 'no' and that worked! So i just cant get NULLs to work

[The Man]

Are you wrapping null with single quotes? Try it without as I think NULL is a keyword.

[Horus_Kol]

murphy - i think you might find that you have NULL values in the $Insert column for records where $sortby are "-"... and this is why it is not returning anthing...

[murphy]

This was solved by using:

WHERE $sortby !='' //empty single quotes

instead of

$sortby != NULL

Never managed to get NULLS working...

Tried IS NOT NULL, != NULL, != 'NULL' none worked.

It would be ok, but i am using a float field for one of my criteria and a '0' in a float field is being interpreted as an empty field '' <- (empty single quotes), so when i do $sortby !='', any of my float fields that genuinely have a '0' value are not being returned...