Mysql check if column is NULL how?

[Jawn]

Hi im trying to check if a column is = null but it doesnt seem to work anyone kn ow how?


SELECT * FROM images WHERE username = 'aaa' AND albumid = NULL ORDER BY id DESC LIMIT 0,20


Best Regards
Jawn

 

[Hitch]

SELECT * FROM images WHERE username = 'aaa' AND WHERE albumid = '' ORDER BY id DESC LIMIT 0,20

Should work?

 

[Jawn]

I get


#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE albumid = '' ORDER BY id DESC LIMIT 0,20' at line 1

 

[niteowl660]

I get


#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE albumid = '' ORDER BY id DESC LIMIT 0,20' at line 1

albumid = '' <-- make sure you are using two single quotes. I beleive is right

 

[TwistMyArm]

albumid = ''

is probably not valid SQL if albumid is an integer column.

I can't see why your original query doesn't work... what exactly is happening with it? Is it that you're just getting no items returned or are you getting a SQL error of some sort?

 

[Jawn]

It gets no items and its a integer column

 

[TwistMyArm]

So just to be sure... are you certain the value in the column is NULL and not 0?

What happens if you use:
WHERE albumid = 0

instead?

 

[Jawn]

im 100% sure its NULL just checked with phpmyadmin

 

[TwistMyArm]

Hmm... and I'm assuming you're using the PHPMyAdmin to run this query, right?

What happens if you use:
SELECT * FROM `images` WHERE `username` = 'aaa' AND `albumid` = NULL ORDER BY id DESC LIMIT 0,20

instead?

The backticks force MySQL to know that you are referring to column names.

 

[Luc]

all of you are wrong.

IS NULL

SELECT * FROM images WHERE username = 'aaa' AND albumid IS NULL ORDER BY id DESC LIMIT 0,20

common mistake. above assumes that you are using a somewhat recent version of mysql.

 

[Jawn]

all of you are wrong.

IS NULL

SELECT * FROM images WHERE username = 'aaa' AND albumid IS NULL ORDER BY id DESC LIMIT 0,20

common mistake. above assumes that you are using a somewhat recent version of mysql.

thanks it works

 

[TwistMyArm]

Eek... now that is embarrassing.

Good work Luc.

 

[Luc]

Eek... now that is embarrassing.

Good work Luc.

nothing to be embarrassed about. i had the same problem a few months back when i moved from an old mysql version to a new one. the old used =, new used IS and things looked weird.

glad i could help.