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

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 https://dev.mysql.com/doc/refman/8.0/en/insert-select.html — user3783243, Jun 29 '22 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, Jun 29 '22 at 19:18
Here you go: https://stackoverflow.com/questions/5874383/invalid-parameter-number-parameter-was-not-defined-inserting-data — easleyfixed, Jun 29 '22 at 19:28
Why are you using `:parameter` when the values are coming from the `SELECT`, not query parameters? — Barmar, Jun 29 '22 at 19:36

score 0 · Answer 1 · answered Jun 29 '22 at 19:33

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! — John Lyons, Jun 29 '22 at 20:53

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

1 Answers1