dpy83214 2013-07-04 13:13
浏览 43
已采纳

使用php mysql查看出勤日期

<?php require_once('Connections/dbconnect.php'); ?>
<?php
$c=$_GET['Class'];
$s=$_GET['Section'];
$f=$_GET['fmonth'];
$t=$_GET['tmonth'];
$count=1;
mysql_select_db($database_dbconnect, $dbconnect);
$query_classwise = "SELECT Name_of_Student, Roll_no, Section,
GROUP_CONCAT(if(DAY(`Date`) = 1, `Status`, NULL)) AS 'day1', 
GROUP_CONCAT(if(DAY(`Date`) = 2, `Status`, NULL)) AS 'day2', 
GROUP_CONCAT(if(DAY(`Date`) = 3, `Status`, NULL)) AS 'day3', 
GROUP_CONCAT(if(DAY(`Date`) = 4, `Status`, NULL)) AS 'day4', 
GROUP_CONCAT(if(DAY(`Date`) = 5, `Status`, NULL)) AS 'day5', 
GROUP_CONCAT(if(DAY(`Date`) = 6, `Status`, NULL)) AS 'day6', 
GROUP_CONCAT(if(DAY(`Date`) = 7, `Status`, NULL)) AS 'day7', 
GROUP_CONCAT(if(DAY(`Date`) = 8, `Status`, NULL)) AS 'day8', 
GROUP_CONCAT(if(DAY(`Date`) = 9, `Status`, NULL)) AS 'day9', 
GROUP_CONCAT(if(DAY(`Date`) = 10, `Status`, NULL)) AS'day10',
GROUP_CONCAT(if(DAY(`Date`) = 11, `Status`, NULL)) AS 'day11', 
GROUP_CONCAT(if(DAY(`Date`) = 12, `Status`, NULL)) AS 'day12', 
GROUP_CONCAT(if(DAY(`Date`) = 13, `Status`, NULL)) AS 'day13', 
GROUP_CONCAT(if(DAY(`Date`) = 14, `Status`, NULL)) AS 'day14', 
GROUP_CONCAT(if(DAY(`Date`) = 15, `Status`, NULL)) AS 'day15', 
GROUP_CONCAT(if(DAY(`Date`) = 16, `Status`, NULL)) AS 'day16', 
GROUP_CONCAT(if(DAY(`Date`) = 17, `Status`, NULL)) AS 'day17', 
GROUP_CONCAT(if(DAY(`Date`) = 18, `Status`, NULL)) AS 'day18', 
GROUP_CONCAT(if(DAY(`Date`) = 19, `Status`, NULL)) AS 'day19', 
GROUP_CONCAT(if(DAY(`Date`) = 20, `Status`, NULL)) AS 'day20', 
GROUP_CONCAT(if(DAY(`Date`) = 21, `Status`, NULL)) AS 'day21', 
GROUP_CONCAT(if(DAY(`Date`) = 22, `Status`, NULL)) AS 'day22', 
GROUP_CONCAT(if(DAY(`Date`) = 23, `Status`, NULL)) AS 'day23', 
GROUP_CONCAT(if(DAY(`Date`) = 24, `Status`, NULL)) AS 'day24', 
GROUP_CONCAT(if(DAY(`Date`) = 25, `Status`, NULL)) AS 'day25', 
GROUP_CONCAT(if(DAY(`Date`) = 26, `Status`, NULL)) AS 'day26', 
GROUP_CONCAT(if(DAY(`Date`) = 27, `Status`, NULL)) AS 'day27', 
GROUP_CONCAT(if(DAY(`Date`) = 28, `Status`, NULL)) AS 'day28', 
GROUP_CONCAT(if(DAY(`Date`) = 29, `Status`, NULL)) AS 'day29', 
GROUP_CONCAT(if(DAY(`Date`) = 30, `Status`, NULL)) AS 'day30',  
GROUP_CONCAT(if(DAY(`Date`) = 31, `Status`, NULL)) AS 'day31', 
COUNT(if(`Status`='Y', `Status`, NULL)) AS 'total' FROM `attendance`
WHERE `Date` BETWEEN '$f' AND '$t' GROUP BY Name_of_Student" ;
$classwise = mysql_query($query_classwise, $dbconnect) or die(mysql_error());
$row_classwise = mysql_fetch_assoc($classwise);
$totalRows_classwise = mysql_num_rows($classwise);
?>

