0

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 ''

nickhar
  • 19,981
  • 12
  • 60
  • 73
BrownChiLD
  • 3,545
  • 9
  • 43
  • 61

1 Answers1

1

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 );
Sir Rufo
  • 18,395
  • 2
  • 39
  • 73
  • Thanks very much for that.. that explains why im getting this issue.. and unfortunately I can't use "siid IS NULL" method.. long story short, it's actually for a php script for a lil search routine im doing... siid='$siid' ..and sometimes the $siid variable will not be specified, and the search results should be blank.. it's ok.. i know what to do to fix this, atleast i know i can't do anything on the mysql side of things as this is the default behavior of the engine.. have to do this php side – BrownChiLD Nov 04 '12 at 13:25
  • @BrownChiLD you can do this on php side, but you can also do it on MySQL side - look at my edit ;o) – Sir Rufo Nov 04 '12 at 14:10
  • ahh yes.. that mySQL side is pretty slick! haha but i got into the habit of keeping my SQL's as simple as possible and i do anything i can do on PhP side i do there.. coz im much better at php than SQL hehe.. and also to offload some stress of the sql engine too... thanks very much though! :) much appreciated anyway just my 2 cents – BrownChiLD Nov 05 '12 at 03:17