Getting an event from a database a week in advancee

Question

I am currently developing a sports website where one of the pages with be forthcoming fixtures in which the user will be able to what team and where the team are playing their next match.

I have a database with the following fields...

• ID
• TEAM NUMBER
• OPPOSITION
• VENUE
• DATE
• MEET TIME
• MATCH TYPE

So a row of data pulled from the DB and print_r'd may look like this

ID=>[1] TEAM NUMBER=>[1] OPPOSITION=>[YORKSHIRE] VENUE=>[HOME] DATE=>[2009/4/25] MEET TIME=>[13.00] MATCH TYPE=>[CUP]

My problem is i cannot work out how to show the next match dependent on what the current date is, so for example for now I want the site to show all the games that will happen over the weeken of the 25th April 2009 and then once that has gone the fixtures for the next weekend.

Hope this makes sense and some one give me an idea of how to tackle this.

Answer 1

you can get the system date (in Oracle using sysdate) and then add to it, so look for all records where DATE = sysdate + 7. You may have to play with this a little, formatting the date so that sysdate + 7 returns a date without the time, but that is basically what you need.

EDIT:

If you want the event between now and a week from now (if games are only on the weekend, then this will return next weekend's games) do

DATE > sysdate AND DATE <= sysdate + 7

Answer 2

To get the next match for team xxx

SELECT * 
FROM   myTable
WHERE  TEAM NUMBER = xxx
AND    DATE = ( SELECT MIN(DATE) 
                FROM   myTable
                WHERE  TEAM NUMBER = xxx
                AND    DATE        > NOW() )

I suspect this is what you really want, if matches only take place at weekends (which seems to be an assumption from your question).
Today + 7 days is not the same as next weekend unless today happens to be the same day of the week as the match.

Answer 3

select * from my_events where date between now() and date_add(now(), interval 7 day);

Should do it I think.

Answer 4

For MySQL check out the Date and Time functions. You can use a combination of CURDATE() and ADDDATE() to achieve what you need.

Answer 5

Your description is very vage but try something like this:

SELECT all_fields_you_need
FROM table_name
WHERE `DATE` > CURDATE() AND `DATE` <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)
ORDER BY `DATE` ASC

(not tested, just written as it came into my mind...)

Load it all into an array and display the data

Answer 6

Instead of relying entirely on MySQL, you can also use PHP's strtotime() function:

$query = "select * from my_events where date between now() and ".
date("Y-m-d", strtotime("+1 week"));