display 1 month of data beginning with current date

[tsp0928]

I have a table in mysql that has data displayed on a web page, but currently it shows all data. I would like to be able to limit the data displayed to only the next 30 days, including today. I have just enough mysql and php experience to do simple things and I tried to use google which returned a lot of information and examples that are hard for me to understand given my limited mysql and php knowledge. Here is what I am currently using and the web site for a live example is here

http://www.maysvillebands.com

// select all the shows in the database
$result = mysql_query("select $database_table.show_id, $database_table.month, $database_table.day, $database_table.year, $database_table.location, $database_table.details, $database_table.venue, $database_table.artist_id, $database_table_artists.artist_id, $database_table_artists.artist_name
from $database_table, $database_table_artists
where $database_table.artist_id = $database_table_artists.artist_id
order by year, month, day, artist_name",$db)
or die_now(&quot;<h2>Could not select shows</h2>&quot;

// output the current shows
echo(&quot;<div class='box'><center>Upcoming Shows</center>\n</div>\n<div class='box'>\n&quot;
echo(&quot;<table border='1' width='100%'>\n&quot;
echo(&quot;\t<tr>\n\t\t<td width=20%>artist</td>\n\t\t<td width=10%>date</td>\n\t\t<td width=20%>venue</td>\n\t\t</tr>\n&quot;
while($row = mysql_fetch_array($result)) {
$the_artist = $row[&quot;artist_name&quot;];
$the_month = $row[&quot;month&quot;];
$the_day = $row[&quot;day&quot;];
$the_venue = $row[&quot;venue&quot;];

// shows
echo(&quot;\t<tr>\n\t\t<td>$the_artist</td>\t\t<td>$the_month&quot; . &quot;/&quot; . &quot;$the_day&quot; . &quot;</td>\n&quot;
echo(&quot;\t\t<td>&quot; . &quot;$the_venue&quot; . &quot;</td>\n&quot

 

[r937]

it would be a lot easier to work with if instead of separate fields for month, day, and year, you had used an actual DATETIME field for each date

then you could use the mysql DATE_ADD function
(see

http://www.mysql.com/doc/en/Date_and_time_functions.html)

[tt] WHERE $database_table.datefield
>= CURRENT_DATE
AND $database_table.datefield
<= DATE_ADD(CURRENT_DATE, INTERVAL 30 DAY)[/tt]


is it too late to change your table design?

i mean, you can get the same result using separate day, month, and year fields, but you would first have to assemble them into a datetime value in order to use DATE_ADD

rudy

http://r937.com

 

[tsp0928]

r937 (TechnicalUser) Nov 29, 2003
is it too late to change your table design?

snip
no, this is brand new and my first effort at creating a mysql database from scratch. I thought my idea was excellent and simple so I went live with it, and then realized today that tomorrow it will still show the November 29th shows. Plus, as the web site grows, the data would be growing quickly....which is why I thought I should try to limit it to either monthly or weekly date ranges. I'll take a look at your recommendations. So much to learn, all the time in the world.

Thanks for your help.