CODEWITHSUNDEEP
phpmysqlsql
John C.
John C.

Reputation: 43

SQL Command Syntax Errors

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?

Upvotes: 0

Views: 74

Answers (1)

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

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'...

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:

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:

FYI:

So your choice of naming convention failed you.

Upvotes: 3

Related Questions