How to detect if date changes in select query [duplicate]

Question

I'm using the following SQL query to select rows from through an inner join sorted by the date.

SELECT    *
FROM      clubnights 
INNER     JOIN club ON clubnights.club = club.club_ID 
WHERE     visibility = 0
AND       date >= CURDATE()
ORDER BY  clubnights.date ASC

I then load all the data in my HTML via a PHP while loop.

My aim is to echo an HTML header above every section of any given date.

Therefore I am looking to detect (either via PHP or SQL) when a change in date is detected via the select query. Basically, I want to compare the last 'date' column to the current one, and if they do not match I will echo something via PHP.

An example, assuming the following are rows:

2016-09-16 - Row 1 // Detect 'change' here as it's the first row

2016-09-16 - Row 2

2016-09-16 - Row 3

2016-09-16 - Row 4

2016-09-16 - Row 5

2016-09-17 - Row 6 // Detect change here as date has changed

2016-09-17 - Row 7

2016-09-17 - Row 8

Edit: I am using MySQL

Answer 1

If I understand your requirement, then for groups of records sharing the same date, the record with the smallest club_ID is the first record in that group. In the answer below, I use a subquery to find the minimum club_ID for each date. This subquery is then joined to your original query to determine which record is the first, for each date.

SELECT clubnights.*,
       club.*,
       CASE WHEN t.club_ID IS NOT NULL THEN 'change' END AS date_changed
FROM clubnights
INNER JOIN club
    ON clubnights.club = club.club_ID
LEFT JOIN
(
    SELECT MIN(club.club_ID) AS club_ID,
           clubnights.date AS date
    FROM clubnights
    INNER JOIN club
        ON clubnights.club = club.club_ID
    GROUP BY clubnights.date
) t
    ON clubnights.date = t.date AND
       club.club_ID    = t.club_ID

Note that I prefer this solution to handling the problem in the PHP presentation layer, because MySQL was designed to manipulate data efficiently, PHP much less so.