Syntax error in SQL with WHERE NOT EXISTS when parsing with PHP

Question

At first I tried to do the SQL-Query with IF NOT EXISTS, but I got always a Syntax Error and didn't really understand why. But typing in my title stackoverflow gave me some questions concerning the same issue. Interesting enough Google wasn't able to find them... Whatever, so I changed the query into what Mike suggested in this Question.

Now I have the following code snippet:

$tblTeam = "INSERT INTO tblTeam (nameTeam, nationID) 
    SELECT * FROM (SELECT '" . $match_Team[1] . "', (SELECT nationID FROM tblNation WHERE nameNation = '" . $nation_name[1] . "')) AS tmp
    WHERE NOT EXISTS (
        SELECT nameTeam FROM tblTeam WHERE nameTeam = '" . $match_Team[1] . "'
    ) LIMIT 1; 

    INSERT INTO tblTeam (nameTeam, nationID) 
    SELECT * FROM (SELECT '" . $match_Team[2] . "', (SELECT nationID FROM tblNation WHERE nameNation = '" . $nation_name[2] . "')) AS tmp
    WHERE NOT EXISTS (
        SELECT nameTeam FROM tblTeam WHERE nameTeam = '" . $match_Team[2] . "'
    ) LIMIT 1;";

If I run that query with pure SQL syntax directly in phpMyAdmin everything is fine, I get my entries in tblTeam. But as soon as I try to do that by PHP I get the following Syntax Error:

Error description tblTeam:

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 'INSERT INTO tblTeam (nameTeam, nationID) SELECT * FROM (SELECT 'B' at line 7

I'm a bit confuse now because I don't see what's the issue. In my used example for debugging $match_Team[1] has the value Serbia, $match_Team[2] the value Brazil. Same goes for $nation_name[1] and $nation_name[2] respectively.

EDIT: Because @FDavidov asked for it, here's the content of $tblTeam:

INSERT INTO tblTeam (nameTeam, nationID) SELECT * FROM (SELECT 'Serbia', (SELECT nationID FROM tblNation WHERE nameNation = 'Serbia')) AS tmp WHERE NOT EXISTS (SELECT nameTeam FROM tblTeam WHERE nameTeam = 'Serbia') LIMIT 1; INSERT INTO tblTeam (nameTeam, nationID) SELECT * FROM (SELECT 'Brazil', (SELECT nationID FROM tblNation WHERE nameNation = 'Brazil')) AS tmp WHERE NOT EXISTS (SELECT nameTeam FROM tblTeam WHERE nameTeam = 'Brazil') LIMIT 1;

Print to console the contents of your variable `$tblTeam` and append the result to your question. — FDavidov, Jun 29 '18 at 10:36
I am really not too experienced but shouldn't you select column_name instead of select $match_team[2] ? does match_team[2] have the column name inside it? if yes, how are you comparing it in the end of your query? — Farhan Qasim, Jun 29 '18 at 10:37
How are you trying to run the query? Your trying to run 2 statements and not all calls support multi-query strings. — Nigel Ren, Jun 29 '18 at 10:40
@FDavidov that actually helped for *one* of the issues. $nation_name wasn't containing the actual nation. However, there's still a syntax error with the SQL query. Will add the content of `$tblTeam` into my question. — S1dy, Jun 29 '18 at 11:29
@S1dy, readers of your question will appreciate if you make an effort and include your code in a readable form. See this example of mine: https://stackoverflow.com/questions/50274021/sql-server-dynamic-sql-with-string-including-variables-declaration — FDavidov, Jun 29 '18 at 17:36

score 1 · Answer 1 · answered Jun 29 '18 at 10:43

1

Let the database do your data validation for you. If you want to ensure that name is unique, add a unique index/constraint:

create unique index unq_tblTeam_nameTeam on tblTeam(nameTeam);

Then, when you load the data, you can ignore the error using on duplicate key update:

insert into tblTeam (nameTeam, nationId)
    select ?, n.nationId
    from tblNation n
    where n.nameNation = ?
    on duplicate key update nameTeam = values(nameTeam);

The on duplicate key clause does nothing -- a no-op. But it does prevent an error.

The ? are placeholders for parameters. You should learn to write queries with parameters and not munge query strings with such values -- that can lead to errors and SQL injection attacks.

answered Jun 29 '18 at 10:43

Gordon Linoff

1,122,135
50
484
624

Well, I try to learn, but since it's just a hobby for me I'm maybe not the fastest one :) Is it possible to avoid to update `nameTeam` after the `on duplicate key` clause? I really just need to check if the values I get by PHP are already in the respective mySQL tables. No need to update them. – S1dy Jun 29 '18 at 11:49
@S1dy . . . You can leave out the `on duplicate key` clause and you will get an error. – Gordon Linoff Jun 29 '18 at 12:05

score 0 · Accepted Answer · answered Jul 01 '18 at 12:51

So, after 2 days of freaking out because of the issue I finally got solutions - actually there were quite a few issues, not just one.

First of all, $nation_name had the wrong string, not the actual nation. Still, there would still be the Syntax Error.

Next I found out that mysqli_query() doesn't allow two queries in the same $tblTeam. That explained why the SQL code worked in phpMyAdmin, but not with PHP. However, there exists mysqli_multi_query() for such cases. Still, I would have problems with parsing. I followed quite a few examples that I found all over the net, but it would never work. I guess the Syntax Error I always got masked the actual error here, too.

The final solution was now to split it in two separate queries. It required a few more code lines, but it was the easiest way and works fine. Now the code snippets looks this:

$tblTeam1 = "INSERT INTO tblTeam (nameTeam, nationID) 
SELECT '" . $match_Team[1] . "', (SELECT nationID FROM tblNation WHERE nameNation = '" . $nation_name[1] . "')
WHERE NOT EXISTS (
    SELECT nameTeam FROM tblTeam WHERE nameTeam = '" . $match_Team[1] . "'
)";

$tblTeam2 = "INSERT INTO tblTeam (nameTeam, nationID) 
SELECT '" . $match_Team[2] . "', (SELECT nationID FROM tblNation WHERE nameNation = '" . $nation_name[2] . "')
WHERE NOT EXISTS (
    SELECT nameTeam FROM tblTeam WHERE nameTeam = '" . $match_Team[2] . "'
)";

Syntax error in SQL with WHERE NOT EXISTS when parsing with PHP

2 Answers2