Repeatable Query

[mramonster]

I am putting together a list of events that happen the same day each week. The the State page I am trying to list them so that the current day of events is on top and then it goes through the rest in order. Such as Today it is Tuesday and in Texas the events would be:

Texas Events
Tuesday Events
Event1

Wednesday Events
Event2
Event3
Event4

Thursday Events....

and so on

In the database the events are kept in day as the full day name. The easiest way I can think of how to do this is to make this query and list into a function and repeat it 6 more times for each day afterwards.

How would you make this query into a fuction:

// Make a MySQL Connection
include 'include/database.php';

//Set the Date Format
$today = date('Y-m-d');
$today_D = date('l');

// Define your colors for the alternating rows
$color2 = "#52B2F9"; 
$color1 = "#76C2FA"; 
$row_count = 0;

//Query the Events
$query  = "(SELECT DATE_FORMAT(start_date, '%Y-%m-%d'), DATE_FORMAT(end_date, '%Y-%m-%d'), day, 
			title, city, state, country, url, DATE_FORMAT(added, '%Y-%m-%d') 
				FROM table
					WHERE end_date >= '$today' AND state = '$get_state2') 
    						ORDER BY day, city";

$result = mysql_query($query) or die('Error, Bike Night Query Failed.  Report to Administrator.');

// print the events
$display_day = ''; 
while(list($start_date, $end_date, $day, $title, $city, $state, $country, $url, $added) = mysql_fetch_array($result))
{ 
include 'include/state-translation/array.php'; 
$title3 = strtolower($title);
$state_name2 = str_replace("_", " ", "$state");
$state_name3 = strtolower($state_name2);
$country3 = strtolower($country);
$city2 = str_replace(" ", "_", "$city");
$row_color = ($row_count % 2) ? $color1 : $color2;
if ($day != $display_day) {echo '
<tr>
	<td colspan="4"><h2>'.$day.' '.$state_name2.' Query</h2></td>
</tr>
';}
	echo '<tr>
	<td width="75%"  bgcolor="'.$row_color.'"><b>';
	if ( $added <= $recent_event ) {
		$e_title_length=55;
	} else {
		echo '<span class="white"><i>NEW!</i></span> ';
		$e_title_length=55;
	}
	echo '<a href="'.$event_page.'/'.$url.'-'.$city2.'-'.$state.'.html" title="'.$title3.'">';
	if(strlen($title)>$e_title_length){
		echo substr($title, 0, $e_title_length) . '…';
		}
		else {
		echo $title;
		}
	echo '</a></b></td>
	<td width="20%"  bgcolor="'.$row_color.'">'.$city.'</td>
	<td width="5%"  bgcolor="'.$row_color.'">'.$state_abbr.'</td>
</tr>';
$row_count++;
}
mysql_close();

Basically you just need to change the '$today' each time, which is easy enough.

[laserlight]

You can do it with one query, by sorting according to the day name. When retrieving the results, you start a new "block" everytime the current day name is different from the previous.

If you want to run the same query 7 times instead, then you are better off switching to the MySQLi or PDO extension and using prepared statements instead. That way you just reset the statement and bind the value of the next day name instead of sending the query to be compiled from scratch.

[mramonster]

If I order it by day it puts it in alphabetical order.

[laserlight]

mramonster wrote:

If I order it by day it puts it in alphabetical order.

That is true. There should be a way for you to specify a custom ordering in the query itself, but another way would be to create another table to map day names to day numbers (it really should have been the opposite, but oh well). You would then join with this table and order by the day numbers instead of the day names.

[mramonster]

Well the database itself isn't populated yet. I can go ahead and change the days to numbers then.

[Weedpacket]

MySQL does provide an ENUM type which effectively turns the day name/day number mapping into another type. I've never used it (not using MySQL), but I guess this would be a use for it.