ช่วยน่อยครับ คือต้องจะทำผลรวมของแต่ละ คอลัม แต่มันไม่ออกอ่ะคับผม
<?php include ("../../config/conndb_nonsession.inc.php");
//echo "<pre>" ;
//print_r($_POST);
$mmyy= $_POST["year"].'-'.sprintf("%02d",$_POST["m"]);
if ($_POST["year"]==""){
$mmyy= date ("Y-m");
}
else{
$mmyy= $_POST["year"].'-'.sprintf("%02d",$_POST["m"]);
}
//echo $mmyy;
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv=Content-Type content="text/html; charset=tis-620">
<title>สถิติการเข้าใช้งานระบบทะเบียน</title>
</head>
<body>
<form action="?" method="post" name="mmyy">
เดือน
<?php include ("function_m.php");?>
ปี พ.ศ.
<?php include ("function_y.php");?>
<input type="submit" value="ตกลง">
</form>
<table width="1684" border="1">
<tr>
<td width="40" rowspan="3" align="center" bgcolor="#CCCCCC">ลำดับ</td>
<td width="200" rowspan="3" align="center" bgcolor="#CCCCCC">สำนักงานเขตพื้นที่การศึกษา</td>
<td width="100" rowspan="3" align="center" bgcolor="#CCCCCC">จำนวนทั้งหมด</td>
<td colspan="11" align="center" bgcolor="#CCCCCC">สถิติการเข้าใช้งานระบบทะเบียนประวัติเดือน(ครั้ง)</td>
</tr>
<tr>
<td colspan="11" align="center" bgcolor="#CCCCCC">จำนวนครั้งการเข้าใช้งานระบบแยกตามช่วงเวลา</td>
</tr>
<tr>
<td width="108" align="center" bgcolor="#CCCCCC">
09:00 - 09:59</td>
<td width="108" align="center" bgcolor="#CCCCCC">
10:00 - 10:59</td>
<td width="108" align="center" bgcolor="#CCCCCC">
11:00 - 11:59</td>
<td width="108" align="center" bgcolor="#CCCCCC">
12:00 - 12:59</td>
<td width="108" align="center" bgcolor="#CCCCCC">
13:00 - 13:59</td>
<td width="108" align="center" bgcolor="#CCCCCC">
14:00 - 14:59</td>
<td width="108" align="center" bgcolor="#CCCCCC">
15:00 - 15:59</td>
<td width="108" align="center" bgcolor="#CCCCCC">
16:00 - 16:59</td>
<td width="108" align="center" bgcolor="#CCCCCC">
17:00 - 17:59</td>
<td width="108" align="center" bgcolor="#CCCCCC">
18:00 - 18:59</td>
<td width="170" align="center" bgcolor="#CCCCCC">
หลัง 18:00 ถึง ก่อน 09:00</td>
</tr>
<?php
$sql = " SELECT
t2.secname,
sum(if(updatetime LIKE '$mmyy%',1,0)) as numall,
sum(if(hour(updatetime)='09' and updatetime LIKE '$mmyy%',1,0)) as num9,
sum(if(hour(updatetime)='10' and updatetime LIKE '$mmyy%',1,0)) as num10,
sum(if(hour(updatetime)='11' and updatetime LIKE '$mmyy%',1,0)) as num11,
sum(if(hour(updatetime)='12' and updatetime LIKE '$mmyy%',1,0)) as num12,
sum(if(hour(updatetime)='13' and updatetime LIKE '$mmyy%',1,0)) as num13,
sum(if(hour(updatetime)='14' and updatetime LIKE '$mmyy%',1,0)) as num14,
sum(if(hour(updatetime)='15' and updatetime LIKE '$mmyy%',1,0)) as num15,
sum(if(hour(updatetime)='16' and updatetime LIKE '$mmyy%',1,0)) as num16,
sum(if(hour(updatetime)='17' and updatetime LIKE '$mmyy%' ,1,0)) as num17,
sum(if(hour(updatetime)='18' and updatetime LIKE '$mmyy%' ,1,0)) as num18,
sum(if(hour(updatetime) NOT IN('09','10',11,12,13,14,15,16,17,18) and updatetime LIKE '$mmyy%',1,0)) as numother
FROM
eduarea as t2
left join
log_update_all_site_login AS t1 on t2.secid=t1.siteid
where t2.status=1
group by t2.secid
order by t2.orderby asc ";
//echo $sql."<br>";
$result = mysql_query($sql)or die(mysql_error()."$sql");
//echo $result;
//$row = mysql_fetch_array($result);
//echo "<pre>";
//print_r($row);
$i=0;
while ($row = mysql_fetch_assoc($result)) {
$i++;
?>
<tr>
<td><p align="center"><?php echo $i; ?></p></td>
<td><p align="left"><?php echo $row['secname']; ?></p></td>
<td><p align="right"><?php echo $row['numall']; ?></p></td>
<td><p align="right"><?php echo $row['num9']; ?></p></td>
<td><p align="right"><?php echo $row['num10']; ?></p></td>
<td><p align="right"><?php echo $row['num11']; ?></p></td>
<td><p align="right"><?php echo $row['num12']; ?></p></td>
<td><p align="right"><?php echo $row['num13']; ?></p></td>
<td><p align="right"><?php echo $row['num14']; ?></p></td>
<td><p align="right"><?php echo $row['num15']; ?></p></td>
<td><p align="right"><?php echo $row['num16']; ?></p></td>
<td><p align="right"><?php echo $row['num17']; ?></p></td>
<td><p align="right"><?php echo $row['num18']; ?></p></td>
<td><p align="right"><?php echo $row['numother']; ?></p></td>
<td><p align="right"><?php echo $sumall['numall']; ?></p></td>
</tr>
<?php
$sumall += $row['numall'];
$sum9+= $row['num9'];
$sum10+= $row['num10'];
$sum11+= $row['num11'];
$sum12+= $row['num12'];
$sum13+= $row['num13'];
$sum14+= $row['num14'];
$sum15+= $row['num15'];
$sum16+= $row['num16'];
$sum17+= $row['num17'];
$sum18+= $row['num18'];
$sumother+= $row['numother'];
}
?>
<tr>
<td width="245" rowspan="1" colspan="2" align="center" bgcolor="#CCCCCC">รวม</td>
<td width="100" rowspan="1" align="center" bgcolor="#CCCCCC"><?php echo $row['numall']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num9']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num10']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num11']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num12']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num13']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num14']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num15']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num16']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num17']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $row['num18']; ?></td>
<td width="170" align="center" bgcolor="#CCCCCC">
<?php echo $row['numother']; ?></td>
</tr>
</body>
</html>
Tag PHP, MySQL
Date :
2015-07-20 09:42:03By :
simpleza07View :
783Reply :
2
ต้องถามก่อนว่า updatetime type เป็น varchar หรือ datetimeCode (PHP) $sql = " SELECT
t2.secname,
count(*) as numall,
sum(hour(updatetime)=9) as num9,
sum(hour(updatetime)=10) as num10,
sum(hour(updatetime)=11) as num11,
sum(hour(updatetime)=12) as num12,
sum(hour(updatetime)=13) as num13,
sum(hour(updatetime)=14) as num14,
sum(hour(updatetime)=15) as num15,
sum(hour(updatetime)=16) as num16,
sum(hour(updatetime)=17) as num17,
sum(hour(updatetime)=18) as num18,
sum(hour(updatetime)<9 or hour(updatetime)>18) as numother
FROM eduarea as t2
LEFT JOIN log_update_all_site_login AS t1
on t2.secid=t1.siteid
where t2.status=1 and updatetime LIKE '$mmyy%'
group by t2.secid
order by t2.orderby";
Code <tr>
<td width="245" rowspan="1" colspan="2" align="center" bgcolor="#CCCCCC">รวม</td>
<td width="100" rowspan="1" align="center" bgcolor="#CCCCCC"><?php echo $row['numall']; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $sum9 ; ?></td>
<td width="108" align="center" bgcolor="#CCCCCC">
<?php echo $sum10 ; ?></td>
.....
.....
Date :
2015-07-20 10:52:49By :
NewbiePHP
Server 05
Load balance : Server 05
