Mysql insert gives error while Sql editor doesn't [duplicate]

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? It will make your life much easier and your code safer. — Dharman
– Dharman ♦, Commented Oct 25, 2020 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
– Soner from The Ottoman Empire, Commented Oct 25, 2020 at 19:41

GMB · Accepted Answer · 2020-10-25 19:17:29Z

0

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.

edited Oct 25, 2020 at 19:17

answered Oct 25, 2020 at 19:09

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nbk · Accepted Answer · 2020-10-25 19:09:11Z

0

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

answered Oct 25, 2020 at 19:09

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

Mysql insert gives error while Sql editor doesn't [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related