PhP, Access/SQL, Data comparison problem. (I'm new to PhP)

[Damiel Fisher]

Hi All!
I'm fairly new to PhP and basicly trying to learn right now. Now I have
a problem - I have a fairly large collection of movies which people keep
borrowing from me. And then not returning. So I've put together an
Access database with three tables - my media library, a list of friends
and email addresses, and a borrow table which uses foreign keys from the
other two tables to see who has borrowed what. Borrow Table also has two
other fields - the date borrowed, which is set to equal now() inside of
access, and the date due, which is set to equal now()+14 - they are both
"short date"s.

Now I had another prloblem which was this: people would call me up at
ungodly hours of the night asking if I had, say, The Excorcist. I would
have to turn on my monitor, load up access, and then scroll through the
list in order to confirm weather or not I had that title (I do). I've
solved this problem by having a really cool and sexy PhP script which
querries the database with an SQL querry based on the user's form. So
thats that problem solved...

So my friends come round at 2:45am wanting to borrow, say The Excorcist,
or Mememento, and four weeks later I am looking for the exact same
title. With my cool and sexy database its now really easy to know who
has what, but what I really want is a PhP script (which I can set to run
automaticly via the task scheduler) which would see what was overdue and
then email that person asking them to return it.

So a fairly simple problem but its been giving me a headache for the
better part of three weeks. Now() in PhP returns the Unix datatime, I
believe, which is of course not immediately compatible for comparison
with the date-time returned by Access which is of this form:

2004-08-27 00:00:00

I dont really mind how this problem is solved - weather by SELECTing
from the database WHERE overdue < now(), or by doing the same thing
using PhPs date() function.

But I would very much like some help wiht this problem as it has been
plaguing me for three weeks now.

(IIS and Apache on WinXP Prof. Corp. PhP version 4.3.7. Dynamic DNS:
[url]http://djcf.sytes.net[/url]. The script I am working on is here:
[url]http://djcf.sytes.net/mylibrary/email.php[/url])

Thanks!

Daniel

[peter]

> So a fairly simple problem but its been giving me a headache for the

> better part of three weeks. Now() in PhP returns the Unix datatime, I
> believe, which is of course not immediately compatible for comparison
> with the date-time returned by Access which is of this form:
>
> 2004-08-27 00:00:00
>
> I dont really mind how this problem is solved - weather by SELECTing
> from the database WHERE overdue < now(), or by doing the same thing
> using PhPs date() function.

Never used access with SQL but all that should be required is for you to do
a select using a where clause and changing the format of the now().

In mySQL there is a date function that does it and I assume Access has the
same.

An example SQL for mySQL might be

SELECT date FROM date_table WHERE date < DATE_FORMAT(NOW(), '%Y-%m-%d
%H:%i:%s')

the above query is not checked and probably wont work in access but gives
you an idea of how it may be achieved

[GoL]

On Sat, 28 Aug 2004 21:23:06 +0100, "peter" <atsp76@dsl.pipex.com>
wrote:

>> So a fairly simple problem but its been giving me a headache for the
>> better part of three weeks. Now() in PhP returns the Unix datatime, I
>> believe, which is of course not immediately compatible for comparison
>> with the date-time returned by Access which is of this form:
>>
>> 2004-08-27 00:00:00
>>
>> I dont really mind how this problem is solved - weather by SELECTing
>> from the database WHERE overdue < now(), or by doing the same thing
>> using PhPs date() function.

>
>Never used access with SQL but all that should be required is for you to do
>a select using a where clause and changing the format of the now().
>
>In mySQL there is a date function that does it and I assume Access has the
>same.
>
>An example SQL for mySQL might be
>
>SELECT date FROM date_table WHERE date < DATE_FORMAT(NOW(), '%Y-%m-%d
>%H:%i:%s')
>
>the above query is not checked and probably wont work in access but gives
>you an idea of how it may be achieved
>

Use date() function...

$now = date("Y-m-d H:i:s");
$query = "SELECT * FROM borrowed_movies WHERE duedate < '$now' "