Mysql insert gives error while Sql editor doesn't

Question

**

(: What's wrong with the question to give -1 ? Please briefly leave its reason.

**

When I try to run the sql query, it works on a sql interpreter, DBeaver. However, PHP gives the error.

Error: INSERT INTO TestSistem.Attendance (DayDate,Presence,StudentId,TeacherId) VALUES ('2020-10-25 21:09:57', '1' ,'4' ,'3'); INSERT INTO TestSistem.Attendance (DayDate,Presence,StudentId,TeacherId) VALUES ('2020-10-25 21:09:57', 0 ,'3' ,'3');
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT INTO TestSistem.Attendance (DayDate,Presence,StudentId,TeacherId) VALUES ' at line 1

Php part

$sql = "INSERT INTO TestSistem.Attendance (DayDate,Presence,StudentId,TeacherId) VALUES ('2020-10-25 21:09:57', '1' ,'4' ,'3'); INSERT INTO TestSistem.Attendance (DayDate,Presence,StudentId,TeacherId) VALUES ('2020-10-25 21:09:57', '0' ,'3' ,'3');";
if ($mysqli->query($sql) === TRUE) {
    echo "New record(s) created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $mysqli->error;
}

Stop checking for mysqli errors manually! Enable error reporting instead. [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) It will make your life much easier and your code safer. — Dharman, Oct 25 '20 at 19:23
@Dharman Thank you for the advice . I don't know php in a good manner. However, I'll heed in next rounds. — Soner from The Ottoman Empire, Oct 25 '20 at 19:41

GMB · Accepted Answer · 2020-10-25T19:17:29.050

mysqli::query() does not support running multiple statements - you would need mysqli::multi_query() instead.

But here, for this simple insert passing literal values, I would recommend the following syntax, which is a single statement:

INSERT INTO TestSistem.Attendance (DayDate,Presence,StudentId,TeacherId) 
VALUES 
    ('2020-10-25 21:09:57', 1 , 4 , 3),
    ('2020-10-25 21:09:57', 0 , 3 , 3);

Note that I modified your code by removing the single quotes around the numbers; presumably, the corresponding columns have int datatype: if so, it is simpler and more efficient to pass the values as literal numbers.

score 0 · Answer 2 · answered Oct 25 '20 at 19:09

This is only fpr 1 qiery.

If you want to insert them this way you need multi_query

see https://www.php.net/manual/en/mysqli.multi-query.php

Then you can also make,l this as one query

INSERT INTO TestSistem.Attendance (DayDate,Presence,StudentId,TeacherId) 
VALUES 
    ('2020-10-25 21:09:57', '1' ,'4' ,'3'),
    ('2020-10-25 21:09:57', 0 ,'3' ,'3');

And then you can use prepared statements with parameters multiple times

see https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php

Mysql insert gives error while Sql editor doesn't

2 Answers2