CODEWITHSUNDEEP
phpmysql
user470152
user470152

Reputation: 11

Display MYSQL query result in horizontal view

We have employee attendance system in mysql database but it dont have the reporting capability, i am trying to generate reports using PHP.

Let me explain clearly :

employee punch in and punch out daily. These punch ins and outs are stored in mysql database table ( attendance). This table contain 5 fields like punch in, punchout, employeeid, note, attendanceid. Our employee names and other details are stored in other table ( employee).

My query is that I want to generate a report using these two tables in horizontal view.

example :

column1 : employee name column2 : punchin column3 : punchout column4 : punchin column : punchout

I am able to generate daily report for all employees but looking to generate weekly/monthly report.

pls help.

thanks, raj

Upvotes: 1

Views: 1884

Answers (2)

Explosion Pills
Explosion Pills

Reputation: 191729

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 :)

Upvotes: 1

Fredrik
Fredrik

Reputation: 98

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?

Upvotes: 0

Related Questions