How to determine if a date has changed in a selected query

Question

I use the following SQL query to select rows from an inner join sorted by date.

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

Then I load all the data into my HTML through the PHP while loop.

My goal is an HTML echo header over every section of any date.

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

As an example, suppose the following lines:

 2016-09-16 - Row 1 // Detect 'change' here as it 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 that have the same date, the record with the lowest club_ID is the first record in this group. In the answer below, I use a subquery to find the minimum club_ID for each date. This subquery is then connected 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 

Please note that I prefer this solution to handle the problem at the presentation level of PHP, because MySQL was designed for efficient data management, PHP is much smaller.

Answer 2

 SELECT *, CASE WHEN @dd!=date THEN 1 ELSE 0 END as changed, @dd:=date FROM clubnights, (select @dd:=null) INNER JOIN club ON clubnights.club = club.club_ID WHERE visibility = 0 AND date >= CURDATE() ORDER BY clubnights.date ASC 

You can define a variable and change it from row to row compared to the current date

Answer 3

well, local variables will help me. Use the following method!

When you display data in tables using php loop, use the following algorithm

* I assume that the database is connected.

 <?php $date2 = sortotime("today"); $q = mysql_query("select your_date_column_name as dt from your_table_name"); while($obj = mysql_fetch_object($q)) { $date1 = $obj->dt; if(strtotime($date1) != strtotime($date2)) { echo "Row Changed"; echo"<br/>"; $date2 = $date1; } echo $date1; 

Hope this helps :)

Answer 4

You can select data from mysql, sorted by date in ascending order, and then compare it with php code, Example:

 $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "myDB"; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); // Check connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "same as your query"; $result = $conn->query($sql); $previousDate = DateTime::createFromFormat('Ym-d', '1990-01-01'); if ($result->num_rows > 0) { // output data of each row while($row = $result->fetch_assoc()) { $mysqlDate= date('Ym-d', $row["date"]); if($previousDate != $mysqlDate) { echo "two different dates"; $previousDate = $mysqlDate; } } } else { echo "0 results"; } $conn->close();