如何使用php mysql创建Crosstab表 [英] How do I create a Crosstab table with php mysql
本文介绍了如何使用php mysql创建Crosstab表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有以下记录集:
Date |Role |Name
=============================
01/02/14 |Musician |Bob
01/02/14 |Leader |Jerry
01/02/14 |Singer |Carol
08/02/14 |Musician |Charles
08/02/14 |Leader |Baz
08/02/14 |Singer |Norman
，我希望数据以轮换/名册格式显示. EG.
and I want the data to be displayed like a rota/roster format. EG.
Role |01/02/14 |08/02/14
===============================
Musician |Bob |Charles
Leader |Jerry |Baz
Singer |Carol |Norman
理想情况下，我希望它可以在php中完成而无需更改MySQL查询.
Ideally, I'd like it to be done in php without changing the MySQL query.
这是我到目前为止所获得的，但是还不能正常工作.
This is what I've got so far but it's not quite working.
$temprole='norole';
$newkey=0;
echo "<table><tr>";
foreach ($result as $key => $val) {
echo "<td>" . $val['date'] . "</td>";
if ($temprole==$val['role_name']){ //is the same role?
} else {
//If the role name is different, print the role column
echo $val['role_name'] . "</br>";
}
$temprole = $val['role_name'];
}
echo "</tr></table>";
echo "<hr>";
推荐答案
天哪，这很有趣...:-/
Goodness, that was fun... :-/
这是经过测试的代码，可按要求执行.有很多评论.随意删除它们以更清楚地查看代码.随便...
This is tested code that does as required. There are lots of comments. Feel free to remove them to see the code more clearly. Whatever...
您应该能够更改$ allRoles数组以使角色以不同的顺序打印.我已经尝试过了，效果很好.
You should be able to change the $allRoles array to get the roles to print in a different order. I have tried it and it works fine.
它在Windows XP(XAMPP)的PHP 5.3.18上运行.
It runs on PHP 5.3.18 on windows XP (XAMPP).
添加了一些CSS，以使表更清晰.
Added some css to make the table clearer.
更改了代码以从"mysqli"查询而不是数组读取数据
Changed the code to read the data from a 'mysqli' query rather than an array
请查看标有！important"的行，以确保其正常工作.
see the lines marked '!important' to ensure it works correctly.
示例输出:
Roles 01/02/14 05/02/14 08/02/14
musician Bob Donald Charles
leader Jerry -- Baz
singer Carol Freddy Norman
代码:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Q2220229 - Pivot table</title>
<style>
td {
border-bottom: 1px solid grey;
width: 10em;
}
</style>
</head>
<body>
<?php
/*
* Some test data base on:
* Date |Role |Name
=============================
01/02/14 |Musician |Bob
01/02/14 |Leader |Jerry
01/02/14 |Singer |Carol
08/02/14 |Musician |Charles
08/02/14 |Leader |Baz
08/02/14 |Singer |Norman
*
*/
/* sample output:
*
* Role |01/02/14 |08/02/14
===============================
Musician |Bob |Charles
Leader |Jerry |Baz
Singer |Carol |Norman
*/
$db = mysqli_connect('localhost', 'test', 'test', 'testmysql');
// 1) Must return three columns only.
// 2) Can return any number of 'roles' - one per row
// 3) Any date range but beware you may need a wide page!
// 4) Must sort by date!
$query = mysqli_query($db, "SELECT service_date, role, member FROM role_by_date ORDER BY service_date ASC, role ASC");
// i prefer to used named subscripts to make the code easier to read.
// These MUST match up with column alias from the above query!
define('THE_DATE', 'service_date'); // !important
define('ROLE', 'role'); // !imortant
define('MEMBER', 'member'); // !important
/*
* Now, we need a complete array of Roles in the order that they are to be displayed.
*
* These names must match with the names of the roles in the input data.
* They will be printed out in the order that they appear in the array.
*
* These are the only roles that will appear in the $outputDates array.
* Add more and in any order to control which 'roles' are shown.
*
*/
$allRoles = array('musician', 'leader', 'singer'); // !important
/*
* At some point we will need an output array that we can easily traverse and
* print out as a row of dates. i.e. a 'page' of data.
*
* We will build it up as we go along...
*/
$outputDates = array(); // !important -- this is the 'pivoted' output array
/*
* Start to process the input data.
*
* To make my life easier, i will use the 'read ahead' technique to simplify the code.
*/
$currentInputRow = mysqli_fetch_array($query);
while (isset($currentInputRow[THE_DATE])) { // process all the input array...
// must be a new day...
$currentDay = $currentInputRow[THE_DATE];
// create an array to hold ALL the possible roles for this day...
$theDayRoles = array();
// initialise the array with default values for all the requested roles.
foreach ($allRoles as $role) {
$theDayRoles[$role] = '--';
}
// now we need to fill theDayRoles with what we actually have for the current day...
while ($currentInputRow[THE_DATE] == $currentDay) { // loop around all records for the current day
// set the appropiate DayRole to the current MEMBER
$theDayRoles[$currentInputRow[ROLE]] = $currentInputRow[MEMBER];
// read the next input row - may be current day, new day or no more
$currentInputRow = mysqli_fetch_array($query);
}
// end of day on the input for whatever reason...
/* we now have:
* 1) Current Date
*
* 2) an array of members for ALL the roles on that day.
*
* We need to output it to another array ($outputDates) where we can print it out
* by scanning the array line by line later.
*
* I will 'pivot' the array and produce an output array we can scan sequentially later.
*/
// to ensure that we are updating the correct $outputDates row i will use a subscript
$currentOutputRowIdx = 0;
// first add the current date to the output...
$outputDates[$currentOutputRowIdx][] = $currentDay;
$currentOutputRowIdx++; // next output row
// we need to drive off the '$allRoles' array to add the role data in the correct order
foreach ($allRoles as $outRole) {
$outputDates[$currentOutputRowIdx][] = $theDayRoles[$outRole];
$currentOutputRowIdx++; // next output row
}
} // end of all the input data
/*
* Now we just need to print the outputDates array one row at a time...
*/
// need the roles as the first column...
// so we need an index for which one we are currently printing
$currentRoleIdx = -1; // increment each time but allow for the first row being the title 'Roles'
echo '<table>';
foreach ($outputDates as $oneOutputRow) {
echo '<tr>';
// this is the first column...
if ($currentRoleIdx < 0) {
echo '<td>'. 'Roles' .'</td>';
}
else {
echo '<td>'. $allRoles[$currentRoleIdx] .'</td>';
}
// now output the day info
foreach($oneOutputRow as $column) {
echo '<td>'. $column .'</td>';
}
echo '</tr>';
$currentRoleIdx++; // next output Role to show...
}
echo '</table>';
?>
</body>
</html>
这篇关于如何使用php mysql创建Crosstab表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
