An interesting challenge. In short, there is a log (TABLE log) form:

 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, ANN , etc. these are clerks. DATE and PROFIT - the profit they brought on that day. Important! All fields are not NULL . id - PRIMARY KEY AUTO_INCREMENT .

Suppose uncle PETYA came and said: “I want a report from 2011-01-03 to 2011-01-10 ”. Approximately this type:

  |'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

Those. here all the clerks are listed without repetition and exactly all the dates requested by the CHEF. Obviously, if you failed to earn that day, then the report is NULL (or empty or   ).

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

PS He invented the task.

    2 answers 2

    Well, everything is simple. First of all we create 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('Ym-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 in exactly the format specified.

    Next, 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);

    functions query, fetch and q are used here

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

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

    Let's go further. We deduce everything that we have here collected.

     $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 all ...

    PS: I did not check, but you should get what you wanted :)

    • Thank you very much! It turned out as wanted. True, I had to slightly fix the moment where the clerks get out .. - alex_90

    The simplest thing you can do is create a two-dimensional array. In rows are employees, in date columns. Go through the data set and for each source line find the corresponding cell of the array and add the PROFIT value to it.

    The variant is more difficult (although to whom how) - knowing the range of dates, form a SQL query that will return the finished crosstab in the desired form. This is done approximately as discussed here . Only there was taken a cut by type of work, and you will have dates.