MySQL UPDATE syntax error with variables

Question

Long story short, I'm trying to write a PHP code that will parse a text file into MySQL queries. Everything works fine except for the queries, which consist of UPDATE statements.

The entire code is kinda long, but if you want to see it - http://pastebin.com/xVR6ArD0

Here is just the part which is problematic :

while ($i<=$no_collumns)
           {
             $j = $i-1;
             if (!
                  mysql_query 
                  ("UPDATE ResultsPredmet 
                   SET ${parsed_collumns[$i]} = '${parsed_words[$j]}' 
                   WHERE ${parsed_first_collumn} LIKE '${parsed_first_word}'")
                ) 
              {echo mysql_error()."\n"; break;}
            // echo "\nUPDATE ResultsPredmet SET ${parsed_collumns[$i]} = '${parsed_words[$j]}' WHERE ${parsed_first_collumn} LIKE \"${parsed_first_word}\"";
         $i++;
         }

... where $parsed_collumns and $parsed_words are arrays of strings and $parsed_first_collumn and $parsed_first_word are strings.

I tried all combinations of quotes and escapes for the variables. I tried putting them in double quotes and escaping them, or double quotes and concatenating them, then maybe i thought it was the fact that I was comparing strings via the '=' operator so i tried with 'LIKE'. I googled for several hours and everywhere people said to use single quotation marks for variables so I tried that too and it didn't work.

In the end I echoed the queries and I get:

UPDATE ResultsPredmet SET grade = '10' WHERE name LIKE "Vildur"
UPDATE ResultsPredmet SET index = '117/2010' WHERE name LIKE "Vildur"
Updating table. 
UPDATE ResultsPredmet SET grade = '6' WHERE name LIKE "NinoDoko"
UPDATE ResultsPredmet SET index = '132/2011' WHERE name LIKE "NinoDoko"
Updating table. 
UPDATE ResultsPredmet SET grade = '10' WHERE name LIKE "Koco"
UPDATE ResultsPredmet SET index = '130/2011' WHERE name LIKE "Koco"
Done.

Which seem fairly fine to me. Other queries I got were the same only with the names with single quotes around them, or with no quotes or any other combinations.

The errors I get are :

Updating table. 
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 'index = '117/2010' WHERE name LIKE 'Vildur'' at line 1
Updating table. 
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 'index = '132/2011' WHERE name LIKE 'NinoDoko'' at line 1
Updating table. 
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 'index = '130/2011' WHERE name LIKE 'Koco'' at line 1

Apparently, the server that I'm using is MariaDB 5.5, but after a bit of research I figured it would be similar to just generic MySQL, though I might be completely off. The "Updating table." is just a random echo in my code. I've also tried the query without indenting it, still got the same errors. The values I get for grade and index are strings - or at least I hope so, since I'm getting them with explode().

Replace double quotes by simple quotes (LIKE "Vildur" -> LIKE 'Vildur'). Plus, check that your grade field is really of varchar type, otherwise remove quotes around its values. — xlecoustillier
– xlecoustillier, Commented Jun 19, 2013 at 12:42
Obligatory: The mysql_* functions will be deprecated in PHP 5.5. It is not recommended for writing new code as it will be removed in the future. Instead, either the MySQLi or PDO and be a better PHP Developer. — Jason McCreary
– Jason McCreary, Commented Jun 19, 2013 at 12:43
@X.L.Ant I tried that as well, it still doesn't work. As I said, I tried all (well, most) combinations of quotes, but no dice. — Nino Dokoskiot
– Nino Dokoskiot, Commented Jun 19, 2013 at 12:47
Anyway, double quotes here are WRONG. Maybe there's another syntax error (see my updated comment), but this one is patent. — xlecoustillier
– xlecoustillier, Commented Jun 19, 2013 at 12:48
@JasonMcCreary I am aware, and I guess that will be my resort. — Nino Dokoskiot
– Nino Dokoskiot, Commented Jun 19, 2013 at 12:50

amigura · Accepted Answer · 2013-06-19 12:53:08Z

0

index is a reserved word

UPDATE ResultsPredmet SET `index` = '10' WHERE name LIKE 'Vildur'

http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html

answered Jun 19, 2013 at 12:53

amigura

5393 silver badges6 bronze badges

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

2 Comments

user925885 Over a year ago

Column names should be surrounded with backticks `, not '.

Nino Dokoskiot Over a year ago

I absolutely love it when something so trivial is the reason I'm bashing my head off a wall for 3 hours. Thank you kindly good sir.

Collectives™ on Stack Overflow

MySQL UPDATE syntax error with variables

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related