1
Answer

Pivot Table or Cross Tab in PHP using MYSQL for attendance

Hello
 
I want to create attendance sheet on which date are printed as column and name of student/staff as column
database is as shown
CREATE TABLE IF NOT EXISTS `attendance` (
  `date` varchar(500) DEFAULT NULL,
  `time` varchar(1000) DEFAULT NULL,
  `staffname` varchar(1000) DEFAULT NULL,
  `id` int(11) DEFAULT NULL,
  `role` varchar(1000) NOT NULL,
  `status` varchar(1) DEFAULT NULL
)
but when I querying the table repeating the names of student and staff which not gives me report as expected here I attaching code also
 <table align="letf"  style="margin-left: 0px; border: 1px solid black; border-spacing: 0px;"  width="8"> 
        <th style="border: 1px solid black; text-align: center;">Date</th>
        <?php
         $sql133="select distinct date from attendance";
       $sql_row133=mysqli_query($dbConn,$sql133);       
       while($sql_res133=mysqli_fetch_assoc($sql_row133))       {
       $date=$sql_res133["date"];
             ?>         
       <th style="border: 1px solid black; text-align: center;">
       <?php echo $date;  ?>
   </th>
                <?php 
           
      $a=$date;
                $sql13="     
  SELECT   atten.date,atten.time,atten.staffname,atten.id, atten.status, supst.id, supst.staffname
    FROM
     (examcenter.attendance atten INNER JOIN examcenter.supportstaff supst ON
         atten.id = supst.id) where atten.date='$a'    group by supst.staffname,supst.id ORDER BY
    atten.id ASC    ";
                 $sql_row13=mysqli_query($dbConn,$sql13);
             while($sql_res13=mysqli_fetch_assoc($sql_row13))      {
             $staffname=$sql_res13["staffname"];
         $status=$sql_res13["status"];
                                       ?>
        
        <tr>
           <td><?php echo $staffname; ?></td>
        <td><?php echo $status; ?></td>
       
    <?php
       }
       }
      
     ?>
    
</table>
please guide what do to create a report as expected
 
Name/Date

 12-11-2013         

 13-11-2013 16-11-2013
Student name1 P             A A
Staffname 1 P P A
awaiting valuable reply

Answers (1)