[RESOLVED] SELECT list of consecutative dates from MySQL

[jme]

Hi all, I've been working on this for a while now without any success.

Basically I have a table which contains class register information and I want to select all consecutive dates from a given date.

Eg. Based on the below table, if the query passed 2010-01-01 it would return 2010-01-02 -> 2010-01-04, if it was passed 2010-01-03 it would return 2010-01-04.

Table Structure

Date              Event             ....... other fields 
2010-01-01        Some event
2010-01-02        Some event
2010-01-03        Some event
2010-01-04        Some event
2010-01-21        Some event
2010-02-11        Some event
2010-02-19        Some event
2010-02-20        Some event

Any advice would be much appreciated.

Cheers.

[sneakyimp]

You need to clarify what you mean by 'consecutive dates'. I don't really understand why you'd get 3 dates in the first case and only one in the second.

[jme]

In the first instance there's three consecutive dates following it before there's a break of dates (ie, there's a gap in the dates until the next record in the db). In the second there's only one consecutive date following it before there's a break in the date.

[sneakyimp]

I still don't see these gaps you are talking about. You need to clarify the 'gap' concept. Do you mean that the month changes?

[jme]

The date changes.

In the example table, there's 4 consecutive days (01 -> 04 of Jan) then there's a 'gap' in dates until the next date which is the 21-01 which is a single date.

I'm attempting (and don't know if it's possible) to select only those dates when there is a 'group' of dates with no day gaps inbetween them.

Cheers,

Jamie

[bradgrafelman]

I don't know how you'd do it in SQL (unless perhaps you created a stored procedure), but I could certainly do it in PHP.

When you're looping through the results, check to see if the date you retrieved is the previous date plus one day. Quick example:

$first = true;
$lastDate = '';

while($row = mysql_fetch_assoc($exec)) {
  if($first || strtotime($row['dateCol']) == strtotime("$lastDate +1 day")) {
    // process row here
  } else break;

  $first = false;
  $lastDate = $row['dateCol']
}

[sneakyimp]

aha....you mean a block of consecutive days with no days in between. i thought this might be possible using a query with a join or two. but haven't been able to sort it out. The logic is pretty convoluted.

[sneakyimp]

I think I had an idea. Join the table to itself twice based on the date column.

SELECT t1.* FROM my_table t1
LEFT JOIN my_table t2 ON (t2.date_col = t1.date_col + INTERVAL 1 DAY)
LEFT JOIN my_table t3 ON (t3.date_col = t3.date_col - INTERVAL 1 DAY)
WHERE 
  t1.date_col >= '2010-01-01'
  AND t2.date_col IS NOT NULL
  AND t3.date_col IS NOT NULL OR t1.date_col = '2010-01-01'

I don't really know if that'll work properly and, to be honest I don't think it's worth it to have that confusing query. The nature of the query is such that it won't really use the date indexes in your table all that much.

EDIT: had error in the query

[jme]

Thanks for your comments and thoughts. I'll try both of these out tomorrow when I'm next in the office and report back.

[zypher11]

Give this a shot:

$submittedDate = "2010-04-29"; //date user submitted
$continue = true;
$sql = "SELECT * FROM table WHERE date = '$submittedDate'";
while($continue == true){
	$submittedDate = date("Y-m-d", strtotime($submittedDate . "+1 day"));
	$check = "SELECT id FROM table WHERE date = '$submittedDate'";
	$check_result = mysql_query($check);
	$numrows = mysql_num_rows($check_result);
	if($numrows > 0)
		$sql .= " OR date = '$submittedDate'";
	else
		$continue = false;
}
$sql .= " ORDER BY date";
$result = mysql_query($sql);
while($row = mysql_fetch_assoc($result)){
	//display returned data
}

Its sending a lot of queries to check if the next day exists but it's the only way i can think off right off hand.

[jme]

I ended up going with zypher11's code and writing it into a function. I hacked the SQL a bit with some where clauses which has greatly reduced the number of queries.

Thanks for your help guys. 🙂

[forsooth]

JME --

Could you post your final code? Would be interesting to see.

Thx

[jme]

Hi forsooth, here is the final code that I used. I adapted the queries a little to reduce the load on the db. Also, added the checking for weekends as I didn't want it to return FALSE if the day was a weekend only week day.

As a side note - this is part of a codeignitor development I'm working on - thus the $this->db type calls.

	// Gets consecutative dates for drop down box. -- need to work on weekends. 
	// -- needs rewriting to activerecord queries
	function get_date_block($sid, $start_date) {

	$continue = TRUE; 
	// Start final SQL query 
	$sql = "SELECT DISTINCT AttDateTime FROM oasys_vw_master WHERE StuRef = '$sid' AND (AttDateTime = '$start_date'"; 

	// Work out other dates needed 
	while ($continue == TRUE) { 
		$start_date = date("Y-m-d", strtotime($start_date . "+1 day")); 
		// Needs rewriting as ActiveRecord
		$query = $this->db->query("SELECT StuRef FROM oasys_vw_master WHERE StuRef = '$sid' AND AttMark IN ('O', 'L', 'B', 'R') AND AttDateTime = '$start_date'"); 

		if ($query->num_rows() > 0) {
	        $sql .= " OR AttDateTime = '$start_date'"; 		
	    } else {
			// If day is Saturday or Sunday don't stop script. 
			if (date('N', strtotime($start_date)) < '5') {
				$continue = FALSE;
			} 
		}	
	} 

	// End final query SQL 
	$sql .= ") ORDER BY AttDateTime"; 

	// Now query database to select dates for drop down box
	$query = $this->db->query($sql);		
	return $query->result(); 

}

[forsooth]

Thank you for posting this. Very helpful thread!