<!-- 13. PHP Program to read data from MySQL table and calculate attendance percentage & Attendance Shortage. -->
<!-- Run this query from MySQL [insert into prog13 values('BC180123','XYZ','WP',40,36,0);]-->
<html>
<head>
<title>Read data and update mysql table using php </title>
</head>
<body bgcolor=gray text=yellow>
<?php
// Establish Connection with Server
$connection = mysql_connect("localhost", "root", "");
// Selecting Database
$db = mysql_select_db("test", $connection);
//MySQL Query to read data
$query = mysql_query("select * from prog13", $connection);
echo "<h3>Student Attendance Details</h3>";
echo "<table border=3 cellspacing=5>";
echo"<tr><th>RegNo</th><th>StudentName</th><th>Subject</th><th>ClassesHeld</th><th>ClassesAttended</th><th>AttendancePercentage</th><th>AttendanceShortage</th></tr>";
while ($row = mysql_fetch_array($query))
{
echo "<tr><td>".$row['RegNo']."</td>";
echo "<td>".$row['StudentName']."</td>";
echo "<td>".$row['Subject']."</td>";
echo "<td>".$row['ClassesHeld']."</td>";
echo "<td>".$row['ClassesAttended']."</td>";
echo "<td>".$row['AttendancePercentage']."</td>";
$reg=$row['RegNo'];
$CH=$row['ClassesHeld'];
$CA=$row['ClassesAttended'];
$per=($CA/$CH)*100;
if($per<75)$status='YES';
else $status='NO';
echo "<td>".$status."</td></tr>";
//Query for updation with calculated percentage.
$query1 = mysql_query("update prog13 set AttendancePercentage='".$per."'where RegNo='".$reg."';",$connection);
//Query to reset AttendancePercentage to zero.
//$query1 = mysql_query("update prog13 set AttendancePercentage=0 where RegNo='".$reg."';",$connection);
}
echo "<table border=3>";
echo "<br><b>MySQL table updated successfully.</b>";
mysql_close($connection); // Closing Connection with Server
?>
</body>
</html>