if语句在mysql查询中

Question

Hi I tried creating an if statement inside a mysql query but I dont know where I went wrong. What I tried to do is to deduct 8 from a column value if that column value is more than 8 hours. Here' what I have done so far

$sql="UPDATE timekeeping SET actualend = timestamp(NOW()), logoutdate = date(NOW()),
totalhours = ((SELECT sum(time_to_sec(timediff(actualend,actualstart))/3600) AS
'totalhours') - 1.50), 
excesstime = (if (totalhours > 8) 
   {
excesstime = (totalhours - 8);
   }
)  WHERE actualend IS NULL and fullname = '$loginuser[fullname]'";

So if I show a table like the one below

time in, timeout, totalhours, excesstime respetively

9:00:00 | 18:00:00 | 9 | 1 <------this should be the answer(1)

Sorry abut this but I'm so new in mysql as well as in php.

Answer 1

You're attempting to use a flow control statement in your query, where you actually need a flow control function. Use the if() function instead.

Try this:

$sql="UPDATE timekeeping SET actualend = timestamp(NOW()), logoutdate = date(NOW()),
totalhours = ((SELECT sum(time_to_sec(timediff(actualend,actualstart))/3600) AS
'totalhours') - 1.50), 
excesstime = if(totalhours > 8,totalhours-8,0)
  WHERE actualend IS NULL and fullname = '$loginuser[fullname]'";

The flow control statements are used in stored procedures, triggers, etc. and aren't valid in single-line queries.

MySQL reference here