SQL Command Syntax Errors

Question

I'm trying to write a SQL command to update some information in a database table, I'm using basic PHP variables to add this information into the correct columns.

I've written plenty of similar commands without any issue but for the life of me I cannot figure out why this keeps producing an error message.

Here's the query..

$sql = "UPDATE nexnum SET regexp = '{$regexp2}', regstatus = '{$regstatus}', expflag = '{$expflag}' WHERE duns = {$duns}";

I've tried this with and without the quotes surrounding the variables, I've tried breaking this up into three separate commands / queries. The variables all work correctly in my PHP code. Is this a syntax issue?

The error message usually contains some hints as to what's wrong. Also please use parameterized/prepared queries, using placeholders in your queries makes the queries secure and you don't have to worry about quotes and other stuff in the queries any more :) — JimL, Jan 07 '17 at 22:56
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 'regexp = '2017-12-08', regstatus = 'ACTIVE', expflag = '1' WHERE duns = 04687848' at line 1 — John C., Jan 07 '17 at 22:58
You must add single quote at last variable used as duns = {'$duns'} OR duns = '$duns' — Pavan Baddi, Jan 07 '17 at 22:59
@PavanBaddi OP states: *"I've tried this with and without the quotes surrounding the variables"* - that isn't what caused that error, something else did and is obvious. — Funk Forty Niner, Jan 07 '17 at 23:05
`near 'regexp` - the error told you what the problem was. @JohnC. — Funk Forty Niner, Jan 07 '17 at 23:06
$sql = "UPDATE nexnum SET regexp = '{$regexp2}', regstatus = '{$regstatus}', expflag = '{$expflag}' WHERE duns = '{$duns}'"; — John C., Jan 07 '17 at 23:08
How is this question a duplicate? I'm not using any reserved words in this query. — John C., Jan 07 '17 at 23:12
Please don't argue; RTM https://dev.mysql.com/doc/refman/5.5/en/keywords.html you see that "R" `REGEXP (R)`?? there. — Funk Forty Niner, Jan 07 '17 at 23:13
@JohnC. I reopened the question and posted my answer below. That other answer was steathly edited and I don't appreciate that type of behaviour. — Funk Forty Niner, Jan 07 '17 at 23:16

score 3 · Answer 1 · edited May 23 '17 at 12:24

Seeing that other answer was Stealth edited after seeing the comments thread, I have to be honest to say that I reopened the question and decided to post my answer.

Taken from the OP's comment:

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 'regexp = '2017-12-08', regstatus = 'ACTIVE'...

MySQL told you where the error starts: near 'regexp
REGEXP is MySQL reserved word:
https://dev.mysql.com/doc/refman/5.5/en/keywords.html

Notice the "R" around it => REGEXP (R), it stands for "Reserved".

Either you rename it to something else, or use ticks around it:

UPDATE nexnum SET `regexp`

"How is this question a duplicate? I'm not using any reserved words in this query. – John C."

A: It is a reserved word, and yes you are.

"I've tried this with and without the quotes surrounding the variables"

That wasn't what was causing your code to fail all this time, it was the reserved word.

And if that still doesn't let you update your database, you may have other issues which are unknown.

Use error reporting:

http://php.net/manual/en/function.error-reporting.php

And check for errors on the query (again) using the error handler for the MySQL API you're using which is also unknown.

Your code is also open to an SQL injection, use a prepared statement:

https://en.wikipedia.org/wiki/Prepared_statement

FYI:

REGEXP is used for regular expressions.
http://dev.mysql.com/doc/refman/5.7/en/regexp.html

So your choice of naming convention failed you.

SQL Command Syntax Errors

1 Answers1