[ubuntu-uk] [OT] MySQL Help Required

[Sean Miller]

Surely the key is to dynamically build your SQL statement in the PHP?

In other words supply start date and supply end date.

Then you start your SQL as...

$query  = "SELECT User as name";

...then loop through the dates between start/end dates (putting date
as var $dt), writing a line for each...

$dt = $sdate;
while ( strtotime($dt) <= strtotime($edate) ) {
      $dt_title = date('d/m",strtotime($dt));
      $query .= "SUM(if(date_format(Timestamp,'%Y-%m-%d') = '" . $dt .
"', 1, 0)) as " . $dt_title . ",";
      $dt = date("y-m-d",strtotime("+1 days",$dt));
}

...or similar...  then finish...

$query .= "count(ID) as total ";
$query .= "WHERE Timestamp BETWEEN '" . $sdate . "' AND '" . $edate . "' ";
$query .= "GROUP BY User ";
$query .= "ORDER BY User";

...then execute query.

Syntax may not be exactly correct (haven't tested and just rushing
out) but the concept is there to see.

Or something similar.

If you're using PHP you might as well generate the SQL dynamically.

Alternatively you could possibly write a stored procedure in MySQL.

Sean