ramiwahdan Posted March 15, 2020 Share Posted March 15, 2020 Hi, I have database, screenshot attached. I have to print all records of attendance sort by ID and have sum group by ID. How to know or figure out when the IDÂ changes so that i can show the total for that ID. Bare with me please and try to show me where is my mistake! Code: <?Php include('session.php'); include('dbcon.php'); ?> <html> <head> <link rel="stylesheet" type="text/css" href="bootstrap.css"> </head> <body> <div class="container"> <div class="row"> <div class="col m-auto"> <div class="card mt-5"> <table class="table table-bordered"> <tr> <input type="button" onClick="window.print()" value="Print The Report"/> <td>OracleID</td> <td>Name</td> <td>Designation</td> <td>Clocking In Time</td> <td>Clocking Out Time</td> <td>Duration</td> </tr> <?php $isdone = -1; $query = "select * from attendance_records where isdone= '".$isdone."' order by OracleID"; $result = mysqli_query($con, $query); $query2 = "SELECT sec_to_time(SUM(timestampdiff(SECOND, ClockingInDate, ClockingOutDate))) as total from attendance_records group by OracleID"; $result2 = mysqli_query($con, $query2); while ($row = mysqli_fetch_assoc($result)) { $userid = $row['OracleID']; $name = $row['Name']; $des = $row['Des']; $clockingindate = $row['ClockingInDate']; $clockingoutdate = $row['ClockingOutDate']; $duration = $row['Duration']; $t=time(); $curdate = date("d-m-Y",$t); $curTime = date("g:i:s A",$t); ?> <tr> <td><?php echo $userid ?></td> <td><?php echo $name ?></td> <td><?php echo $des ?></td> <td><?php echo $clockingindate ?></td> <td><?php echo $clockingoutdate ?></td> <td><?php echo $duration ?></td> </tr> <?php } while($row2 = mysqli_fetch_assoc($result2)) { $totals= $row2['total']; echo '<td><h5>Total Attendance Time:</h5></td>'; echo '<td>'; echo $totals; echo '</td>'; } echo '<tr>'; echo 'Report created by ', $_SESSION['user_id2'], ' on ', $curdate, ' at ', $curTime; echo '</tr>'; ?> </table> <a href="home.php" width="100%">Click here to go back to Main Menu</a> </div> </div> </div> </div> </body> </html> and screenshot of result is attached. I have the totals but not at the end of each OracleID, it is at the end of the report. Thanks. Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted March 15, 2020 Share Posted March 15, 2020 what do you want the output to actually be? where do you want to show the totals and do you really want to repeat all the id/name/designation information? Quote Link to comment Share on other sites More sharing options...
ramiwahdan Posted March 15, 2020 Author Share Posted March 15, 2020 (edited) 25 minutes ago, mac_gyver said: what do you want the output to actually be? where do you want to show the totals and do you really want to repeat all the id/name/designation information? I am new so it into show ID, name and designation once then attendance records for that ID. the total after each ID group. In my example ID 533349 then total for that then ID 533355 then total for that. Edited March 15, 2020 by ramiwahdan Error typo Quote Link to comment Share on other sites More sharing options...
Barand Posted March 16, 2020 Share Posted March 16, 2020 try <?php require 'db_inc.php'; $db = pdoConnect('test'); $res = $db->query("SELECT oracleid , name , des , clockingindate as clockin , clockingoutdate as clockout , timediff(clockingoutdate, clockingindate) as duration , total FROM attendance_record JOIN ( SELECT oracleid , sec_to_time(sum(timestampdiff(SECOND, clockingindate, clockingoutdate))) as total FROM attendance_record GROUP BY oracleid ) tots USING (oracleid) ORDER BY oracleid, clockingindate "); ?> <html> <body> <table border='1' style='border-collapse:collapse; width: 700px;'> <tr><th>OracleID</th><th>Name</th><th>Designation</th><th>Clock In</th><th>Clock Out</th><th>Duration</th></tr> <?php $previd = 0; foreach ($res as $row) { if ($row['oracleid'] != $previd) { // id changed so output total if ($previd != 0) { echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>"; } $previd = $row['oracleid']; } echo "<tr><td>{$row['oracleid']}</td> <td>{$row['name']}</td> <td>{$row['des']}</td> <td>{$row['clockin']}</td> <td>{$row['clockout']}</td> <td>{$row['duration']}</td> </tr> "; $total = $row['total']; } echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>"; // dont forget total for last id ?> </table> </body> </html> 1 Quote Link to comment Share on other sites More sharing options...
ramiwahdan Posted March 16, 2020 Author Share Posted March 16, 2020 6 hours ago, Barand said: try <?php require 'db_inc.php'; $db = pdoConnect('test'); $res = $db->query("SELECT oracleid , name , des , clockingindate as clockin , clockingoutdate as clockout , timediff(clockingoutdate, clockingindate) as duration , total FROM attendance_record JOIN ( SELECT oracleid , sec_to_time(sum(timestampdiff(SECOND, clockingindate, clockingoutdate))) as total FROM attendance_record GROUP BY oracleid ) tots USING (oracleid) ORDER BY oracleid, clockingindate "); ?> <html> <body> <table border='1' style='border-collapse:collapse; width: 700px;'> <tr><th>OracleID</th><th>Name</th><th>Designation</th><th>Clock In</th><th>Clock Out</th><th>Duration</th></tr> <?php $previd = 0; foreach ($res as $row) { if ($row['oracleid'] != $previd) { // id changed so output total if ($previd != 0) { echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>"; } $previd = $row['oracleid']; } echo "<tr><td>{$row['oracleid']}</td> <td>{$row['name']}</td> <td>{$row['des']}</td> <td>{$row['clockin']}</td> <td>{$row['clockout']}</td> <td>{$row['duration']}</td> </tr> "; $total = $row['total']; } echo "<tr><td colspan='4'</td><td>Total:</td><td>$total</td></tr>"; // dont forget total for last id ?> </table> </body> </html> Thanks for the help but i am new to php and don't know how to run the pdo, can you guide me how to connect with mysqli Quote Link to comment Share on other sites More sharing options...
Barand Posted March 16, 2020 Share Posted March 16, 2020 As it's a query() and not a prepare() the code is the same. Just create a mysqli connection instead. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.