RE: [PHP-DB] time field query problems.
Thanks, I wasn't sure what kind of field to use, I'll give the timestamp a try and set 0 as default. Regards, Steve. -Original Message- From: DL Neil [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 6:34 PM To: Steve Bradwell; [EMAIL PROTECTED]; Php-Db (E-mail) Subject: Re: [PHP-DB] time field query problems. Steve, For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is NULL. If this cannot be done in a query, whats the best way to compare time in php? The best way to compare time in PHP is to use the MySQL RDBMS that is managing/retrieving the data for you. Recommendation 1: do not use a Time field (you did mean the back 'half' of a Date-time field didn't you?). Because you are (apparently only) using this field to temporarily lock a row, the value is only ever used for computation (cf display). A Timestamp field is best for computation - a Time field for presentation. Consider also storing such data as an integer field or beware the automatic update feature for Timestamp fields. Recommendation 2: re-consider the (default) use of NULL - this may be the root of the question you're asking: what if the row has never been 'locked' and attempt the (above) SELECT? (then the last comparison clause would be illogical) If the default were zero (0 or 00:00:00) and the retrieval logic updated slightly, things should be less complicated. Regards, =dn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] time field query problems.
On Tuesday 30 July 2002 02:41, Steve Bradwell wrote: Sorry to repost but I typed in the wrong sql statement in my previous post. For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; I think you have to use something like: ... WHERE DATE_ADD(EDIT_LOCK, ...) ... Check manual for details. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Intuition, however illogical, is recognized as a command prerogative. -- Kirk, Obsession, stardate 3620.7 */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] time field query problems.
Ya I checked and the manual said that with a version 3.23 or higher you can use + and - signs instead of the date_add subtract. Either way it just returns null. Ever tried somthing like this? Thanks, Steve. -Original Message- From: Jason Wong [mailto:[EMAIL PROTECTED]] Sent: Monday, July 29, 2002 3:13 PM To: [EMAIL PROTECTED] Subject: Re: [PHP-DB] time field query problems. On Tuesday 30 July 2002 02:41, Steve Bradwell wrote: Sorry to repost but I typed in the wrong sql statement in my previous post. For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; I think you have to use something like: ... WHERE DATE_ADD(EDIT_LOCK, ...) ... Check manual for details. -- Jason Wong - Gremlins Associates - www.gremlins.com.hk Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* Intuition, however illogical, is recognized as a command prerogative. -- Kirk, Obsession, stardate 3620.7 */ -- 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] time field query problems.
Steve, For some reason the below statement is not working. Can anyone tell me why? Select EDIT_LOCK from ordmaster where EDIT_LOCK + INTERVAL 10 MINUTE now() AND ORDER_NO = '5' AND EDIT_LOCK 0; -EDIT_LOCK is a MySQL (ver 3.23.49-max) time field, allows nulls, default is NULL. If this cannot be done in a query, whats the best way to compare time in php? The best way to compare time in PHP is to use the MySQL RDBMS that is managing/retrieving the data for you. Recommendation 1: do not use a Time field (you did mean the back 'half' of a Date-time field didn't you?). Because you are (apparently only) using this field to temporarily lock a row, the value is only ever used for computation (cf display). A Timestamp field is best for computation - a Time field for presentation. Consider also storing such data as an integer field or beware the automatic update feature for Timestamp fields. Recommendation 2: re-consider the (default) use of NULL - this may be the root of the question you're asking: what if the row has never been 'locked' and attempt the (above) SELECT? (then the last comparison clause would be illogical) If the default were zero (0 or 00:00:00) and the retrieval logic updated slightly, things should be less complicated. Regards, =dn -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
