MySQL table output

An interesting task. In short, there is a (TABLE log)log like this:

id |NAME |    DATE    | PROFIT
---+-----+------------+-------   
 1 | BEN |'2011-01-01'|   3
 2 | ANN |'2011-01-01'|   1
 3 | JOY |'2011-01-05'|   7
 4 | BEN |'2011-01-03'|   2
....
 ? | PAUL|'2011-12-31'|   5

BEN, ANNetc. it’s clerks. DATEand PROFITare the profit they made that day. Important! All fields are not NULL. idPRIMARY KEY AUTO_INCREMENT.

Let’s say Uncle PETYAcame and said “I want a report from 2011-01-03to 2011-01-10“.
Something like this:

   |'2011-01-01'|'2011-01-02'|'2011-01-03'|...|'2011-01-10'
----+------------+------------+------------+---+------------
 ANN|      1     |    NULL    |      6     |...|    NULL 
 BEN|      3     |      2     |    NULL    |...|      9 
....
 TOM|    NULL    |    NULL    |      5     |...|      4

I.e. it lists all the clerks without repetition and exactly all the dates the CHEF asked for. It is clear that if it was not possible to make money on this day, then in the report NULL(either empty or &nbsp).

Now the question is how to display this report in a table using PHP. The names of all employees are known in advance, and the dates can always be added to the end of the log. Please focus on PHP(up to echo "<tr></tr>"))), because the 3rd day I can no longer figure out the output functions mysql_fetch_arrayand mysql_result.

P.S. I came up with the task myself.


Answer 1, authority 100%

Well, everything is simple here. First of all, we form an array of dates.

$from = '2011-01-03';
$to = '2011-01-10';
if ($from > $to) {
    $tmp = $from; $from = $to; $to = $tmp;
}
$st = strtotime($from . ' 12:00:00');
$dates = array();
while (true) {
    $cd = date('Y-m-d', $st);
    $dates[] = $cd;
    if ($cd >= $to) {
        break;
    }
    $st = strtotime('+1 day', $st);
}
print_r($dates);

This will output:

Array
(
[0] => 2011-01-03
[1] => 2011-01-04
[2] => 2011-01-05
[3] => 2011-01-06
[4] => 2011-01-07
[5] => 2011-01-08
[6] => 2011-01-09
[7] => 2011-01-10
)

It is important that the dates in that place fall exactly in the format indicated.

Next, we collect information from the database:

$data = array();
$res = query('select * from log where `DATE` >= '.q($from).' and `DATE` <= '.q($to));
while ($r = fetch($res)) {
    $data[$r['NAME']][$r['DATE']] = $r['PROFIT'];
}
free($res);

query, fetch and q functions are used here

According to the first two, I think you can guess, but q is a string quoting. In the simplest case, it will look like this:

funciton q($str)
{
    return "'".mysql_real_escape_string($str)."'";
}

Let’s move on. We display everything that we have collected here.

$t = '<table>';
foreach ($data as $name => $d) {
    $t .= '<tr>';
    $t .= '<th>'.$name.'</th>';
    foreach ($dates as $date) {
        $t .= '<td>'.(isset($d[$date]) ? $d[$date] : 'NULL').'</td>';
    }
}
$t .= '</table>';
echo $t;

That’s it…

PS: I didn’t check it, but in theory it should turn out what you wanted 🙂


Answer 2, authority 100%

The easiest thing you can do is create a 2D array. In the rows – employees, in the columns – dates. Walk through the data set and for each source line find the corresponding array cell and add the PROFIT value to it.

The variant is more complicated (although it doesn’t matter to anyone) – knowing the date range, form an SQL query that will return a ready-made crosstab in the desired form. This is done approximately as discussed here. Only there a section was taken by type of work, and you will have by date.