使用 php 和 mysql 创建出勤数据透视表 [英] Creating Pivot table for attendance using php and mysql
本文介绍了使用 php 和 mysql 创建出勤数据透视表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想使用 phpmysql 制作像 this 这样的出勤视图
I want to make attendance view like this using phpmysql
以下是我的考勤表:
`CREATE TABLE IF NOT EXISTS `attendance` (
`aid` int(11) NOT NULL AUTO_INCREMENT,
`Name_of_Student` varchar(100) COLLATE latin1_general_ci NOT NULL,
`Class` varchar(20) COLLATE latin1_general_ci NOT NULL,
`Roll_no` int(11) NOT NULL,
`Section` varchar(20) COLLATE latin1_general_ci NOT NULL,
`Status` binary(1) NOT NULL,
`time` varchar(20) COLLATE latin1_general_ci NOT NULL,
`Date` date NOT NULL,
PRIMARY KEY (`aid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=33 ;
--
-- Dumping data for table `attendance`
--
INSERT INTO `attendance` (`aid`, `Name_of_Student`, `Class`, `Roll_no`, `Section`, `Status`, `time`, `Date`) VALUES
(1, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-01'),
(2, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-01'),
(3, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-02'),
(4, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-02'),
(5, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-03'),
(6, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-03'),
(7, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-04'),
(8, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-04'),
(9, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-05'),
(10, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-05'),
(11, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-06'),
(12, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-06'),
(13, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-07'),
(14, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-07'),
(15, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-08'),
(16, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-08'),
(17, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-01'),
(18, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-01'),
(19, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-02'),
(20, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-02'),
(21, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-03'),
(22, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-03'),
(23, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-04'),
(24, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-04'),
(25, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-05'),
(26, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-05'),
(27, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-06'),
(28, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-06'),
(29, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-07'),
(30, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-07'),
(31, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-08'),
(32, 'Zonundanga', 'X', 5, 'A', 'N', 'Afternoon', '2013-07-08');`
尝试代码在这里:SQL:
The attempt code is here: SQL:
SELECT * FROM attendance
PHP:
$query = $dbh->query("SELECT * FROM attendance");
$result = $query->fetchAll(PDO::FETCH_OBJ);
$column = [];
foreach($result as $key=>$val){
$column[$val->Name_of_Student][$val->Date][$val->time] = $val->Status;
}
$Status = [];
$hming = [];
?>
<table border="1" align="center" width="100%">
<tr>
<th>Name</th>
<?php foreach($column as $dt=>$vt):
$hming[] = $dt;
foreach($vt as $k=>$v):
$Status[$k] = $v['Morning'];
$Status[$k] = $v['Afternoon'];
?>
<th colspan="2"><?php echo $k; ?></th>
<?php endforeach; endforeach; ?>
</tr>
<?php foreach($hming as $name): ?>
<tr>
<td><?php echo $name; ?></td>
<?php foreach($Status as $time): ?>
<td><?php echo $time; ?></td>
<?php endforeach;?>
</tr>
<?php endforeach;?>
</table>
问题是每个学生的日期都没有循环.
The problem is the date is not looping for each student.
推荐答案
我的问题的更正解决方案在这里:
The corrected solution to my question is here:
$query = $dbh->query("SELECT * FROM attendance");
$result = $query->fetchAll(PDO::FETCH_OBJ);
$column = [];
foreach($result as $key=>$val){
$column[$val->Name_of_Student][$val->Date][$val->time] = $val->Status;
}
$dateat = [];
foreach($column as $studentname=>$hming){
foreach($hming as $dt=>$nihming){
$dateat[$studentname][] = $dt;
}
}
$prevDate = [];
?>
<table border="1" align="center" width="80%" cellpadding="10" cellspacing="4">
<?php
echo "<tr>";
echo "<td>Name</td>";
foreach($dateat as $nk=>$nithar):
foreach($nithar as $atdate):
if (!in_array($atdate, $prevDate)) {
$prevDate[] = $atdate;
echo '<td colspan="2">'.$atdate."</td>\n";
}
endforeach;
endforeach;
echo "</tr>";
foreach($column as $stname=>$stval):
echo "<tr>";
echo "<td>".$stname."</td>\n";
foreach($stval as $sk=>$sv){
foreach($sv as $stt){
echo "<td>".$stt."</td>\n";
}
}
echo "</tr>";
endforeach; ?>
</table>
输出:
<table border="1" align="center" width="80%" cellpadding="10" cellspacing="4">
<tr><td>Name</td><td colspan="2">2013-07-01</td>
<td colspan="2">2013-07-02</td>
<td colspan="2">2013-07-03</td>
<td colspan="2">2013-07-04</td>
<td colspan="2">2013-07-05</td>
<td colspan="2">2013-07-06</td>
<td colspan="2">2013-07-07</td>
<td colspan="2">2013-07-08</td>
</tr><tr><td>Lalchhandami</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
</tr><tr><td>Zonundanga</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>N</td>
</tr></table>这篇关于使用 php 和 mysql 创建出勤数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