My query is very long, I don't understand whether my query is correct or not, My knowledge in php is very limited, I try to output using the following php code:

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {color: #666600}
.style2 {color: #000000}
-->
</style>
</head>
<body>
<table width="640" border="0" align="center" cellpadding="4" cellspacing="4">
<tr>
<td width="48" bgcolor="#000000"><div align="center">SL No</div></td>
<td width="225" bgcolor="#000000"><div align="center">Name of Student</div></td>
<td width="45" bgcolor="#000000"><div align="center">Roll no</div></td>
<td width="45" bgcolor="#000000">Day 1 </td>
<td width="45" bgcolor="#000000">Day 2</td>
<td width="45" bgcolor="#000000">Day 3 </td>
</tr>
<?php do { ?>
<tr>
<td bgcolor="#333333"><div align="center"><?php echo $count++; ?></div></td>
<td bgcolor="#333333"><?php echo $row_classwise['Name_of_Student']; ?></td>
<td bgcolor="#333333"><div align="center"><?php echo $row_classwise['Roll_no'];
?> </div></td>
<td bgcolor="#333333"><?php echo $row_classwise['day1']; ?></td>
<td bgcolor="#333333"><?php echo $row_classwise['day2']; ?></td>
<td bgcolor="#333333"><?php echo $row_classwise['day3']; ?></td>
</tr>
<?php } while ($row_classwise = mysql_fetch_assoc($classwise)); ?>
</table>
</body>
</html>
<?php
mysql_free_result($classwise);
?>

I want to get the correct Y or N under each day(day1,day2,and so on) columns. So far it does not output the desired resultI can provide the table if necessary...

Here is my new query, I want to put -/- and - where the day is Saturday and Sunday or Holiday:

SELECT Students.Name_of_Student, Students.Roll_no, Students.Class, Students.Section, allDays.aDay, 
GROUP_CONCAT(IFNULL(attendance.Status, '-') ORDER BY TimePeriod.`time` DESC SEPARATOR '/') AS Status
FROM (SELECT DISTINCT Name_of_Student, Roll_no, Class, Section FROM attendance ) AS Students
CROSS JOIN (SELECT 1 AS aDay UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
SELECT 10 AS aDay UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION 
SELECT 20 AS aDay UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION 
SELECT 30 UNION SELECT 31) allDays
CROSS JOIN (SELECT 'Morning' AS `time` UNION SELECT 'Afternoon') TimePeriod
LEFT OUTER JOIN attendance ON Students.Name_of_Student = attendance.Name_of_Student AND allDays.aDay = DAY(`Date`) 
AND BINARY TimePeriod.`time` = BINARY attendance.`time` where Date Between '$f' and '$t' and Students.Section='$s' and Students.Class='$c' 
GROUP BY Students.Name_of_Student, Students.Roll_no, Students.Section, allDays.aDay 
ORDER BY Students.Name_of_Student, allDays.aDay
  • 写回答

1条回答 默认 最新

  • douzhushen_9776 2013-07-04 14:11
    关注

    Rather than trying to get a whole line line that, probably easier to get a row per person per day. Then in your php code you throw a new line when the person changes.

    SQL something like this:-

    SELECT Students.Name_of_Student, Students.Roll_no, Students.Section, allDays.aDay, attendence.Status
    FROM (SELECT DISTINCT Name_of_Student FROM attendence WHERE `Date` BETWEEN '$f' AND '$t') AS Students
    CROSS JOIN (
    SELECT 1 AS aDay UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 AS aDay UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION 
    SELECT 20 AS aDay UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION 
    SELECT 30 UNION SELECT 31 
    ) allDays
    LEFT OUTER JOIN attendence
    ON Students.Name_of_Student = attendence.Name_of_Student AND allDays.aDay = DAY(`Date`)
    WHERE `Date` BETWEEN '$f' AND '$t'
    

    This is getting a list of the students (which would be easier if you had a student table) and a list of all the days of the month. Then joining those with a left join against attendence.

    Your php would then be something like this (not tested)

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    <style type="text/css">
    <!--
    .style1 {color: #666600}
    .style2 {color: #000000}
    -->
    </style>
    </head>
    <body>
    <table width="640" border="0" align="center" cellpadding="4" cellspacing="4">
    <tr>
    <td width="48" bgcolor="#000000"><div align="center">SL No</div></td>
    <td width="225" bgcolor="#000000"><div align="center">Name of Student</div></td>
    <td width="45" bgcolor="#000000"><div align="center">Roll no</div></td>
    <td width="45" bgcolor="#000000">Day 1 </td>
    <td width="45" bgcolor="#000000">Day 2</td>
    <td width="45" bgcolor="#000000">Day 3 </td>
    </tr>
    <?php
    $PrevName_of_Student = '';
    while ($row_classwise = mysql_fetch_assoc($classwise))
    { 
        if ($PrevName_of_Student != $row_classwise['Name_of_Student'])
        {
            if ($PrevName_of_Student ! = '')
            {
                echo '</tr>';
            }
            ?><tr>
            <td bgcolor="#333333"><div align="center"><?php echo $count++; ?></div></td>
            <td bgcolor="#333333"><?php echo $row_classwise['Name_of_Student']; ?></td>
            <td bgcolor="#333333"><div align="center"><?php echo $row_classwise['Roll_no'];?></div></td>
            <?php
            $PrevName_of_Student = $row_classwise['Name_of_Student'];
        }
    ?>
        <td bgcolor="#333333"><?php echo $row_classwise['Status']; ?></td>
        </tr>
    <?php 
    } 
    if ($PrevName_of_Student ! = '')
    {
        echo '</tr>';
    }
    ?>
    </table>
    </body>
    </html>
    <?php
    mysql_free_result($classwise);
    ?>
    

    Putting that together you get something like this (tested and working)

    <?php
    
    $link = mysql_connect('localhost', 'root', '');
    If (!$link) 
    {
        die ('Could not connect: ' . mysql_error());
    }
    @mysql_select_db('testarea') or die ('Unable to select database');
    
    $query = "SELECT Students.Name_of_Student, Students.Roll_no, Students.Section, allDays.aDay, attendance.Status, TimePeriod.`time`
    FROM (SELECT DISTINCT Name_of_Student, Roll_no, Section FROM attendance ) AS Students
    CROSS JOIN (
    SELECT 1 AS aDay UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 AS aDay UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION 
    SELECT 20 AS aDay UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION 
    SELECT 30 UNION SELECT 31 
    ) allDays
    CROSS JOIN (SELECT 'Morning' AS `time` UNION SELECT 'Afternoon') TimePeriod
    LEFT OUTER JOIN attendance
    ON Students.Name_of_Student = attendance.Name_of_Student AND allDays.aDay = DAY(`Date`) AND BINARY TimePeriod.`time` = BINARY attendance.`time`
    ORDER BY Students.Name_of_Student, allDays.aDay, TimePeriod.`time` DESC";
    
    $classwise = mysql_query($query);
    
    ?>
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    <style type="text/css">
    <!--
    .style1 {color: #666600}
    .style2 {color: #000000}
    -->
    </style>
    </head>
    <body>
    <table width="640" border="0" align="center" cellpadding="4" cellspacing="4">
    <tr>
    <td width="48" ><div align="center">SL No</div></td>
    <td width="225" ><div align="center">Name of Student</div></td>
    <td width="45" ><div align="center">Roll no</div></td>
    <?php
    for($iCnt = 1; $iCnt <=31; $iCnt++)
    {
        foreach(array("Morning", "Afternoon") AS $TimePeriod)
        {
            echo "<td width='45' >Day $iCnt $TimePeriod</td>";
        }
    }
    echo "</tr>";
    $PrevName_of_Student = '';
    $count = 0;
    while ($row_classwise = mysql_fetch_assoc($classwise))
    { 
        if ($PrevName_of_Student != $row_classwise['Name_of_Student'])
        {
            if ($PrevName_of_Student != '')
            {
                echo '</tr>';
            }
            echo "<tr>";
            echo "<td ><div align='center'>".$count++."</div></td>";
            echo "<td >".$row_classwise['Name_of_Student']."</td>";
            echo "<td ><div align='center'>".$row_classwise['Roll_no']."</div></td>";
            $PrevName_of_Student = $row_classwise['Name_of_Student'];
        }
        echo "<td >".$row_classwise['Status']."</td>";
    } 
    if ($PrevName_of_Student != '')
    {
        echo '</tr>';
    }
    mysql_free_result($classwise);
    ?>
    </table>
    </body>
    </html>
    

    EDIT - merge the morning and afternoon records for a day for a person:-

    <?php
    
    $link = mysql_connect('localhost', 'root', '');
    If (!$link) 
    {
        die ('Could not connect: ' . mysql_error());
    }
    @mysql_select_db('testarea') or die ('Unable to select database');
    
    $query = "SELECT Students.Name_of_Student, Students.Roll_no, Students.Section, allDays.aDay, GROUP_CONCAT(IFNULL(attendance.Status, '-') ORDER BY TimePeriod.`time` DESC SEPARATOR '/') AS Status
    FROM (SELECT DISTINCT Name_of_Student, Roll_no, Section FROM attendance ) AS Students
    CROSS JOIN (
    SELECT 1 AS aDay UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 AS aDay UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION 
    SELECT 20 AS aDay UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION 
    SELECT 30 UNION SELECT 31 
    ) allDays
    CROSS JOIN (SELECT 'Morning' AS `time` UNION SELECT 'Afternoon') TimePeriod
    LEFT OUTER JOIN attendance
    ON Students.Name_of_Student = attendance.Name_of_Student AND allDays.aDay = DAY(`Date`) AND BINARY TimePeriod.`time` = BINARY attendance.`time`
    GROUP BY Students.Name_of_Student, Students.Roll_no, Students.Section, allDays.aDay
    ORDER BY Students.Name_of_Student, allDays.aDay";
    
    $classwise = mysql_query($query);
    
    ?>
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
    <title>Untitled Document</title>
    <style type="text/css">
    <!--
    .style1 {color: #666600}
    .style2 {color: #000000}
    -->
    </style>
    </head>
    <body>
    <table width="640" border="0" align="center" cellpadding="4" cellspacing="4">
    <tr>
    <td width="48" ><div align="center">SL No</div></td>
    <td width="225" ><div align="center">Name of Student</div></td>
    <td width="45" ><div align="center">Roll no</div></td>
    <?php
    for($iCnt = 1; $iCnt <=31; $iCnt++)
    {
        echo "<td width='45' >Day $iCnt </td>";
    }
    echo "</tr>";
    $PrevName_of_Student = '';
    $count = 0;
    while ($row_classwise = mysql_fetch_assoc($classwise))
    { 
        if ($PrevName_of_Student != $row_classwise['Name_of_Student'])
        {
            if ($PrevName_of_Student != '')
            {
                echo '</tr>';
            }
            echo "<tr>";
            echo "<td ><div align='center'>".$count++."</div></td>";
            echo "<td >".$row_classwise['Name_of_Student']."</td>";
            echo "<td ><div align='center'>".$row_classwise['Roll_no']."</div></td>";
            $PrevName_of_Student = $row_classwise['Name_of_Student'];
        }
        echo "<td >".$row_classwise['Status']."</td>";
    } 
    if ($PrevName_of_Student != '')
    {
        echo '</tr>';
    }
    mysql_free_result($classwise);
    ?>
    </table>
    </body>
    </html>
    

    EDIT - updated query.

    Note I have put the clauses to limit the students within the sub select which gets the list of students. Further the check for dates I have put in the ON clause of the LEFT OUTER JOIN to get the actual attendance. This was even if the student has not attended any classes in that time period they will still appear in the report (just with -/- for each day).

    SELECT Students.Name_of_Student, Students.Roll_no, Students.Class, Students.Section, allDays.aDay, 
    GROUP_CONCAT(IFNULL(attendance.Status, '-') ORDER BY TimePeriod.`time` DESC SEPARATOR '/') AS Status
    FROM (SELECT DISTINCT Name_of_Student, Roll_no, Class, Section FROM attendance where Section='$s' AND Class='$c' ) AS Students
    CROSS JOIN (SELECT 1 AS aDay UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION 
    SELECT 10 AS aDay UNION SELECT 11 UNION SELECT 12 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION 
    SELECT 20 AS aDay UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28 UNION SELECT 29 UNION 
    SELECT 30 UNION SELECT 31) allDays
    CROSS JOIN (SELECT 'Morning' AS `time` UNION SELECT 'Afternoon') TimePeriod
    LEFT OUTER JOIN attendance ON Students.Name_of_Student = attendance.Name_of_Student AND allDays.aDay = DAY(`Date`) 
    AND BINARY TimePeriod.`time` = BINARY attendance.`time` 
    AND Date Between '$f' and '$t' 
    GROUP BY Students.Name_of_Student, Students.Roll_no, Students.Section, allDays.aDay 
    ORDER BY Students.Name_of_Student, allDays.aDay
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 phython如何实现以下功能?查找同一用户名的消费金额合并—
  • ¥15 孟德尔随机化怎样画共定位分析图
  • ¥18 模拟电路问题解答有偿速度
  • ¥15 CST仿真别人的模型结果仿真结果S参数完全不对
  • ¥15 误删注册表文件致win10无法开启
  • ¥15 请问在阿里云服务器中怎么利用数据库制作网站
  • ¥60 ESP32怎么烧录自启动程序,怎么查看客户esp32板子上程序及烧录地址
  • ¥50 html2canvas超出滚动条不显示
  • ¥15 java业务性能问题求解(sql,业务设计相关)
  • ¥15 52810 尾椎c三个a 写蓝牙地址