Database between 2 dates....

jdh

I have having trouble (again) on the best way that I should do this. Here is my problem.

I would like to specify every week, then search the database for that days in that week. If it is included in that week, display it in week 1. Then take the next week, and if any values in the database in the 2nd week, display those in week 2.... and so on. I have an idea how I can do it for each week, and the next week... but what happends when I get to the end of the month? How should I go about saying it is the end of the month, and the get rest of the week from that?

Any ideas how to tackle this?

Thanks in advance,

hand

Take a look at "mySQL Date and Time Functions" -
WEEK(date,first) ... returns the week for a given date
http://www.mysql.com/doc/en/Date_and_time_functions.html

jdh

I am looking for something that can do this in php:

MySQL Database: dates
+-----+----------------+
| id | date_due |
+-----+----------------+
| 2 | 2002-08-26 |
| 3 | 2002-08-26 |
| 4 | 2002-08-30 |
| 5 | 2002-09-05 |
| 6 | 2002-09-06 |
| 7 | 2002-09-07 |
...............................

So, when php reads these values like so:

Week 1:
2
3
4
are due this week

Week 2:
5
6
7
are due in 2 weeks

Also, once we get past that week, 08-30-2002, it will not show that week, and move week 2 values to week 1, and week 3 to week 2.... etc

I want it to dynamicly update so it always gets whats do this week, next week, and 3 weeks ahead..... so it can forecast what is supposed to be due that week.

Thanks again,

hand

I know, you want to calculate the week with php 🙁

mysql> select * from hand;
+------+------------+
| id   | date_due   |
+------+------------+
|    1 | 2002-08-26 |
|    2 | 2002-08-26 |
|    3 | 2002-08-30 |
|    4 | 2002-09-05 |
|    5 | 2002-09-06 |
|    6 | 2002-09-07 |
+------+------------+
6 rows in set (0.00 sec)

mysql> select id,date_due,(week(date_due)-week(now())) as week from hand where (week(date_due)-week(now())) > 0;
+------+------------+------+
| id   | date_due   | week |
+------+------------+------+
|    1 | 2002-08-26 |    1 |
|    2 | 2002-08-26 |    1 |
|    3 | 2002-08-30 |    1 |
|    4 | 2002-09-05 |    2 |
|    5 | 2002-09-06 |    2 |
|    6 | 2002-09-07 |    2 |
+------+------------+------+
6 rows in set (0.00 sec)

mysql>

jasper

Or use Unix-timestamps. No need for special functions since PHP can use these with the "date"-function, database-layout is simple (just use a int or varchar) and easy sorting. For a week difference just try $mydate-(606024*7)...