rain rain - 1 month ago 8
HTML Question

Pivot dynamic html table using mysql and php

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?

Answer

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.