updating the MySQL database with time by using mysqli_multi_query (php)

Question

I'm using the below code in order to insert a text into the db and also to insert the current time (time only not date)

$time = date("h:i");
$query = "UPDATE a_2020 SET done = 'yes' WHERE id = '2' ;";
$query .= "UPDATE a_2020 SET nowTime = $time WHERE id = '1' ";
$result = mysqli_multi_query($con,$query);
echo mysqli_error($con);

but I'm getting the below error each time without updating the database:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE a_2020 SET nowTime = 09:23 WHERE id = '1' at line 1 

I tried to change the column type to datetime, timestamp, text ...etc and without any results, after you solve the issue I want also to add another pastTime and want to get the variance between the nowTime and pastTime.

Answer 1

You need quotes around the time.

$time = date("h:i");
$query = "UPDATE a_2020 SET done = 'yes' WHERE id = '2' ;";
$query .= "UPDATE a_2020 SET nowTime = '$time' WHERE id = '1' ";
$result = mysqli_multi_query($con,$query);
echo mysqli_error($con);

Note that in my experience, there's rarely a good reason to use mysqli_multi_query(). It provides little benefit and just makes things more complicated. Just call mysqli_query() twice.

And it's generally better to use prepared statements, which aren't available with mysqli_multi_query(). This avoids quoting problems and also protects against SQL injection.

Answer 2

I think using CASE is the better solution for you...

UPDATE a_2020 
SET
  done = CASE 
    WHEN id = '2' 
      THEN 'yes' 
    ELSE done 
    END
  , nowTime = CASE 
    WHEN id = '1' 
      THEN $time 
    ELSE nowTime 
    END;

Answer 3

Do not use mysqli_multi_query()!

If you want to execute two queries like this, you should use prepared statements. In your case, because you have no parameter to be passed in the first query you could use query().

$time = date("h:i");

$con->query("UPDATE a_2020 SET done = 'yes' WHERE id = '2'");

$stmt = $con->prepare("UPDATE a_2020 SET nowTime = ? WHERE id = '1'");
$stmt->bind_param('s', $time);
$stmt->execute();

Please also read: How to get the error message in MySQLi?