check null values with php or like variables (is null doesn't help)

[dix.selken]

Hello people, sorry for this long post, I want to build a simple query to check some variables... let's have this mysql table (some random data):

 

fruitcolorflavor

appleredsweet

lemongreenbitter

melonyellow{null}

coconut{null}{null}

 

if you query

select * from fruits where flavor is null

you obviously get melon and cocounut, but what if we want to get invariable a fruit regardless of the null/not null condition

 

let's have this php code:

$flavor = 'sweet';
$my_query = "select * from fruits where flavor = '$flavor'";

$flavor = '';
$my_query = "select * from fruits where flavor = '$flavor'";

 

the first example retrieves successfully returns apple, but the second query does not return anything, thats because null is not equal to '' in mysql...

 

do you have any suggestion how to solve this? checking the variable to be null before building the query is not possible since my actual table has many attributes, and any of them can be null, also an ifnull like in

where ifnull(attribute, '') = '' 

 

although a working solution, it could require php code edition if the table is altered.

 

thank you

[ldb358]

try:

$my_query = str_replace("''",'null',"select * from fruits where flavor = '$flavor'");

[fenway]

You'll have to check both conditions.