Creating Pivot table for attendance using php and mysql

Question

I want to make attendance view like this using phpmysql

The following is my attendance table :

    `CREATE TABLE IF NOT EXISTS `attendance` (
      `aid` int(11) NOT NULL AUTO_INCREMENT,
      `Name_of_Student` varchar(100) COLLATE latin1_general_ci NOT NULL,
      `Class` varchar(20) COLLATE latin1_general_ci NOT NULL,
      `Roll_no` int(11) NOT NULL,
      `Section` varchar(20) COLLATE latin1_general_ci NOT NULL,
      `Status` binary(1) NOT NULL,
      `time` varchar(20) COLLATE latin1_general_ci NOT NULL,
      `Date` date NOT NULL,
      PRIMARY KEY (`aid`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=33 ;

--
-- Dumping data for table `attendance`
--

    INSERT INTO `attendance` (`aid`, `Name_of_Student`, `Class`, `Roll_no`, `Section`, `Status`, `time`, `Date`) VALUES
    (1, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-01'),
    (2, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-01'),
    (3, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-02'),
    (4, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-02'),
    (5, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-03'),
    (6, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-03'),
    (7, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-04'),
    (8, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-04'),
    (9, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-05'),
    (10, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-05'),
    (11, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-06'),
    (12, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-06'),
    (13, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-07'),
    (14, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-07'),
    (15, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Morning', '2013-07-08'),
    (16, 'Zonundanga', 'X', 5, 'A', 'Y', 'Morning', '2013-07-08'),
    (17, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-01'),
    (18, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-01'),
    (19, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-02'),
    (20, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-02'),
    (21, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-03'),
    (22, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-03'),
    (23, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-04'),
    (24, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-04'),
    (25, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-05'),
    (26, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-05'),
    (27, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-06'),
    (28, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-06'),
    (29, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-07'),
    (30, 'Zonundanga', 'X', 5, 'A', 'Y', 'Afternoon', '2013-07-07'),
    (31, 'Lalchhandami', 'X', 1, 'A', 'Y', 'Afternoon', '2013-07-08'),
    (32, 'Zonundanga', 'X', 5, 'A', 'N', 'Afternoon', '2013-07-08');`

The attempt code is here: SQL:

SELECT * FROM attendance

PHP:

$query = $dbh->query("SELECT * FROM attendance");

$result = $query->fetchAll(PDO::FETCH_OBJ);
$column = [];
foreach($result as $key=>$val){
    $column[$val->Name_of_Student][$val->Date][$val->time] = $val->Status; 
}

$Status = []; 
$hming = [];
?>
<table border="1" align="center" width="100%">
    <tr>
        <th>Name</th>
        <?php foreach($column as $dt=>$vt):             
                $hming[] = $dt;
                foreach($vt as $k=>$v):                     
                    $Status[$k] = $v['Morning'];
                    $Status[$k] = $v['Afternoon'];
                    ?>
        <th colspan="2"><?php echo $k; ?></th>
        <?php endforeach; endforeach; ?>
    </tr>
    <?php foreach($hming as $name): ?>
    <tr>
        <td><?php echo $name; ?></td>
        <?php foreach($Status as $time): ?>
        <td><?php echo $time; ?></td>
        <?php endforeach;?>
    </tr>
    <?php endforeach;?>
</table>

The problem is the date is not looping for each student.

You could at least go to the effort of formatting your code properly — Bojangles
– Bojangles, Commented Jul 4, 2013 at 11:22

Rohitashv Singhal · Accepted Answer · 2013-07-04 11:27:23Z

0

Select all data using the mysql_query, and dump the data into your HTML

answered Jul 4, 2013 at 11:27

Rohitashv Singhal

4,56713 gold badges63 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mawia HL Over a year ago

I am newbie...all I want is to make view like postimg.org/image/t46xc1laz using php.

Mawia HL · Accepted Answer · 2018-06-17 12:18:16Z

The corrected solution to my question is here:

$query = $dbh->query("SELECT * FROM attendance");
$result = $query->fetchAll(PDO::FETCH_OBJ);
$column = [];
foreach($result as $key=>$val){
    $column[$val->Name_of_Student][$val->Date][$val->time] = $val->Status; 
}
$dateat = [];
foreach($column as $studentname=>$hming){   
    foreach($hming as $dt=>$nihming){
        $dateat[$studentname][] = $dt;
    }
}
$prevDate = []; 
?>
<table border="1" align="center" width="80%" cellpadding="10" cellspacing="4">
    <?php 
        echo "<tr>";
        echo "<td>Name</td>";
        foreach($dateat as $nk=>$nithar):
                foreach($nithar as $atdate):                    
                    if (!in_array($atdate, $prevDate)) {
                        $prevDate[] = $atdate;
                        echo '<td colspan="2">'.$atdate."</td>\n";
                    }
                endforeach; 
            endforeach; 
        echo "</tr>";
    foreach($column as $stname=>$stval):
            echo "<tr>";
            echo "<td>".$stname."</td>\n";
            foreach($stval as $sk=>$sv){
                foreach($sv as $stt){
                    echo "<td>".$stt."</td>\n";
                }
            }
            echo "</tr>";
    endforeach; ?>
</table>

Output:

<table border="1" align="center" width="80%" cellpadding="10" cellspacing="4">
	<tr><td>Name</td><td colspan="2">2013-07-01</td>
<td colspan="2">2013-07-02</td>
<td colspan="2">2013-07-03</td>
<td colspan="2">2013-07-04</td>
<td colspan="2">2013-07-05</td>
<td colspan="2">2013-07-06</td>
<td colspan="2">2013-07-07</td>
<td colspan="2">2013-07-08</td>
</tr><tr><td>Lalchhandami</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
</tr><tr><td>Zonundanga</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
<td>N</td>
</tr></table>

Rohitashv Singhal · Accepted Answer · 2013-07-04 11:35:38Z

-1

You can go like this:

<table>
<tr><th>Sr no</th><th > .....//all of your table heads </th> </tr>   
<?php

    $query = "select * from `attendance`";
    $result = mysql_query($query);
    $result = mysql_fetch_row($result);
    foreach($result as $result)
    {
       echo "<tr><td>".$result['aid']."</td><td>all of id with index</td></tr>"
    }
    ?> </table>

edited Jul 4, 2013 at 11:35

answered Jul 4, 2013 at 11:29

Rohitashv Singhal

4,56713 gold badges63 silver badges107 bronze badges

3 Comments

Mawia HL Over a year ago

I want something like pivot table..I want to use the date rows as column and under each column will be Y for present and N for Absent and I want to group it by <pre>Name_of_Student</pre>

Rohitashv Singhal Over a year ago

You can have the referance here stackoverflow.com/questions/14492552/…

Mawia HL Over a year ago

I have already visited the link you gave me. The problem is that I don't know how to use the query in php. I am also not counting the number of Y or N. I just want to display Y or N under each corresponding date(used as column). Please can you help me. I am new, I've been trying to solve this problem since five days ago.

Collectives™ on Stack Overflow

Creating Pivot table for attendance using php and mysql

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related