Query: Where = nothing

[DoD]

Im trying to make my script so the MySQL will only read the records with a blank "Deleted" field (this is so it cannot be added again until this has been deleted)

So how can i adapt WHERE to make it like WHERE Deleted='' ? or is that right?

[[deleted]]

"So how can i adapt WHERE to make it like WHERE Deleted='' ? or is that right?"

it depends on what you mean by 'blank'

if you updated the record like:

UPDATE table
SET field=''
WHERE something

then the field contains an empty string and WHERE field='' will work.

[su1d]

... WHERE fld='' OR fld IS NULL
should work in virtually any case

[[deleted]]

".. WHERE fld='' OR fld IS NULL
should work in virtually any case"

Actually they only work in one case each.

An empty string is different from a NULL and is different from 'zero'.

Zo checking for IS NULL is entirely different from checking for =''.

[su1d]

Originally posted by vincente
Zo checking for IS NULL is entirely different from checking for =''.

Exactly! That's why I put'em together.
We don't know whether the field has been defined with NOT NULL flag in the table. Thuz, "an empty" field (which looks like "" in PHP) may take two values: NULL or "".
If we left only WHERE fld="", the result would always be FALSE for NULL values. So, we do need two conditions to cover all cases.

[[deleted]]

"So, we do need two conditions to cover all cases."

That is an option, but personally I prefer to find out which of the two is required and use only that option.

[DoD]

Hmmm an easier way is for me to set the default to 0

[[deleted]]

"Hmmm an easier way is for me to set the default to 0"

Remember that if you do that you'll always see '0', and you can nolonger tell wether this is a '0' because you never entered a value, or wether you put that '0' in there yourself.

Besides which, what happens if you really want to enter '0' as a value?

[DoD]

For what im doing its fine, as its on/off.