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.