Re: [PHP-DB] MySQL Conditional Trigger
Aleksandar Vojnovic wrote: You could try with the <> operator. !=, <>Not equal operator Same issue as using '=': mysql> select 2 <> null; +---+ | 2 <> null | +---+ | NULL | +---+ 1 row in set (0.00 sec) Comparing anything with null returns null. (I'm not asking for a solution, I'm stating it as fact). Or you could try it this way - ISNULL(col_or_data_to_check). Example: mysql> SELECT ISNULL(1+1); +-+ | ISNULL(1+1) | +-+ | 0 | +-+ 1 row in set (0.02 sec) You can also use 'coalesce' to set default value for null columns. http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_coalesce http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Conditional Trigger
You could try with the <> operator. !=, <>Not equal operator Or you could try it this way - ISNULL(col_or_data_to_check). Example: mysql> SELECT ISNULL(1+1); +-+ | ISNULL(1+1) | +-+ | 0 | +-+ 1 row in set (0.02 sec) Aleksander Chris wrote: Chris wrote: unknown compared to anything is unknown. he was talking about plsql and condition evaluation (ends with true or false), your point is absolutely useless. The context is in the database - my example is in the database. How is that useless? In case you missed it, this is the context: > Which only works when ShipDate was not NULL to begin with. > I suppose it evaluates the following to FALSE > IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN > (not liking the "NULL != '2008-10-31'" part) > > Please give me the correct syntax. > TIA > > anything compared to NULL is always false ie - you are wrong. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Conditional Trigger
Chris wrote: unknown compared to anything is unknown. he was talking about plsql and condition evaluation (ends with true or false), your point is absolutely useless. The context is in the database - my example is in the database. How is that useless? In case you missed it, this is the context: > Which only works when ShipDate was not NULL to begin with. > I suppose it evaluates the following to FALSE > IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN > (not liking the "NULL != '2008-10-31'" part) > > Please give me the correct syntax. > TIA > > anything compared to NULL is always false ie - you are wrong. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Conditional Trigger
unknown compared to anything is unknown. he was talking about plsql and condition evaluation (ends with true or false), your point is absolutely useless. The context is in the database - my example is in the database. How is that useless? -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Conditional Trigger
Original Message Subject: Re: [PHP-DB] MySQL Conditional Trigger From: Chris To: OKi98 Date: 8.12.2008 23:09 anything compared to NULL is always false Actually it's null. mysql> select false = null; +--+ | false = null | +--+ | NULL | +--+ 1 row in set (0.01 sec) mysql> select 1 = null; +--+ | 1 = null | +--+ | NULL | +--+ 1 row in set (0.00 sec) mysql> select 2 = null; +--+ | 2 = null | +--+ | NULL | +--+ 1 row in set (0.00 sec) unknown compared to anything is unknown. he was talking about plsql and condition evaluation (ends with true or false), your point is absolutely useless. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Conditional Trigger
Fortuno, Adam wrote: All, Chris makes a good point i.e., "unknown compared to anything is unknown". His comment made me want to clarify my earlier note. In my earlier example, I wasn't suggesting that-that logic be coded in the database. I was assuming it would be in the page: I tested this, and it worked. Here is why, PHP (like pretty much every other language) silently casts a NULL to false: "When converting to boolean, the following values are considered FALSE: - the boolean FALSE itself - the integer 0 (zero) - the float 0.0 (zero) - the empty string, and the string "0" - an array with zero elements - an object with zero member variables (PHP 4 only) - the special type NULL (including unset variables)" ("Booleans", php.net, 05 Dec. 2008). If you use the shortcut php check, but you can do it the longer way. if ($value !== null) { ... } Can you turn off email notifications too please? It's rather annoying when it's on a public mailing list. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Conditional Trigger
All, Chris makes a good point i.e., "unknown compared to anything is unknown". His comment made me want to clarify my earlier note. In my earlier example, I wasn't suggesting that-that logic be coded in the database. I was assuming it would be in the page: I tested this, and it worked. Here is why, PHP (like pretty much every other language) silently casts a NULL to false: "When converting to boolean, the following values are considered FALSE: - the boolean FALSE itself - the integer 0 (zero) - the float 0.0 (zero) - the empty string, and the string "0" - an array with zero elements - an object with zero member variables (PHP 4 only) - the special type NULL (including unset variables)" ("Booleans", php.net, 05 Dec. 2008). If you were going to code it in the database, I'd suggest something like this: --Using T-SQL here... DECLARE @TestNullValue SMALLDATETIME SET @TestNullValue = NULL If (@TestNullValue Is Null) PRINT 'Evaluates to true!'; ELSE PRINT 'Evaluates to false!'; In either case, this should have the same net result. Be Well, A- -Original Message- From: Chris [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2008 5:10 PM To: OKi98 Cc: php-db@lists.php.net Subject: Re: [PHP-DB] MySQL Conditional Trigger > anything compared to NULL is always false Actually it's null. mysql> select false = null; +--+ | false = null | +--+ | NULL | +--+ 1 row in set (0.01 sec) mysql> select 1 = null; +--+ | 1 = null | +--+ | NULL | +--+ 1 row in set (0.00 sec) mysql> select 2 = null; +--+ | 2 = null | +--+ | NULL | +--+ 1 row in set (0.00 sec) unknown compared to anything is unknown. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Conditional Trigger
anything compared to NULL is always false Actually it's null. mysql> select false = null; +--+ | false = null | +--+ | NULL | +--+ 1 row in set (0.01 sec) mysql> select 1 = null; +--+ | 1 = null | +--+ | NULL | +--+ 1 row in set (0.00 sec) mysql> select 2 = null; +--+ | 2 = null | +--+ | NULL | +--+ 1 row in set (0.00 sec) unknown compared to anything is unknown. -- Postgresql & php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] MySQL Conditional Trigger
Dee, If all you're trying to code is that a value of NULL equates to FALSE and a date value (whatever date value) equates to true, you can use something like this: If ($MyVariable) { //... true path blah... } else { //... false path blah... } You can use this because NULL equates to false. If you prefer something more concise, try the following: $MyVariable ? //True : //False I'm not a PHP guy so take this with a grain of salt. If I'm full of it, don't hesitate to correct me. A- -Original Message- From: OKi98 [mailto:[EMAIL PROTECTED] Sent: Monday, December 08, 2008 4:33 PM To: Dee Ayy Cc: php-db@lists.php.net Subject: Re: [PHP-DB] MySQL Conditional Trigger Original Message -------- Subject: [PHP-DB] MySQL Conditional Trigger From: Dee Ayy <[EMAIL PROTECTED]> To: php-db@lists.php.net Date: 31.10.2008 17:09 > I don't think my trigger likes a comparison with a variable which is > NULL. The docs seem to have a few interesting variations on playing > with NULL and I was hoping someone would just throw me a fish so I > don't have to try every permutation (possibly using CASE, IFNULL, > etc.). > > If my ShipDate (which is a date datatype which can be NULL) changes to > a non-null value, I want my IF statement to evaluate to TRUE. > IF NULL changes to aDate : TRUE > IF aDate changes to aDifferentDate : TRUE > IF anyDate changes to NULL : FALSE > > In my trigger I have: > ... > IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN > ... > > Which only works when ShipDate was not NULL to begin with. > I suppose it evaluates the following to FALSE > IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN > (not liking the "NULL != '2008-10-31'" part) > > Please give me the correct syntax. > TIA > > anything compared to NULL is always false NULL = NULL (NULL included) => false NULL != anything (NULL included) => false that's why IS NULL exists I would go this way: IF NVL(OLD.ShipDate, -1) != NVL(NEW.ShipDate, -1) THEN -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] MySQL Conditional Trigger
Original Message Subject: [PHP-DB] MySQL Conditional Trigger From: Dee Ayy <[EMAIL PROTECTED]> To: php-db@lists.php.net Date: 31.10.2008 17:09 I don't think my trigger likes a comparison with a variable which is NULL. The docs seem to have a few interesting variations on playing with NULL and I was hoping someone would just throw me a fish so I don't have to try every permutation (possibly using CASE, IFNULL, etc.). If my ShipDate (which is a date datatype which can be NULL) changes to a non-null value, I want my IF statement to evaluate to TRUE. IF NULL changes to aDate : TRUE IF aDate changes to aDifferentDate : TRUE IF anyDate changes to NULL : FALSE In my trigger I have: ... IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN ... Which only works when ShipDate was not NULL to begin with. I suppose it evaluates the following to FALSE IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN (not liking the "NULL != '2008-10-31'" part) Please give me the correct syntax. TIA anything compared to NULL is always false NULL = NULL (NULL included) => false NULL != anything (NULL included) => false that's why IS NULL exists I would go this way: IF NVL(OLD.ShipDate, -1) != NVL(NEW.ShipDate, -1) THEN -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] MySQL Conditional Trigger
I don't think my trigger likes a comparison with a variable which is NULL. The docs seem to have a few interesting variations on playing with NULL and I was hoping someone would just throw me a fish so I don't have to try every permutation (possibly using CASE, IFNULL, etc.). If my ShipDate (which is a date datatype which can be NULL) changes to a non-null value, I want my IF statement to evaluate to TRUE. IF NULL changes to aDate : TRUE IF aDate changes to aDifferentDate : TRUE IF anyDate changes to NULL : FALSE In my trigger I have: ... IF OLD.ShipDate != NEW.ShipDate AND NEW.ShipDate IS NOT NULL THEN ... Which only works when ShipDate was not NULL to begin with. I suppose it evaluates the following to FALSE IF NULL != '2008-10-31' AND '2008-10-31' IS NOT NULL THEN (not liking the "NULL != '2008-10-31'" part) Please give me the correct syntax. TIA -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php