MYSQL Update Syntax error with string input

Question

my code below

    $count++;
    $yesstring = 'MATCH';

    echo $count . '. RESULT ' . $idcheck . ': ' . $phonecheck . ' was matched. <br />';

    $matchquery = sprintf("UPDATE `list` SET match = `%s` WHERE homephone = `%s` LIMIT 1",
        mysql_real_escape_string($yesstring),
        mysql_real_escape_string($phonecheck));

    $matchresult = mysql_query($matchquery);

    if (!$matchresult) {
        die("Invalid query: " . mysql_error());
    }

and this is my error

Invalid query: 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 = MATCH WHERE homephone = (999) 999-9999 LIMIT 1' at line 1

any help would be appreciated

juergen d · Accepted Answer · 2012-10-15 20:23:53Z

2

match is a reserved word in MySQL. Escape it with backticks:

 UPDATE `list` SET `match` = ...

answered Oct 15, 2012 at 20:23

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

1 Comment

Will English IV Over a year ago

That did it. I had no idea. Swapped out the column name and this all good to go.

tadman · Accepted Answer · 2012-10-15 20:24:19Z

1

You're using backticks when you should be using regular quotes. Backticks are reserved for escaping table or column names:

INSERT INTO `foo` VALUES ('value')

Although you're properly escaping your SQL, calling mysql_real_escape_string can prove to be a constant nuisance. Switching to mysqli or PDO would make writing correct SQL a lot easier in the long-run.

answered Oct 15, 2012 at 20:24

tadman

212k23 gold badges237 silver badges266 bronze badges

Collectives™ on Stack Overflow

MYSQL Update Syntax error with string input

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related