NULL values

------=_NextPart_000_0025_01CB9330.A255DB00
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable


When I do the following query in mySQL only 1 record is retrieved. =20

SELECT * FROM `paypal_payment_info` WHERE `os1` NOT LIKE =
'commission_paid'=20

I am surprised by this. This one record has no characters in it, but =
the â€=9CINSERT INTOâ€=9D that created it used: ( `os1` ) VALUES =
( ‘’ ) instead of: ( `os1` ) VALUES ( NULL ) . There =
are a number of records where `os1` is NULL. I would like these rows to =
retrieve as well. How do I make a WHERE clause for a cell that is NULL =
? =20

Ron

The Verse of the Day
â€=9CEncouragement from Godâ€=99s Wordâ€=9D
http://www.TheVerseOfTheDay.info

------=_NextPart_000_0025_01CB9330.A255DB00--

Re: NULL values

On Fri, Dec 3, 2010 at 6:25 PM, Ron Piggott
wrote:
>
> When I do the following query in mySQL only 1 record is retrieved.
>
> SELECT * FROM `paypal_payment_info` WHERE `os1` NOT LIKE 'commission_paid=
'
>
> I am surprised by this. =A0This one record has no characters in it, but t=
he =93INSERT INTO=94 that created it used: ( `os1` ) VALUES ( ‘’ ) inst=
ead of: ( `os1` ) VALUES ( NULL ) . =A0There are a number of records where =
`os1` is NULL. =A0I would like these rows to retrieve as well. =A0How do I =
make a WHERE clause for a cell that is NULL ?
>

You need to explicitly check for NULLs. The regular operators (<, >,
=3D, LIKE) work on values. NULL columns have no value, so you need to
use IS NULL or IS NOT NULL. Also, if you're not doing wildcard
matches, you should probably just use <> or =3D:

SELECT * FROM `paypal_payment_info` WHERE (`os1` <> 'commission_paid'
OR `os` IS NULL)

Scotty

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: NULL values

--20cf3054a703bfbbf10496941e68
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Dear Ron

Or try this

SELECT * FROM `paypal_payment_info` WHERE ifnull(os1, '') <>
'commission_paid'
============
regds
amit

"The difference between fiction and reality? Fiction has to make sense."


On Sat, Dec 4, 2010 at 7:55 AM, Ron Piggott
wrote:

>
> When I do the following query in mySQL only 1 record is retrieved.
>
> SELECT * FROM `paypal_payment_info` WHERE `os1` NOT LIKE 'commission_paid=
'
>
> I am surprised by this. This one record has no characters in it, but the
> â€=9CINSERT INTOâ€=9D that created it used: ( `os1` ) VALUES ( =
‘’ ) instead of: (
> `os1` ) VALUES ( NULL ) . There are a number of records where `os1` is
> NULL. I would like these rows to retrieve as well. How do I make a WHER=
E
> clause for a cell that is NULL ?
>
> Ron
>
> The Verse of the Day
> â€=9CEncouragement from Godâ€=99s Wordâ€=9D
> http://www.TheVerseOfTheDay.info
>

--20cf3054a703bfbbf10496941e68--

Re: NULL values

On 4 December 2010 11:40, Amit Tandon wrote:
> Dear Ron
>
> Or try this
>
> SELECT * FROM `paypal_payment_info` WHERE ifnull(os1, '') <>
> 'commission_paid'
> ============
> regds
> amit
>
> "The difference between fiction and reality? Fiction has to make sense."
>
>
> On Sat, Dec 4, 2010 at 7:55 AM, Ron Piggott
> wrote:
>
>>
>> When I do the following query in mySQL only 1 record is retrieved.
>>
>> SELECT * FROM `paypal_payment_info` WHERE `os1` NOT LIKE 'commission_pai=
d'
>>
>> I am surprised by this.  This one record has no characters in it, b=
ut the
>> â€=9CINSERT INTOâ€=9D that created it used: ( `os1` ) VALUES ( =
‘’ ) instead of: (
>> `os1` ) VALUES ( NULL ) .  There are a number of records where `os1=
` is
>> NULL.  I would like these rows to retrieve as well.  How do I =
make a WHERE
>> clause for a cell that is NULL ?
>>
>> Ron
>>
>> The Verse of the Day
>> â€=9CEncouragement from Godâ€=99s Wordâ€=9D
>> http://www.TheVerseOfTheDay.info
>>
>

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators. html#function_i=
snull

where isnull(column, '') <> 'value'



--=20
Richard Quadling
Twitter : EE : Zend
@RQuadling : e-e.com/M_248814.html : bit.ly/9O8vFY

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php