Complex Query? I need Help on this one.

tony_l

I have an application I am writing that involves DATES

I need to know a few things, so I can get this thing working right...

How can I Code my page to display a week at a time? I want to do this without hard coding in dates. Bascially, I want to list weeks such as Monday, Jan 1, 2007 - Jan 7, 2007 and the next line would be the next week. Is there a way to do this that is accurate?
How can I then pull a bunch of rows from a table in a mysql database utilizing a field of the type "DATE" that corresponds to the dates in the week? Let me explain this better here - I want to get ALL entries that are between Jan 1 and Jan 7. How can I do this? I have tried the BETWEEN operation in a where clause, but it is not working the way I thought it would.

here is the SQL Query that I am attempting to use (and only returns 4 of the 18 entries that fall within the dates set.

SELECT
*
FROM journal_entries, items
WHERE
(items.item_number = journal_entries.id)
AND
(journal_entries.date BETWEEN '$startDate' AND '$endDate')
ORDER BY
journal_entries.date

Note $startDate and $endDate are set in the script in this format - "2007-01-01". journal_entries.date is of the "DATE" type.

tony_l

Ok, I figured out how to extract data from the database - I had a glitch with the name of the field, so that was my bad.

I still need to figure out how to code a page to display a week. I have tried the formula -

60 seconds 60 Minutes 24 Hours * 7 Days = 1 week of seconds.

but this does not seem to be very reliable or accurate - or is it?

NogDog

tony_l wrote:
...
I still need to figure out how to code a page to display a week. I have tried the formula -

60 seconds 60 Minutes 24 Hours * 7 Days = 1 week of seconds.

but this does not seem to be very reliable or accurate - or is it?

It's hard to say without a bit more information as to what you want to do with the value. You might want to use strtotime(). For instance, to get the UNIX times for tomorrow and the next day:

$tomorrow = strtotime('+1 day');
$theDayAfter = strtotime('+1 day', $tomorrow);