Insert in Mysql does not run

Asked

Viewed 193 times

-3

I’m having problems with SQL when making a small insertion in DB. I have a table with 4 fields:

id_match -> int
id_usuario1 -> int
id_usuario2 -> int
situation -> varchar(5)

When I do the following insert, it doesn’t enter the comic book:

INSERT INTO match VALUES(NULL, '$usuario1', '$usuario2', 'open')

What is wrong?

mysql sql

  • which error it presents ?

  • @wryel Running on the application shows no error, but running on phpMyAdmin: "#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 'match VALUES (null, '5', '0', 'open')' at line 1"

2 answers

4


Your mistake is because match be a reserved word. This means that in order to be used as an identifier (column or table name, for example) it needs a special treatment.

From your comment I saw that you have already solved the problem. Here, however, is the answer for future reference.

INSERT INTO `match` VALUES(NULL, '$usuario1', '$usuario2', 'open')

To use a reserved word as a table name, column or identify an index you can use one of two solutions;

  • Using "`" severe accent, or

  • If ANSI_QUOTES in SQL mode is enabled you can use quotes (")

  • 1

    Thanks for the @Bruno explanation. I even looked for reserved words in sql language on google, but I did not find anything related to match. But thanks a lot for the tip.

  • No problem @Gustavosevero. The word match in my reply has a link to a list of reserved words in Mysql.

3

Just make it work:

INSERT INTO match VALUES(NULL, 5, 0, 'open')

You’re trying to record strings a columns of the type int. Is not possible.

With the editing it looks like it’s PHP code, so the correct would be something like this:

INSERT INTO match VALUES(NULL, $usuario1, $usuario2, 'open')

But I can’t guarantee because PHP code information is missing.

Finally there is an error in the table name. By the comment added below it was clear what the error was. AP resolved to "escape" the table name which is a reserved SQL word. This way:

INSERT INTO `match` VALUES(NULL, $usuario1, $usuario2, 'open')

I put in the Github for future reference.

  • these data are in variables... Is it possible to do this? INSERT INTO match values(NULL, $variavel1, $variavel2, '$variavel3')?

  • Look at the bug that appeared in phpMyAdmin, @bigown: "#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 'match VALUES(NULL, 5, 0, 'open')' at line 1"

  • 1

    If they are in variables, your question is wrong. It is possible to do what you want but you have to see the whole context. Now you’re saying it’s something totally different than what’s in the question. The right one was to remove this question but still gives you time to tidy up and put everything as effectively you are using.

  • See if the command is right now in the question @bigown

  • Look! I made a small change to the sql script that worked, but so far I didn’t understand how, because NEVER, NEVER had to do this. I had to put " ", crase, antes e depois do nome da tabela:

INSERT INTOmatch` VALUES(NULL, '$usuario1', '$usuario2', 'open')

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.