CODEWITHSUNDEEP
mysqlfieldwhere-clausezeroclause
BrownChiLD
BrownChiLD

Reputation: 3713

Mysql returning rows with zero (0) column data in WHERE clause

I have a simple query for my table:

SELECT * from delivery_receipts WHERE siid='';

This table's unique key is drid, (irrelevant for this question).. The targeted column (siid) is an int(11) field, non index and non unique...

Anyway my problem is that, when I run this, mysql returns ALL ROWS that has:

SELECT drid,siid from delivery_receipts WHERE siid='0'

But of course if I search with the siid specified, only the rows w/ that siid match comes up....

So it seems my table is returning a match on all rows that has "0" when searching for ''

Upvotes: 0

Views: 1134

Answers (1)

Sir Rufo
Sir Rufo

Reputation: 19106

The Select-Statement is being interpreted by the server and so it is recognized, that there is the need of converting data from char to int. And an empty char as you provide will be converted into 0 because of default value for int.

So if you want to get all rows with no value (NULL) you have to do

SELECT * from delivery_receipts WHERE siid IS NULL;

EDIT

SELECT * from delivery_receipts WHERE ( siid = '$siid' and '$siid' <> '' ) OR ( '$siid' = '' AND siid IS NULL );

Upvotes: 1

Related Questions