Limiting a query by number and date

[ninedoors]

I run a hockey league and want to post my schedule for the upcoming season.  The problem is that I have 100 games in the season and I want to limit the query to 30 games on the schedule that is posted on my website.  That part was no problem for me but I also would like to have those 30 games be determined by date.  So I would like the script to acquire the current date and post the next 30 games that are upcoming.  I have a date field in the daatbase that is in the form YYYY-MM-DD.  Any help would be great.  Thanks.

[Jessica]

you'll need to use the ORDER BY and LIMIT clause.

http://dev.mysql.com/doc/refman/4.1/en/select.html

[ninedoors]

Yes, I understand that part, it is the date part that I am having trouble with.  Any suggestions on that part?

[BlueSkyIS]

only select the records where the date is equal to or greater than now in ascending order, then limit the results to as many as you need (30 in this case.)

[Jessica]

Use PHP and date() to create a variable that is one month ago. Then use WHERE date > $monthAgo or something.

[ninedoors]

Ok, I took a break and tried to figure this problem out myself and couldn't.  Here is the code I have if anyone could help please let me know.

 

$today = date("Y-m-d");
$query  = "SELECT hometeam, visitingteam, showdate, time FROM $tbl_name WHERE date >= '$today' ORDER BY gamenumber LIMIT 0, 20";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
    
$game = $row['gamenumber']; 
    
echo "$game";
   

}

 

The reason I am trying to echo the gamenumber was to make sure that it was working.  All I get is a blank page when I run this query.  The date column in my database has the format 2007-09-22(Y-m-d).  I'm not sure if this is the problem or it is something else.  What I want to achieve here is to take the date the user looks at the webpage and give him the next 20 games on my schedule.  If you need anything else let me know.  Thanks in advance.

 

Nick

[BlueSkyIS]

you didn't select gamenumber, so this will be empty:

 

$game = $row['gamenumber'];

[ted_chou12]

$query  = "SELECT gamenumber, hometeam, visitingteam, showdate, time FROM $tbl_name WHERE date >= '$today' ORDER BY gamenumber LIMIT 0, 20";
$result = mysql_query($query);

while($row = mysql_fetch_assoc($result))
{
    
$game = $row['gamenumber']; 
    
echo "$game";
   

}