Repeating a Listing by Day

mramonster

I'm trying to figure out a way to list events by date from a MySQL Query by listing the day of the event and the dates it starts and ends.

Such as Event A is every Thursday from March 1 to December 12. and Event B is every Monday from February 2 to April 26.

I'm sure it's possible but that's beyond my current PhP knowledge.

mramonster

Rather than that is it possible to break the events up by Day to where it there's be a header for each day and only days with events.

such as:

Mondays
Event B

Thurdays
Event A

Installer

Something like this:

$query = "SELECT DAY_OF_WEEK(start_date) AS dow, start_date FROM my_table GROUP BY dow";
$result = mysqli_query($db, $query);
$display_dow = '';
while ($row = mysqli_fetch_assoc($result)) {
    if ($row['dow'] != $display_dow) {
        echo '<b>' . $row['dow'] . 's</b><br />';
        $display_dow = $row['dow'];
    }
    echo $row['start_date'] . ' - ' . $row['end_date'] . '<br />';
    }
}

I don't know exactly how the group by will sort. You may need to add an order by (ORDER BY start_date?) clause. And if the database is large it might be quicker to group and sort the results with php.

mramonster

Thanks that helps a lot.

So it'd be faster if the database gets big to query each day?

$query = "SELECT DAY_OF_WEEK(start_date) AS dow, start_date FROM my_table WHERE day='Monday'";

and then continue with the other queries after the echo as?

$query = "SELECT DAY_OF_WEEK(start_date) AS dow, start_date FROM my_table WHERE day='Tuesday'";

Installer

That should work very well, although if you want the events sorted within each day you'd still need to add an order by clause to each query or sort the results of each query with php functions.

What I had more in mind was doing one query, then building an array from the result set with the start and end times in subarrays, then sorting that array (or something along those lines).

So, so far you have three ways to do it. Frankly, I don't know which would be the best for a large array. There may or may not be enough difference in speed to make one any better than the others. I'd first experiment with the method in my first reply and see how it works.

mramonster

Is it at all possible to do something along the lines of:

$today = date('Y-m-d');
$today_D = date('l');

$query  = "(SELECT DAYNAME(start_date) AS dow, DATE_FORMAT(start_date, '%Y-%m-%d'), DATE_FORMAT(end_date, '%Y-%m-%d'), title, city, state, country, url, DATE_FORMAT(added, '%Y-%m-%d') 
			FROM table
				WHERE end_date >= '$today' AND dow = '$today_D'
					GROUP BY state) 
    					ORDER BY state, city";

I get an error with it. But basically I'm trying to select an event that is only occurring today by using the start date as that day.