ansaurus

Question

Display MYSQL query result in horizontal view

Answer 1

A:

The report format is too vague. Should it be:

one row per user followed by all punchins and punchouts as a single row (not nice).

or

should the punchins/outs be aggregated to give a grand total of worked time per user over the period?

Fredrik 2010-10-08 11:26:26

Answer 2

A:

It sounds like you are trying to do a pivot query (switch rows and columns). MySQL has no support for this, but it can be simulated. It is much easier to just get the data you need and parse it with php, however.

It sounds like (and this is just a guess) you want the employee to be the first column and all punch ins/outs to be in the same row for that employee.

Here's a crack at what you want to do:

$data = select_all_relevant_data_with_mysql();
$employees = array();
while ($row = $data->fetch()) {
   $employees[$row['employee']][] = array(
      'in' => $row['punchin']
      , 'out' => $row['punchout']
   );
}

(Note this will generate undefined index notices for each Employee entry. You should handle this, but it would double the code there).

Now you have the punchin/out data arranged per employee (hopefully in order, MySQL can handle that easily with ORDER BY).

Now you do something link:

foreach ($employees as $name => $punches) {
   echo <<<HTML
      <tr><td>$name</td>
HTML;
   foreach ($punches as $punch) {
      echo <<<HTML
         <td>$punch[in]</td><td>$punch[out]</td>
HTML;
   }
   echo '</tr>';
}

I would suggest using a templating system like PHPTAL for this kind of thing. Shameless plug :)

tandu 2010-10-09 02:32:31

ansaurus

tags:

views:

answers:

Display MYSQL query result in horizontal view

related questions