Calculete Time Diffrence in php mysql

i am fresher in php: i have table (admin) where two row is there intime,outtime
i wants to calculete time Diffrence and show to frontend how can i do that12.PNG

i Want to show total time diffrence like this format 08.00.12hrs[hours:minutes:second]
manoj kumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vish KTechnologistCommented:
Try carbon. Visit this link for begter help.
http://carbon.nesbot.com/docs/
0
manoj kumarAuthor Commented:
can u give some to calulete time diffrence in php with mysql ,it's urgent is there
help me plz...using carbon i donot understand because i am fresher
0
Vish KTechnologistCommented:
Use strtotime to convert into seconds, then subtract to get differences. Then use date to format it.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

manoj kumarAuthor Commented:
can u give some snippet of code:how to convert and display total loginhours

plz help me
0
manoj kumarAuthor Commented:
My Php code to display id,username,logintime,logouttime
i also want to caluelete total login hours
how can i do that<?php require_once 'database-config.php'; $sql = "select admins.id, userregisters.username, admins.intime, admins.outtime from admins inner join userregisters on admins.userid = userregisters.id"; if($result = mysql_query($sql)){ if(mysql_num_rows($result) > 0){ echo "<table border='1'>"; echo "<tr>"; echo "<th>ID,</th>"; echo "<th>User_ID,</th>"; echo "<th>Logintime,</th>"; echo "<th>Logout_Time,</th>"; echo "</tr>"; while($row = mysql_fetch_array($result)){ echo "<tr>"; echo "<td>" . $row['id'] . "</td>"; echo "<td>" . $row['username'] . "</td>"; echo "<td>" .$row['intime'] . "</td>"; echo "<td>" . $row['outtime'] . "</td>"; echo "</tr>"; } } else{ echo "No records were found."; } } else{ echo "ERROR: Could not able to execute $sql. " ; } ?>
0
manoj kumarAuthor Commented:
<?php
					
                  require_once 'database-config.php';
                    
                    $sql = "select admins.id, userregisters.username, admins.intime, admins.outtime
from admins inner join userregisters
on admins.userid = userregisters.id"; 
                    if($result = mysql_query($sql)){
                        if(mysql_num_rows($result) > 0){
							echo "<table border='1'>";
							echo "<tr>";
                         echo "<th>ID,</th>";
						   echo "<th>User_ID,</th>";  
						   echo "<th>Logintime,</th>";
						     echo "<th>Logout_Time,</th>";
                         
						 echo "</tr>";
                                while($row = mysql_fetch_array($result)){
									echo "<tr>";
                                   
								     echo "<td>" . $row['id'] . "</td>";
									   echo "<td>" . $row['username'] . "</td>";
									     echo "<td>" .$row['intime'] . "</td>";
										   echo "<td>" . $row['outtime'] . "</td>";
                                      
                          echo "</tr>";
								}
                        } else{
                            echo "No records were found.";
                        }
						
                    } else{
                        echo "ERROR: Could not able to execute $sql. " ;
                    }
    
                   
                    ?>

Open in new window

0
manoj kumarAuthor Commented:
But i wants total login hours:how can i do that
0
Julian HansenCommented:
Take a look at the mysql TIMEDIFF function

SELECT username, intime, outtime, TIMEDIFF(outtime, intime) AS TimeDifference FROM admin WHERE userid =230

Open in new window

