2

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;

Improve this question
6
  • how is using row data in place of a column name ok? Commented Jun 29, 2018 at 10:36
  • 2
    Print to console the contents of your variable $tblTeam and append the result to your question. Commented Jun 29, 2018 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? Commented Jun 29, 2018 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. Commented Jun 29, 2018 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. Commented Jun 29, 2018 at 11:29

2 Answers 2

Reset to default
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.

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

2 Comments

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 . . . You can leave out the on duplicate key clause and you will get an error.
0

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] . "'
)";
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.