title: Viewing Attendance datewise using phpmysql
body: <pre><code>&lt;?php require_once('Connections/dbconnect.php'); ?&gt;
  &lt;?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);
  ?&gt;
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>&lt;html xmlns="http://www.w3.org/1999/xhtml"&gt;
  &lt;head&gt;
  &lt;meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /&gt;
  &lt;title&gt;Untitled Document&lt;/title&gt;
  &lt;style type="text/css"&gt;
  &lt;!--
  .style1 {color: #666600}
  .style2 {color: #000000}
  --&gt;
  &lt;/style&gt;
  &lt;/head&gt;
  &lt;body&gt;
  &lt;table width="640" border="0" align="center" cellpadding="4" cellspacing="4"&gt;
  &lt;tr&gt;
  &lt;td width="48" bgcolor="#000000"&gt;&lt;div align="center"&gt;SL No&lt;/div&gt;&lt;/td&gt;
  &lt;td width="225" bgcolor="#000000"&gt;&lt;div align="center"&gt;Name of Student&lt;/div&gt;&lt;/td&gt;
  &lt;td width="45" bgcolor="#000000"&gt;&lt;div align="center"&gt;Roll no&lt;/div&gt;&lt;/td&gt;
  &lt;td width="45" bgcolor="#000000"&gt;Day 1 &lt;/td&gt;
  &lt;td width="45" bgcolor="#000000"&gt;Day 2&lt;/td&gt;
  &lt;td width="45" bgcolor="#000000"&gt;Day 3 &lt;/td&gt;
  &lt;/tr&gt;
  &lt;?php do { ?&gt;
  &lt;tr&gt;
  &lt;td bgcolor="#333333"&gt;&lt;div align="center"&gt;&lt;?php echo $count++; ?&gt;&lt;/div&gt;&lt;/td&gt;
  &lt;td bgcolor="#333333"&gt;&lt;?php echo $row_classwise['Name_of_Student']; ?&gt;&lt;/td&gt;
  &lt;td bgcolor="#333333"&gt;&lt;div align="center"&gt;&lt;?php echo $row_classwise['Roll_no'];
  ?&gt; &lt;/div&gt;&lt;/td&gt;
  &lt;td bgcolor="#333333"&gt;&lt;?php echo $row_classwise['day1']; ?&gt;&lt;/td&gt;
  &lt;td bgcolor="#333333"&gt;&lt;?php echo $row_classwise['day2']; ?&gt;&lt;/td&gt;
  &lt;td bgcolor="#333333"&gt;&lt;?php echo $row_classwise['day3']; ?&gt;&lt;/td&gt;
  &lt;/tr&gt;
  &lt;?php } while ($row_classwise = mysql_fetch_assoc($classwise)); ?&gt;
  &lt;/table&gt;
  &lt;/body&gt;
  &lt;/html&gt;
  &lt;?php
  mysql_free_result($classwise);
  ?&gt;
  </code></pre>
  
  <p>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...</p>
  
  <p>Here is my new query, I want to put -/- and - where the day is Saturday and Sunday or Holiday:</p>
  
  <pre><code>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
  </code></pre>