Jump to content

sum group by

ramiwahdan

By ramiwahdan
in PHP Coding Help

 Share

Recommended Posts

ramiwahdan Advanced Member

ramiwahdan

Posted
Posted

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.

screen.png

Link to comment
https://forums.phpfreaks.com/topic/310291-sum-group-by/
Share on other sites
ramiwahdan Advanced Member

ramiwahdan

Posted
  • Author
Posted (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 by ramiwahdan
Error typo
Link to comment
https://forums.phpfreaks.com/topic/310291-sum-group-by/#findComment-1575464
Share on other sites
Barand Prolific Member

Barand

Posted
Posted

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>

image.png.d875a366b3de4c2e703ef4ef97d75602.png

  • Like 1
Link to comment
https://forums.phpfreaks.com/topic/310291-sum-group-by/#findComment-1575469
Share on other sites
ramiwahdan Advanced Member

ramiwahdan

Posted
  • Author
Posted
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>

image.png.d875a366b3de4c2e703ef4ef97d75602.png

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

Link to comment
https://forums.phpfreaks.com/topic/310291-sum-group-by/#findComment-1575471
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.