My db table is like:
[id] [city] [temp] [cssstyle]
[1] [London] [10] [top:15px;left:5px]
[2] [Berlin] [10] []
[3] [Paris] [10] [top:15px;left:5px]
[4] [New York] [10] []
cssstyle is varchar(128)
Im trying to display only rows that have cssstyle with the below command:
mysql_query("SELECT * FROM cities WHERE cssstyle is not null")
but this is displaying all rows, i like to be displayed only London and Paris rows.
Thanks
You seem to store empty string ('') rather than NULL in the empty fields. Those are different things in MySQL.
Use this:
SELECT *
FROM cities
WHERE cssstyle > ''
By using "greater than" you give MySQL posibility to use an index range scan on cssstyle
SELECT * FROM cities WHERE cssstyle IS NOT NULL OR cssstyle <> ''
You can also use AND to check for both the conditions resulting into null
SELECT * FROM cities where cssstyle IS NOT NULL AND cssstyle <> ''
it will be also better if you insert NULL instead of '' in your table
then your query must be enough
SELECT * FROM cities where cssstyle IS NOT NULL
You can also try LENGTH function in MySQL:
SELECT *
FROM cities
WHERE LENGTH(cssstyle) > 0
I'm not sure your cssstyle column storing default value as null. Better check if not empty or is null.
SELECT * FROM cities WHERE cssstyle != '' OR cssstyle IS NOT NULL
NULL?