使用mysql和php支持动态html表 [英] Pivot dynamic html table using mysql and php
本文介绍了使用mysql和php支持动态html表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是我目前的代码。
<？php
$ query_production =SELECT uploadby，uploaddate，count （uploadby）作为项目，date_format（uploaddate，'％m-％d-％Y'）作为日期FROM imsexport WHERE uploaddate'2013-01-01'和'2013-01-03'GROUP BY uploadedby，date ORDER BY uploadedby;
$ production = mysql_query（$ query_production，$ prepress）或die（mysql_error（））;
$ row_production = mysql_fetch_assoc（$ production）;
？>
< html>
< head>
< title>无标题文档< / title>
< / head>
< body>
< table width =200border =1>
< tr>
< td>已上传< / td>
< td> uploaddate< / td>
< td>项目< / td>
< td>日期< / 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>
结果显示为：
uploadedby uploaddate项目日期
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
我想显示它是这样的：
uploadedby 01-01-2013 01-02-2013
Adonis Abiera 64 46
Aileen Alina 0 15
Aileen Mosende 98 69
Ailyn Barnum 84 55
亚历山大杜莱0 64
阿尔弗雷多圣地亚哥79 59
分析雷耶斯65 51
。 .....等等
请注意，某些日期可能不存在的某些记录。 ex（Aileen Alina和Alexander Dulay的唱片）。关于如何实现这一点的任何想法？ 解决方案
如果您想在MySQL中执行此操作， 使用带有 CASE 表达式的聚合函数的数据：
select uploadedby，
count（case date
when date_format（uploaddate，'％m-％d-％Y'）= '01 -01-2013'
then uploadedby end）as` 01-01-2013`，
count（case date
when date_format（uploaddate，'％m-％d-％Y'）= '01 -02-2013'
然后通过结束上传） as'01-02-2013`
from imsexport
上传日期'2013-01-01'和'2013-01-03'之间的上传日期
上传者群
或者如果您不想重复 date_format（），那么您可以使用子查询：
select uploadedby，
count（case when date = '01 -01-2013'then then uploadedby结束）为`01-01-2013`，
计数（日期= '01 -02-2013'时的情况'然后上传到最后）作为`01-02-2013`
从
（
选择uploadedby，date_format（uploaddate，'％m-％d-％Y'）日期
从imsexport
上传日期'2013-01-01'和'2013-01-03'
）src
group by uploadedby
如果您需要动态执行此操作，则可以在MySQL中使用预准备语句。这将获得需要列的日期列表：
SET @sql = NULL;
SELECT
GROUP_CONCAT（DISTINCT
CONCAT（
'count（case when date ='''，
date_format（uploaddate，'％m-％d-％Y '），然后上传
'''结束）as``，
date_format（uploaddate，'％m-％d-％Y'），'`'
）
）INTO @sql
FROM imsexport
其中uploaddate在'2013-01-01'和'2013-01-03'之间;
SET @sql = CONCAT（'SELECT uploadedby，'，@sql，'
FROM
（
select uploadedby，date_format（uploaddate，''％m- ％d-％Y）日期
from imsexport
其中uploadUp在''2013-01-01''和''2013-01-03''
之间）src
GROUP BY uploadedby'）;
从@sql中准备stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请参阅 SQL小提琴演示
如果您担心自己可能缺少日期，那么我会建议创建一个日历表，你会得到日期列表，然后你将日历表加入到你的 imsexport 表中，以确保你有一个所有日期的列表，即使有当天没有上传。
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?
解决方案
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;
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.
这篇关于使用mysql和php支持动态html表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
