Pivot dynamic html table using mysql and php

Question

This my current code.

<?php
$query_production ="SELECT uploadedby, uploaddate, count(uploadedby) as items, date_format(uploaddate,'%m-%d-%Y') as date FROM imsexport WHERE uploaddate between '2013-01-01' and '2013-01-03' GROUP BY uploadedby, date ORDER BY uploadedby";
$production = mysql_query($query_production,$prepress) or die (mysql_error());
$row_production = mysql_fetch_assoc($production);
?>

<html>
<head>
<title>Untitled Document</title>
</head>
<body>
<table width="200" border="1">
   <tr>
      <td>uploadedby</td>
      <td>uploaddate</td>
      <td>items</td>
      <td>date</td>
   </tr>
 <?php do {?>
   <tr>
     <td><?php echo $row_production['uploadedby']?></td>
     <td><?php echo $row_production['uploadedby']?></td>
     <td><?php echo $row_production['items']?></td>
     <td><?php echo $row_production['date']?></td>
  </tr>

The resulting display is:

 uploadedby              uploaddate           items   date
Adonis Abiera       2013-01-01 08:03:00     64  01-01-2013
Adonis Abiera       2013-01-02 05:30:00     46  01-02-2013
Aileen Alina        2013-01-02 16:29:00     15  01-02-2013
Aileen Mosende      2013-01-01 07:54:00     98  01-01-2013
Aileen Mosende      2013-01-02 06:00:00     69  01-02-2013
Ailyn Barnum        2013-01-01 09:21:00     84  01-01-2013
Ailyn Barnum        2013-01-02 09:16:00     55  01-02-2013
Alexander Dulay     2013-01-02 08:15:00     64  01-02-2013
Alfredo Santiago    2013-01-01 08:02:00     79  01-01-2013
Alfredo Santiago    2013-01-02 06:06:00     59  01-02-2013
Analyn Reyes        2013-01-01 09:04:00     65  01-01-2013
Analyn Reyes        2013-01-02 09:33:00     51  01-02-2013
Andresito Muzones   2013-01-01 08:37:00     60  01-01-2013
Andresito Muzones   2013-01-02 08:45:00     72  01-02-2013
Angelica Domingo    2013-01-01 09:28:00     68  01-01-2013
Angelica Domingo    2013-01-02 09:40:00     59  01-02-2013
Arman Romanca       2013-01-01 09:23:00     73  01-01-2013
Arman Romanca       2013-01-02 10:14:00     64  01-02-2013
Don Feliciano       2013-01-01 09:13:00     70  01-01-2013
Don Feliciano       2013-01-02 06:11:00     56  01-02-2013

I want to display it like this:

uploadedby         01-01-2013    01-02-2013
Adonis Abiera         64            46
Aileen Alina           0            15
Aileen Mosende        98            69
Ailyn Barnum          84            55
Alexander Dulay        0            64
Alfredo Santiago      79            59
Analyn Reyes          65            51
...... and so on

Note that there are certain records that may not exist for certain dates. ex(records for Aileen Alina and Alexander Dulay). Any ideas on how I could accomplish this?

Taryn · Accepted Answer · 2013-01-24 12:28:44Z

If you wanted to perform this in MySQL, then you would pivot the data using an aggregate function with a CASE expression:

select uploadedby,
  count(case 
        when date_format(uploaddate,'%m-%d-%Y') = '01-01-2013'
        then uploadedby end) as `01-01-2013`,
  count(case 
        when date_format(uploaddate,'%m-%d-%Y') = '01-02-2013'
        then uploadedby end) as `01-02-2013`        
from imsexport
where uploaddate between '2013-01-01' and '2013-01-03'
group by uploadedby

Or if you do not want to repeat the date_format() then you can use a subquery:

select uploadedby,
  count(case when date = '01-01-2013' then uploadedby end) as `01-01-2013`,
  count(case when date = '01-02-2013' then uploadedby end) as `01-02-2013`        
from
(
  select uploadedby, date_format(uploaddate,'%m-%d-%Y') date
  from imsexport
  where uploaddate between '2013-01-01' and '2013-01-03'
) src
group by uploadedby

If you need to do this dynamically, then you could use a prepared statement in MySQL. Which will get the list of dates that need to be columns:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when date = ''',
      date_format(uploaddate,'%m-%d-%Y'),
      ''' then uploadedby end) AS `',
      date_format(uploaddate,'%m-%d-%Y'), '`'
    )
  ) INTO @sql
FROM imsexport
where uploaddate between '2013-01-01' and '2013-01-03';

SET @sql = CONCAT('SELECT uploadedby, ', @sql, ' 
                  FROM
                  ( 
                    select uploadedby, date_format(uploaddate,''%m-%d-%Y'') date
                    from imsexport
                    where uploaddate between ''2013-01-01'' and ''2013-01-03''
                  ) src
                  GROUP BY uploadedby');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

If you are worried that you might be missing dates, then I would suggest creating a calendar table that you would get the list of dates from and then you would join the calendar table to your imsexport table to make sure that you would have a list of all dates even if there were no uploads for the day.

Thank you for the reply. I tried that too but encountered several problems. 1) The dates are dynamic and ranges upto two years, I can't figure out how to limit it to a specific date range. 2) How do I display that in HTML? 3) What happens if a specific date does not have an uploadedby count value?

Collectives™ on Stack Overflow

Pivot dynamic html table using mysql and php

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related