MYSQL error in snytax connecting to API via postman but data is displayed correctly [duplicate]

Question

When connecting to Postman, I am able to insert data correctly. Here is my query in postman:

{<br> "Company2_ID_Company2": "120",<br> "Company2_DHS6_ID": "144", <br> "Company2_ID_Main": "153",<br> "Retake_ID_Main": "120", <br> "Retake_ID": "120", <br> "Retake1_ID": "120",<br> "Retake1_ID_Retake1": "120",<br> "Pass": "Yes"<br> }

However, the error is

 <br /> <b>Fatal error</b>: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 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 <b>'Company2_ID_Company2 = '120', Company2_DHS6_ID = '144', Company2_ID_Main = '153''</b> at line 17 in C:\XAMPP\htdocs\API_Demo2\Models\Post.php:212 Stack trace: #0 C:\XAMPP\htdocs\API_Demo2\Models\Post.php(212): PDOStatement-&gt;execute() #1 C:\XAMPP\htdocs\API_Demo2\Post\Create.php(43): Post-&gt;create() #2 {main} thrown in <b>C:\XAMPP\htdocs\API_Demo2\Models\Post.php</b> on line <b>212</b><br />

Whats strange is that whenever I change the numbers in the query, they show up correctly in the error.

Here is my PHP code with the entire query:

$query = 'SELECT sandbox.company2.Company2_ID_Company2 AS Company2_ID_Company2, sandbox.company2.Company_DHS6_ID AS Company2_DHS6_ID, sandbox.main.Retake_ID_Main AS Retake_ID_Main, sandbox.main.Company2_ID_Main AS Company2_ID_Main, sandbox.retake.Retake_ID AS Retake_ID, sandbox.retake.Retake1_ID AS Retake1_ID, sandbox.retake1.Retake1_ID_Retake1 AS Retake1_ID_Retake1, sandbox.retake1.Pass AS Pass FROM ' . $this->table . ' company2 JOIN main ON sandbox.Company2.Company2_ID_Company2 = sandbox.main.Company2_ID_Main JOIN retake ON sandbox.main.Retake_ID_Main = sandbox.retake.Retake_ID JOIN retake1 ON sandbox.retake.Retake1_ID = sandbox.retake1.Retake1_ID_Retake1' . 'INSERT INTO ' . $this->table . ' SET Company2_ID_Company2 = :Company2_ID_Company2, Company2_DHS6_ID = :Company2_DHS6_ID, Company2_ID_Main = :Company2_ID_Main, Retake_ID_Main = :Retake_ID_Main, Retake_ID = :Retake_ID, Retake1_ID = :Retake1_ID, Retake1_ID_Retake1 = :Retake1_ID_Retake1, Pass = :Pass';

The insert syntax is inverted. INSERT INTO table (columns) select values... See dev.mysql.com/doc/refman/8.0/en/insert-select.html — user3783243
– user3783243, Commented Jun 29, 2022 at 19:12
Many thanks friend! However, I got this strange error afterward: Fatal error: Uncaught PDOException: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\XAMPP\htdocs\API_Demo2\Models\Post.php:224 Stack trace: #0 C:\XAMPP\htdocs\API_Demo2\Models\Post.php(224): PDOStatement->execute() #1 C:\XAMPP\htdocs\API_Demo2\Post\Create.php(43): Post->create() #2 {main} thrown in C:\XAMPP\htdocs\API_Demo2\Models\Post.php on line 224 — John Lyons
– John Lyons, Commented Jun 29, 2022 at 19:18
Why are you using :parameter when the values are coming from the SELECT, not query parameters? — Barmar
– Barmar, Commented Jun 29, 2022 at 19:36

Bill Karwin · Accepted Answer · 2022-06-29 19:33:10Z

You should study the INSERT...SELECT syntax more carefully. If you use INSERT...SELECT, then you don't need a SET clause or a VALUES clause for your INSERT statement, nor do you need parameter placeholders.

I'd code it this way:

$query = <<<SQL_END INSERT INTO `{$this->table}` ( Company2_ID_Company2, Company2_DHS6_ID, Company2_ID_Main, Retake_ID_Main, Retake_ID, Retake1_ID, Retake1_ID_Retake1, Pass) SELECT sandbox.company2.Company2_ID_Company2 AS Company2_ID_Company2, sandbox.company2.Company_DHS6_ID AS Company2_DHS6_ID, sandbox.main.Company2_ID_Main AS Company2_ID_Main, sandbox.main.Retake_ID_Main AS Retake_ID_Main, sandbox.retake.Retake_ID AS Retake_ID, sandbox.retake.Retake1_ID AS Retake1_ID, sandbox.retake1.Retake1_ID_Retake1 AS Retake1_ID_Retake1, sandbox.retake1.Pass AS Pass FROM `{$this->table}` company2 JOIN main ON sandbox.Company2.Company2_ID_Company2 = sandbox.main.Company2_ID_Main JOIN retake ON sandbox.main.Retake_ID_Main = sandbox.retake.Retake_ID JOIN retake1 ON sandbox.retake.Retake1_ID = sandbox.retake1.Retake1_ID_Retake1 SQL_END;

The columns named in the parentheses after the table name must be in the same order as the columns returned by the select-list of the SELECT query.

I also make use of PHP heredoc syntax to format the string, and also variable expansion inside the heredoc. I also put the table name in back-ticks just in case the name conflicts with an SQL reserved keyword or contains special characters.

I wanted to thank everyone for helping me out. I learned a lot!

Collectives™ on Stack Overflow

MYSQL error in snytax connecting to API via postman but data is displayed correctly [duplicate]

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related