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()."
"; break;}
// echo "
UPDATE 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().
index is a reserved word
UPDATE ResultsPredmet SET `index` = '10' WHERE name LIKE 'Vildur'