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.

Barmar · Accepted Answer · 2020-03-24T03:16:43.807

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.

edited Mar 24 '20 at 03:16

answered Mar 24 '20 at 01:34

Barmar

741,623
53
500
612

thank you for your answer, but I want to use my first code style if you can, I added the semicolon but unfortunate I didn't get any results – Gerardo Mar 24 '20 at 01:41
@user13113208 It is insecure. `mysqli_multi_query` can't be used with parameterize queries. Did you quote the datetime string? The update doesn't show that change. See the latter part of Barmar's first comment, `You also need to put quotes around the time.`. – user3783243 Mar 24 '20 at 01:55

score 0 · Answer 2 · answered Mar 24 '20 at 02:11

0

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;

answered Mar 24 '20 at 02:11

Antony Jack

480
2
16

score 0 · Answer 3 · answered Mar 25 '20 at 18:44

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?

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

3 Answers3