Re: [PHP-DB] 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_paid' >> >> I am surprised by this. This one record has no characters in it, but the >> “INSERT INTO” 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 >> “Encouragement from God’s Word” >> http://www.TheVerseOfTheDay.info >> > http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_isnull where isnull(column, '') <> 'value' -- 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
Re: [PHP-DB] NULL values
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 > “INSERT INTO” 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 > “Encouragement from God’s Word” > http://www.TheVerseOfTheDay.info >
Re: [PHP-DB] 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. This one record has no characters in it, but the > “INSERT INTO” 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 ? > You need to explicitly check for NULLs. The regular operators (<, >, =, 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 =: 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
[PHP-DB] NULL values
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 “INSERT INTO” 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 “Encouragement from God’s Word” http://www.TheVerseOfTheDay.info
[PHP-DB] NULL Values
I have a loop process that reads a record out of a flat file, stores the data for a given record in an array, writes the record to the db, resets the data in the array, then iterates again thru the loop. Not every record has a value for each field. For example, Not every record has a value for the field "cus034a". Since this is numeric type data, I do not want the value in the db to be "0" if there is no value for the field (it should be NULL). # partial table definition cus034a TINYINT UNSIGNED NULL, sat01 TINYINT UNSIGNED NULL, What I try to do is set the array to NULL values before iterating the loop again. However, if I do this and the next record does not have a value for "cus034a", the value in the db still ends up being "0". $data_array["cus034a"] = NULL; $data_array["sat01"] = NULL; I must be doing something wrong? Thank you. Zach Curtis POPULUS -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED]
