sql for row where value=''

[meltedown]

This is probably a stupid question but I don't get it.
I have a table called sites

2 rows have a name='siteisdow n'
1 row has a value of 1 and the other has a value of '' (nothing)

This works fine, it returns 1 row with a value of 1, along with all the
other columns.
"SELECT * from sites where name='siteisdow n' AND value =".TRUE);

This returns nothing:
"SELECT * from sites where name='siteisdow n' AND value ='' ";

I don't understand why this returns nothing.
How do I call the siteisdown row with a value of nothing ?

[Malcolm Dew-Jones]

Re: sql for row where value=''

meltedown (groups2@reenie .org) wrote:
: This is probably a stupid question but I don't get it.
: I have a table called sites

: 2 rows have a name='siteisdow n'
: 1 row has a value of 1 and the other has a value of '' (nothing)

: This works fine, it returns 1 row with a value of 1, along with all the
: other columns.
: "SELECT * from sites where name='siteisdow n' AND value =".TRUE);

?

: This returns nothing:
: "SELECT * from sites where name='siteisdow n' AND value ='' ";

Try the syntax

SELECT * from sites where name='siteisdow n' AND value is NULL;


The database is probably one that uses '' as a shorthand for the NULL
value. NULL is a special value that represents having no value or a value
which is unknown. There aren't any values that can ever be equal "no
value", and so value='' selects nothing.

As an aside, you'll note that "NULL = NULL" will also return no rows.
That is because NULL also represents an "unknown" value, and it isn't
possible to say that two unknown values are the same as each other (since
you don't know what values they are).

[meltedown]

Re: sql for row where value=''

Malcolm Dew-Jones wrote:[color=blue]
> meltedown (groups2@reenie .org) wrote:
> : This is probably a stupid question but I don't get it.
> : I have a table called sites
>
> : 2 rows have a name='siteisdow n'
> : 1 row has a value of 1 and the other has a value of '' (nothing)
>
> : This works fine, it returns 1 row with a value of 1, along with all the
> : other columns.
> : "SELECT * from sites where name='siteisdow n' AND value =".TRUE);
>
> ?
>
> : This returns nothing:
> : "SELECT * from sites where name='siteisdow n' AND value ='' ";
>
> Try the syntax
>
> SELECT * from sites where name='siteisdow n' AND value is NULL;
>
>
> The database is probably one that uses '' as a shorthand for the NULL
> value. NULL is a special value that represents having no value or a value
> which is unknown. There aren't any values that can ever be equal "no
> value", and so value='' selects nothing.
>
> As an aside, you'll note that "NULL = NULL" will also return no rows.
> That is because NULL also represents an "unknown" value, and it isn't
> possible to say that two unknown values are the same as each other (since
> you don't know what values they are).
>[/color]

OK thanks, I had tried that but my syntax was wrong. I used php null
instead of sql null
"SELECT * from sites where name='siteisdow n' AND value is ".NULL"

which of course doesn't compute.