Calulating hours and minutes from 2 times using php

PHP
1

Hi there,

I Have a php function called timedifference()

and this is its code.


require("dbconnect.php");
	$Query="SELECT TIMEDIFF($ti)-TIMEDIFF($e) WHERE who='$who'";
	echo $Query;
	$result = mysqli_query($con,$Query);
	if($result)
	{
		//
		$count=$result->num_rows;
		if($count>=0)
		{
			$days = mysqli_fetch_array($result);
			echo "".$days[0]."";
		}
		//
	}
	else
	{
		echo "Error".mysqli_error($con);
	}

The parameters being passed are these

					$who=$data['who'];
					$ti=$data['time'];$e=date("H:i");
					echo calcTime($ti,$e,$who);

Now why am i recieving these errors and how can i fix these errors below

'ErrorYou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':01)-TIMEDIFF(05:41) WHERE who=‘User a’

Now am i doing wrong with my query above or somthing is there somthing i need to fixed as i am only wanting to know the difference between the 2 minutes and hours?

Thanks,William

2

TIMEDIFF($ti)-TIMEDIFF($e
should be
TIMEDIFF(‘$ti’)-TIMEDIFF(‘$e’)

3

this function is senseless
these calculations supposed to be a part of main query, not php function

4

What do you suggest i do then?

5

Try to ask more clear question.
You mentioned 2 functions in your post, timedifference() and calcTime(), but provided no code for both of them

Also I doubt that you need PHP calculation, because your previous question was about mysql and you’re using $data[‘who’] variable which I suspect come from the database

6

the code above is from this function


function calcTime($ti,$e,$who)
{
	require("dbconnect.php");
	$Query="SELECT TIMEDIFF('$ti')-TIMEDIFF('$e') WHERE who='$who'";
	echo $Query;
	$result = mysqli_query($con,$Query);
	if($result)
	{
		//
		$count=$result->num_rows;
		if($count>=0)
		{
			$days = mysqli_fetch_array($result);
			echo "".$days[0]."";
		}
		//
	}
	else
	{
		echo "Error".mysqli_error($con);
	}
}

I want to calculate how many hours and minutes it has been between 2 hours and minutes

7

Does that make it any more clearer as to what i want?

8

You don’t need mysql for this
actually it’s the exercise from the elementary school
can you count the difference between 2:15 ans 8:30 manually?

9

i could do it manually but i want my php script to do it as the date is stored in the mysql database and i want to know how many hour it has been since then that is all.

10

You should use a solution from your previous topic, using mysql, right in the query that fetch your data.

if you want to do it with PHP, you have to alrorithmize your manual solution and write a code implementing this algorithm

11

the mysql TIMEDIFF function requires two parameters, not one

this type of confusion could easily be avoided with a very quick, very simple check of da manual

12

i was passing 2 parameters

13

no, you were calling the function twice but passing only one parameter each time –

SELECT TIMEDIFF($ti)-TIMEDIFF($e) ...
14

So somthing like this


function calcTime($ti,$e,$who)
{
    require("dbconnect.php");
    $Query="SELECT TIMEDIFF('$ti','$e')-TIMEDIFF('$e','$ti') WHERE who='$who'";
    echo $Query;
    $result = mysqli_query($con,$Query);
    if($result)
    {
        //
        $count=$result->num_rows;
        if($count>=0)
        {
            $days = mysqli_fetch_array($result);
            echo "".$days[0]."";
        }
        //
    }
    else
    {
        echo "Error".mysqli_error($con);
    }
}
15

Rudy, aren’t you going to tell him not to use Mysql for the PHP date calculations, are you?

16

depends on whether either of both of the parameters involved in calculating the difference actually come from the database :slight_smile:

i want my php script to do it as the date is stored in the mysql database and i want to know how many hour it has been since then
this sounds to me like a calculation of the difference between a stored datetime and the current datetime

i suppose you ~could~ pull the stored datetime from the database and then do the calculation in php, but you’d have to be application-code oriented as opposed to database-extract oriented to want to do it that way

:smiley:

17

only 1 of them comes from the database which is $ti the other is current time variable.

18

no, not like that

have you looked up the function in the manual yet?

19

yes i have and this is what it says

TIMEDIFF() returns expr1 – expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

mysql> SELECT TIMEDIFF(‘2000:01:01 00:00:00’,
-> ‘2000:01:01 00:00:00.000001’);
-> ‘-00:00:00.000001’
mysql> SELECT TIMEDIFF(‘2008-12-31 23:59:59.000001’,
-> ‘2008-12-30 01:01:01.000002’);
-> ‘46:58:57.999999’

What is that telling me i have to have the to parameters not like the above?

20

the manual is a little bit shaky on which one goes first, the earlier value or the later value

try it both ways and see which one gives a negative value

SELECT TIMEDIFF(yourtime,CURRENT_TIME) AS diff FROM yourtable

SELECT TIMEDIFF(CURRENT_TIME,yourtime) AS diff FROM yourtable

note that if your column is DATETIME instead of TIME, then you’ll want to use CURRENT_TIMESTAMP instead of CURRENT_TIME