You should definitely change the date_format part of your query. I'd replace '%d %b %Y' with '%e/%c/%Y'.
The other part is tricky. To output HTML, you have to loop through your columns for the first row, then your columns for the second row, etc. Since you don't know if your query will return a value for every column for a given row, you are going to need to loop thru your results and build some data structures that are more suitable for outputting this stuff. I'd try something like this (note I've changed your query):
$query="SELECT u.staff_no, date_format(w.FollowUp_date, '%e/%c/%Y' ) as DATE_FOLLOW_UP , count(w.QSTATUS) as TCase
FROM wocreports w, users u WHERE ((w.FollowUp_by=u.staff_no)) GROUP BY DATE_FOLLOW_UP ORDER BY w.FollowUp_date ASC"
$result = mysql_query($query)
or die('The query failed!');
// this code creates a 2-d array called $data from your query results
$dates_seen = array(); // to remember *all* the dates we've encountered
$staff_nos_seen = array(); // to remember *all* the staff_nos we've encountered
$data = array();
while ($row = mysql_fetch_assoc($result)) {
if (!in_array($row['DATE_FOLLOW_UP'], $dates_seen)) {
$dates_seen[] = $row['DATE_FOLLOW_UP'];
}
if (!in_array($row['staff_no'], $staff_nos_seen)) {
$staff_nos_seen[] = $row['staff_no'];
}
if (!is_array($data[$row['staff_no']])) {
$data[$row['staff_no']] = array();
}
$data[$row['staff_no']][$row['DATE_FOLLOW_UP']] = $row['TCase'];
}
// now loop thru all the dates and staff_no values to create your table
$output = '<table>';
$output .= '<tr>';
// the empty cell over the staff_no column
$output .= '<td></td>';
// make the header row from the dates we've seen
foreach($dates_seen as $date) {
$output .= '<td>' . $date . '</td>';
}
// end of the header row
$output .= '</tr>';
// loop thru $data by staff no for rows then $dates for columns
// set the array pointer to the first element
reset($staff_nos_seen);
foreach($staff_nos_seen as $staff_no) {
$output .= '<tr>';
// the first column with staff no
$output .= '<td>' . $staff_no . '</td>';
// the other columns come from $data...reset $dates_seen first
reset($dates_seen);
foreach ($dates_seen as $date) {
$t_cap = $data[$staff_no][$date];
// if the value is empty, use - instead
$t_cap = ($t_cap) ? $t_cap : '-';
$output .= '<td>' . $t_cap . '</td>';
}
// end of the row for this staff_no
$output .= '</tr>';
}
// end of the table
$output .= '</table>';
echo $output;
I haven't tested that...it may have bugs but you should get the idea. There's probably a more efficient way to do it but what do you want for free?