0
manoj kumarAuthor Commented:
Then how to print Totaltime Diffrence in php,in my page it show only userid,logintime,logouttime but not prit timm diffrence
Help me Julian Hansen Where i can print time Diffrencerequire_once 'database-config.php'; if(isset($_POST['search'])) { $userid=$_POST['userid']; $sql1="SELECT username, intime, outtime, TIMEDIFF(outtime, intime) AS TimeDifference FROM admin WHERE userid =$userid"; echo "$sql1"; if($result = mysql_query($sql)){ if(mysql_num_rows($result) > 0){ echo "<table class='table table-bordered table-striped'>"; echo "<thead>"; echo "<tr>"; echo "<th>id</th>"; echo "<th>userid</th>"; echo "<th>LoginTime</th>"; echo "<th>LogoutTime</th>"; echo "</tr>"; echo "</thead>"; echo "<tbody>"; while($row = mysql_fetch_array($result)){ echo "<tr>"; echo "<td>" . $row['id'] . "</td>"; echo "<td>" . $row['userid'] . "</td>"; echo "<td>" .$row['intime'] . "</td>"; echo "<td>" . $row['outtime'] . "</td>"; echo "<td>"; echo "</tr>"; } echo "</tbody>"; echo "</table>"; mysql_free_result($result);
0
manoj kumarAuthor Commented:
  <?php
                  
                    require_once 'database-config.php';
					if(isset($_POST['search']))
					 {
						$userid=$_POST['userid'];
					$sql1="SELECT intime, outtime, TIMEDIFF(outtime, intime) AS TimeDifference FROM admins WHERE userid =$userid";
					echo "$sql1";
					  if($result = mysql_query($sql1)){
                          if(mysql_num_rows($result) > 0){
                            echo "<table class='table table-bordered table-striped'>";
                                echo "<thead>";
                                    echo "<tr>";
                                        echo "<th>id</th>";
										echo "<th>userid</th>";
                                    
                                        echo "<th>LoginTime</th>";
										echo "<th>LogoutTime</th>";
									
                                       
                                    echo "</tr>";
                                echo "</thead>";
                                echo "<tbody>";
                                while($row = mysql_fetch_array($result)){
                                    echo "<tr>";
                                        echo "<td>" . $row['id'] . "</td>";
										echo "<td>" . $row['userid'] . "</td>";
                                         echo "<td>" .$row['intime'] . "</td>";
										echo "<td>" . $row['TIMEDIFF'] . "</td>";
										
                                      
                                        echo "<td>";
                                           
                                    echo "</tr>";
                                }
                                echo "</tbody>";                            
                            echo "</table>";
                            
                            mysql_free_result($result);
                        } else{
                            echo "<p class='lead'><em>No records were found.</em></p>";
                        }
                    }
                     else{
                        echo "ERROR: Could not able to execute $sql. " . mysql_error();
                    }}
					
		
				
				
				
    ?>

Open in new window



Sorry this is code in php
0
manoj kumarAuthor Commented:
Julian Hansen help me for calculeting time diifrence and display in php
0
Julian HansenCommented:
Why do you want to do it in PHP when you can do it as part of the SQL Query?
0
manoj kumarAuthor Commented:
Because we calculete intime and outtime tiffrence and diplay the user how much time user can loged in
so i am displaying intime,outtime and total login time to display the user
0
manoj kumarAuthor Commented:
Dear Julian Hansen,can it possible to that
0
Julian HansenCommented:
But you can do that with an SQL Query - I still don't see what doing it in PHP is going to do for you - using the DB is the right way to do it - you get the answer in your Query results.

However, if you feel you have to use PHP then you can use DateTime::diff(()
<?php
$datetime1 = new DateTime($row['intime']);
$datetime2 = new DateTime($row['outtime']);
$interval = $datetime1->diff($datetime2);
$hours = $interval->d > 0 ? $interval->d * 24 + $interval->h : $interval->h;
echo $interval->format("{$hours}:%i:%s");

Open in new window


BTW: You are STILL using MySQL library. This has been deprecated. It will not work on later versions of PHP. Consider upgrading to MySQLi or PDO

http://php.net/manual/en/migration55.deprecated.php
0
manoj kumarAuthor Commented:
while($row = mysql_fetch_array($result)){
                                    echo "<tr>";
                                        echo "<td>" . $row['id'] . "</td>";
										echo "<td>" . $row['userid'] . "</td>";
                                         echo "<td>" .$row['intime'] . "</td>";
										  echo "<td>" .$row['outtime'] . "</td>";
										  $datetime1 = new DateTime($row['intime']);
                                              $datetime2 = new DateTime($row['outtime']);
						                      $interval = $datetime1->diff($datetime2);
						                      $hours = $interval->d > 0 ? $interval->d * 24 + $interval->h : $interval->h;
										      
						                      echo $interval->format("{$hours}:%i:%s");

Open in new window

But It showing  for Each user
man00.PNG
0
manoj kumarAuthor Commented:
I want to display for each user for her logintime and logout time i want to display totallogin hours

help me Julian Hansen
0
Julian HansenCommented:
Where is your query!
Please post code relevant to the output - to determine why you are getting 0000-00-00 for the TimeDifference we need to see the query that was run.
0
manoj kumarAuthor Commented:
Beacause in my phpadmin it is old version :and i search i get that in one table u can not pass two timestamp(login:timestamp,logout timestamp):so i am taking default:
0
manoj kumarAuthor Commented:
so it is showing 0000.000.00.00.00
0
Julian HansenCommented:
I don't understand? The timedifference comes from your query
From this comment
SELECT 
  username, 
  intime, 
  outtime, 
  TIMEDIFF(outtime, intime) AS TimeDifference
FROM
  admin
WHERE
  userid =230

Open in new window


You don't need a TimeDifference column in your database - you calculate it when you do the query.
0
manoj kumarAuthor Commented:
Actually Julian Hansen u did not get My point:100.PNG
i want to displAY USER LOGIN LOGOUT TIME IN THIS FORMAT SHOW IN PICTURE
FOR EVERY USE ID,USERNAME,LOGINTIME,LOGOUTTIME,AND TOTAL lOGINDURATION  I AM DISPLAYING
0
Julian HansenCommented:
I did get your point exactly.

With respect to your data - you have a record where the outtime comes before the intime, however, lets work with it.

Given your data above running this query
SELECT 
  username, 
  intime, 
  outtime, 
  TIMEDIFF(outtime, intime) AS TimeDifference
FROM
  admin

Open in new window

You get these results
ss146.jpg
Please explain what you still need.

Also, PLEASE stop bolding your posts!
1
manoj kumarAuthor Commented:
i am displaying same as u show in your image:same it is
but in my table it contain only id,username,logintime,logouttime
When i Excute This Query$sql1="SELECT id,userid,intime, outtime, TIMEDIFF(outtime, intime) AS TimeDifference FROM admins WHERE userid =$userid";
man00.PNGso i want  all details with time diffrence as u show in your pictures
0
manoj kumarAuthor Commented:
if u look my result it's time diffrence show in one line:i donot want for each user i am showing total loginhours as show in your posted image
0
Julian HansenCommented:
My post above was run against a copy of your database - the query works.

Here is my source tabless147.jpgAs you can see - same as yours.
I ran the query from my previous post against that table and obtained the results I showed you. So the query is good.

I am guessing that there is something in your code that is not right - but you have not posted your latest code - so I cannot tell what you are doing.

How did you get that output? Did you run the query directly against the DB using PHPMyAdmin (or similar) or is it the output from a script?

If output from a script - POST THE SCRIPT.
0
manoj kumarAuthor Commented:
when i run query in my php admin it's correct
but in php how to print total timediffrence for specfic user:here my code is:
  <?php
                  
                    require_once 'database-config.php';
					if(isset($_POST['search']))
					 {
						$userid = $_POST['userid'];
					
					$sql1="SELECT id,userid,intime, outtime, TIMEDIFF(outtime, intime) AS TimeDifference FROM admins WHERE userid =$userid";
	
					  if($result = mysql_query($sql1)){
                          if(mysql_num_rows($result) > 0){
                            echo "<table class='table table-bordered table-striped'>";
                                echo "<thead>";
                                    echo "<tr>";
                                        echo "<th>id</th>";
										echo "<th>userid</th>";
                                    
                                        echo "<th>LoginTime</th>";
										echo "<th>LogoutTime</th>";
										echo "<th>TotalLoginhours</th>";
										
									
                                       
                                    echo "</tr>";
                                echo "</thead>";
                                echo "<tbody>";
                                while($row = mysql_fetch_array($result)){
                                    echo "<tr>";
                                        echo "<td>" . $row['id'] . "</td>";
										echo "<td>" . $row['userid'] . "</td>";
                                         echo "<td>" .$row['intime'] . "</td>";
										  echo "<td>" .$row['outtime'] . "</td>";
									
										      $datetime1 = new DateTime($row['intime']);
                                              $datetime2 = new DateTime($row['outtime']);
						                      $interval = $datetime1->diff($datetime2);
						                      $hours = $interval->d > 0 ? $interval->d * 24 + $interval->h : $interval->h;
										      
						                      echo $interval->format("{$hours}:%i:%s");
							  }
						 
										
                                      
                                        echo "<td>";
                                           
                                    echo "</tr>";
                                }
                                echo "</tbody>";                            
                            echo "</table>";
                            
                            mysql_free_result($result);
                        } else{
                            echo "<p class='lead'><em>No records were found.</em></p>";
                        }
                    }
						  
                     else{
                        echo "ERROR: Could not able to execute $sql. " . mysql_error();
                    }
					 
		
				
				
				
    ?>

Open in new window

1000.jpgi wants result in your posted image :instead i am geeting this Resultdman00.PNG
0
Julian HansenCommented:
You are mixing solutions.

I originally said it can be done with a query. You said you wanted PHP, I then gave you the PHP version and now you have mixed the two.

Your While loop should look like this
while($row = mysql_fetch_array($result)) {
  echo "<tr>";
  echo "<td>" . $row['id'] . "</td>";
  echo "<td>" . $row['userid'] . "</td>";
  echo "<td>" .$row['intime'] . "</td>";
  echo "<td>" .$row['outtime'] . "</td>";
  echo "<td>" .$row['TimeDifference '] . "</td>"; // JUST GET THE VALUE FROM THE QUERY
  echo "</tr>";
  
/* YOU DONT NEED THIS
  $datetime1 = new DateTime($row['intime']);
  $datetime2 = new DateTime($row['outtime']);
  $interval = $datetime1->diff($datetime2);
  $hours = $interval->d > 0 ? $interval->d * 24 + $interval->h : $interval->h;

  echo $interval->format("{$hours}:%i:%s");
*/  
}

Open in new window

0
manoj kumarCommented:
Undefined index: TimeDifference i:how to solve
0
manoj kumarAuthor Commented:
Actually i am quite confuse in this line echo "<td>" .$row['TimeDifference '] . "</td>";
in this line i am calculeting TimeDifference in sql query:but after while statement .$row search it for row in database (TimeDifference);but in my databse TimeDifference is not theere:it contain only id,username,logintime,logout time:So i am quite confuse In This line
0
Julian HansenCommented:
There is no TimeDifference column in the database.
You don't need a TimeDifference column in the database.
The TimeDifference is calculated from the intime and the outtime - you do it when you run the query.

You have posted that there was a problem but you did not post your code. I cannot tell how you have implemented the solution so I can't tell you how to fix it.

My implementation of the above works perfectly.
<?php
require_once 'dbconfig.php';
$userid = isset($_GET['id']) ? $_GET['id'] : false;

?>
<!doctype html>
<html>
<head>
<meta charset="UTF-8"/>
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=2.0, user-scalable=yes" />
<title>Title</title>
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css" rel="stylesheet" />
<link href="css/custom.css" rel="stylesheet" />
<style type="text/css">
</style>
</head>
<body>
<div class="wrapper container">
<?php
$conn = new mysqli($host, $user, $password, $db);
$sql1 = <<< QUERY
SELECT 
  `id`,
  `userid`,
  `intime`,
  `outtime`,
  TIMEDIFF(`outtime`, `intime`) AS `TimeDifference`
FROM
  `29078510`
QUERY;
if ($userid) {
  $sql1 .= <<< FILTER
WHERE
  `userid`={$userid}
FILTER;
}

if($result = $conn->query($sql1)){
  if($result){
    echo <<< TABLE
	<p>Click on User ID to filter table</p>
    <table class="table table-bordered table-striped">
      <thead>
        <tr>
          <th>id</th>
          <th>userid</th>
          <th>LoginTime</th>
          <th>LogoutTime</th>
          <th>TotalLoginhours</th>
        </tr>
      </thead>
      <tbody>
TABLE;
    while($row = $result->fetch_object()) {
      echo <<< ROW
        <tr>
          <td>{$row->id}</td>
          <td><a href="?id={$row->userid}">{$row->userid}</a></td>
          <td>{$row->intime}</td>
          <td>{$row->outtime}</td>
          <td>{$row->TimeDifference}</td>
        </tr>
ROW;
    }
    echo <<< TABLE
      </tbody>
    </table>
	<a href="?">Show All</a>
TABLE;

    $result->free();
  } 
  else {
    echo "<p class='lead'><em>No records were found.</em></p>";
  }
}
else {
  echo "ERROR: Could not able to execute $sql. " . mysql_error();
}
?>
</body>
</html>

Open in new window

Working sample here
In the above sample it will show all records by default. You can view individual records by clicking on the userid.

NB NB NB NB: I have coded the above the way I think this should be done.
I had to add my own db config - you will need to change to use yours
I am using MySQLi - instead of the deprecated MySQL library.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
manoj kumarAuthor Commented:
Thanks Julian Hansen "it's very helpfult to me
0
Julian HansenCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.