Update end time and duration in same time, using TIMESTAMPDIFF PHP MYSQL

Question

I want to update 'duration' and 'end_time' field in same query/action. 'end_time' field filled current time, 'duration' field filled from different minute between 'start' and 'end_time' field. when i execute this update, the result in 'duration' field is 0. How to get end_time and duration in same time.

this is the php code :

<?php
include("koneksi.php");
$id     = $_GET['id'];
$start  = gmdate("Y-m-d H:i:s", time()+60*60*7);
$end_time   = gmdate("Y-m-d H:i:s", time()+60*60*7);
$duration  = $_POST['duration'];


$query = "update billing set end_time='$end_time', duration = TIMESTAMPDIFF(MINUTE, '$start', '$end_time') where id='$id'";

$result = mysql_query($query);
if ($result){
echo '<script language="javascript">window.location = "../?p=los"</script>'; 
} ?>

$id is coming from GET method and $duration is coming from POST method. A form cant be submitted using both method at a time. — rack_nilesh, Jul 15 '14 at 05:01
@rack_nilesh echo "STOP , thats code on my html, when i click that link it will return to updatelos.php , which code like post above. — bangkosim, Jul 16 '14 at 03:21
You should not set the $start value here. Here $start and $end_time represent same value that is current time. That's why you get duration as 0. — rack_nilesh, Jul 16 '14 at 04:52

score 0 · Accepted Answer · edited May 23 '17 at 11:53

0

Duration is being set to 0 because $start and $end_time are the same.

Consider your code:

$start  = gmdate("Y-m-d H:i:s", time()+60*60*7);
$end_time   = gmdate("Y-m-d H:i:s", time()+60*60*7);

$query = "... TIMESTAMPDIFF(MINUTE, '$start', '$end_time') ...";

Because $start == $end_time, the difference will always be 0.

You probably want to use the value of start_time already stored in the database, not the recently created php variable $start. Perhaps something like this:

$query = "
 update billing set 
 end_time='$end_time', 
 duration = TIMESTAMPDIFF(MINUTE, start_time, '$end_time') 
 where id='$id'
";

where start_time is a column in the database.

Note: mysql_* functions are deprecated, and you are susceptible to SQL injection. Consider using mysqli or pdo and utilize prepared statements.

edited May 23 '17 at 11:53

Community

1
1

answered Jul 15 '14 at 05:02

Mark Miller

7,442
2
16
22

if not $start == $end_time, it will save nothing in 'duration' field when i execute. or you can help me to correct all my code above. thanks – bangkosim Jul 15 '14 at 08:05
@bangkosim In your table `billing` you have a field called `end_time`. You must also have a field called `start_time`, right? That is what you want to use in `TIMESTAMPDIFF`, ***not*** `$start`. – Mark Miller Jul 15 '14 at 18:26
i already have that field and i named it with 'start'. in the code above I using $start to call that field from database. thanks – bangkosim Jul 15 '14 at 22:20

score 0 · Answer 2 · answered Jul 20 '14 at 11:09

thank you for your answer, and this is my fix code

$query = "update billing set , end_time='$end_time', 
 duration = TIMESTAMPDIFF(MINUTE, start, '$end_time'),
 cost = TIMESTAMPDIFF(MINUTE, start, '$end_time') * (SELECT basiccost from costed where id ='1')
 where id='$id'";

Update end time and duration in same time, using TIMESTAMPDIFF PHP MYSQL

2 Answers2