SQL Error :1064 You have an error in your SQL syntax

Question

I have a Table called users like that:

    ____         ____            __________        _____________
   |    |       |     |          |         |       |           |
   | id |       | name|          |firstCon |       | secondCon |
   |____|       |_____|          |________ |       |___________|

     1           john               true               false

     2           mark               false              false

I want to change the firstCon and secondCon values with true or false.

So I'm using the following query:

$sql = "UPDATE users SET ? = ? WHERE name = ?";
$query->bind_param($condition, $value, $name);

Where $condition is either firstCon or secondCon , $value = true/false , $name is the name of the user.

I get that error:

1064 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 '? = ? WHERE name = ?'

I'm using that method as I don't know which condition was selected , So I depend on the name.

any reason why you want to bind column names? Also FYI you can't do that — Rotimi, Jun 13 '18 at 15:05
Possible duplicate of [How can I fix MySQL error #1064?](https://stackoverflow.com/questions/23515347/how-can-i-fix-mysql-error-1064) — Daniel W., Jun 13 '18 at 15:06
@AkintundeOlawale , These condition values come from inputs , I don't know which one is clicked , I just know the name and the condition name then , So I can't just put a static column there as I don't know which one of them should be updated — , Jun 13 '18 at 15:12
its a bad idea to collect table and or column names via a form or any user-input. You should look for an alternative. Maybe you should also add your end goal to your question? — Rotimi, Jun 13 '18 at 15:15
@AkintundeOlawale , My end goal is to update the table with the chosen condition — , Jun 13 '18 at 15:27

Gordon Linoff · Accepted Answer · 2018-06-14T01:44:50.897

0

You cannot pass column names (or other identifiers) as parameters. Here is an alternative method, that doesn't require munging the query string:

UPDATE users 
    SET firstcon = (case when ? = 'firstcon' then ? else firstcon end),
        secondcon = (case when ? = 'secondcon' then ? else secondcon end)
    WHERE name = ?;

Note: This has more placeholders. It might be simpler if you pass the parameters in as named parameters:

UPDATE users 
    SET firstcon = (case when :which = 'firstcon' then :value else firstcon end),
        secondcon = (case when :which = 'secondcon' then :value else secondcon end)
    WHERE name = :name;

edited Jun 14 '18 at 01:44

answered Jun 13 '18 at 15:06

Gordon Linoff

1,242,037
58
646
786

I tried the first one and added a third condition , But it game me an error – Jun 13 '18 at 15:18
and the second one gave me this error with 3 conditions : `:which = 'firstcon' then :value else firstcon end), secondcon = (case when :which =' at line 2` – Jun 13 '18 at 15:56
Thanks the problem is resolved , I used the first one, But there should be `end` at the end of the second check – Jun 13 '18 at 17:41
`UPDATE users SET firstcon = (case when ? = 'firstcon' then ? else firstcon end), secondcon = (case when ? = 'secondcon' then ? else secondcon end) WHERE name = ?` – Jun 13 '18 at 17:42

SQL Error :1064 You have an error in your SQL syntax

1 Answers1