Pivot Table or Cross Tab in PHP using MYSQL for attendance

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
akshayhomkar
Forum Newbie
Posts: 1
Joined: Wed Jan 15, 2014 10:55 am

Pivot Table or Cross Tab in PHP using MYSQL for attendance

Post by akshayhomkar »

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

Code: Select all

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

Code: Select all

<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

awaiting valuable reply
Attachments
tableformatexp.jpg
tableformatexp.jpg (8.07 KiB) Viewed 4618 times
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Pivot Table or Cross Tab in PHP using MYSQL for attendan

Post by requinix »

Do just one query that gets all the students and their attendance records for all the dates. At one time. Then turn the resultset into an array of students and dates that looks like

Code: Select all

array(
	"Student name1" => array(
		"12-11-2013" => "P",
		"13-11-2013" => "A",
		"16-11-2013" => "A"
	),
	"Staffname 1" => array(
		"12-11-2013" => "P",
		"13-11-2013" => "P",
		"16-11-2013" => "A"
	)
)
You can do that by
1. Start with an empty array
2. Loop through the rows returned by the query
3. If the array does not already have an item for the student, add it
4. Set the attendance record, like $array[student's name][attendance date] = attandance value

Now you can begin printing out the table. Loop through the new array to get the attendance data for each student. Then inside that, loop through the big list of dates you got at the start and print a cell for each date; the value you put in the cell is the value you get from within the array of the student's attendance data (if there is a value there).
olivierh
Forum Newbie
Posts: 4
Joined: Thu Jan 30, 2014 3:10 pm

Re: Pivot Table or Cross Tab in PHP using MYSQL for attendan

Post by olivierh »

thanks for your answer. I would like to jump in as I am also interested in a similar query.

Could you elaborate a bit on how to achieve 2, meaning adding an item for a student only if it does not already exist.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Pivot Table or Cross Tab in PHP using MYSQL for attendan

Post by requinix »

olivierh wrote:Could you elaborate a bit on how to achieve 2, meaning adding an item for a student only if it does not already exist.
Say there's a third student, Bob Smith, beyond the two I showed. Also say the array looks like how I posted, after six rows of data for two people:

Code: Select all

array(
   "Student name1" => array(
      "12-11-2013" => "P",
      "13-11-2013" => "A",
      "16-11-2013" => "A"
   ),
   "Staffname 1" => array(
      "12-11-2013" => "P",
      "13-11-2013" => "P",
      "16-11-2013" => "A"
   )
)
Step 3 then reads "If the array does not already have an item for Bob Smith, add it". Since the names are being used as array keys you can use isset() to check:

Code: Select all

if (!isset($array["Bob Smith" /* you'd put a variable in here, of course */])) {
    $array["Bob Smith"] = array(); // adding an (empty) item for the student, this being the first time we've seen it from the query
}
Continuing on, step 4 looks like (with example data)

Code: Select all

$array["Bob Smith"]["12-11-2013" /* another variable */] = "A" /* and a third */;
and the array now has

Code: Select all

array(
   "Student name1" => array(
      "12-11-2013" => "P",
      "13-11-2013" => "A",
      "16-11-2013" => "A"
   ),
   "Staffname 1" => array(
      "12-11-2013" => "P",
      "13-11-2013" => "P",
      "16-11-2013" => "A"
   ),
   "Bob Smith" => array(
      "12-11-2013" => "A"
   )
)
olivierh
Forum Newbie
Posts: 4
Joined: Thu Jan 30, 2014 3:10 pm

Re: Pivot Table or Cross Tab in PHP using MYSQL for attendan

Post by olivierh »

great. Thanks so much for your prompt email.

I think I have all the necessary info to implement this.
Post Reply