Hey,
I have a script that generates a graphic, (via JpGraph). In my db I have following fields🙁..., depart, arrive,...). I can allready count the number of times a depart is between the beginning and the end of a day, but when a ride runs over several days, the depart isn't enough I should need something like:
"SELECT COUNT( *) FROM rides WHERE ((BETWEEN depart AND ARRIVE) BETWEEN '".date('Y-m-d H:i:s', $start + $i * SAMPLERATE)."' AND '".date('Y-m-d H:i:s', $start + ($i+1) * SAMPLERATE)."')";
This is the source of the page:
DEFINE('NDATAPOINTS',33);
DEFINE('SAMPLERATE',86400);
$start = mktime(0, 0, 0, date("m"), 0, date("Y"));
$end = $start+NDATAPOINTS*SAMPLERATE;
$data = array();
$xdata = array();
for( $i=1; $i < NDATAPOINTS; ++$i ) {
$sql1 = "SELECT COUNT(depart) FROM rides WHERE (depart BETWEEN '".date('Y-m-d H:i:s', $start + $i * SAMPLERATE)."' AND '".date('Y-m-d H:i:s', $start + ($i+1) * SAMPLERATE)."')";
$result1 = mysql_query($sql1) or die ("FOUT:".mysql_error());
$row1 = mysql_fetch_row($result1);
$count = $row1[0];
$data[$i] = $count;
$xdata[$i] = $start + $i * SAMPLERATE;
}
Thx, this goes above my MySQL-skills 🙂
