RE: [PHP-DB] time field query problems. From: Steve Bradwell (SteveB <email protected>)
Date: 07/30/02

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:PHPml <email protected>]
Sent: Monday, July 29, 2002 6:34 PM
To: Steve Bradwell; mysql <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 <mysql-thread115837 <email protected>>
To unsubscribe, e-mail
<mysql-unsubscribe-SteveB=compxnet.com <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