CODEWITHSUNDEEP
mysqlzend-frameworkzend-db
MGP
MGP

Reputation: 653

Zend DB where clause error

EDIT:

$query->where('`value` = ?', $number); It seems that does the job. I still don't know why it won't work under normal conditions, but it's a work around.. still looking for the right answer!

I'm trying to query a DB with a simple:

$number = 4;
$query = $this->select();
$query->where('value = ?', $number);
$row = $this->fetchRow($query);

But for some reason I constantly got this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 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 'value = 4) LIMIT 1' at line 1

When I do my assemble to see the query string:

SELECT `mighty_table`.* FROM `mighty_table` WHERE (value = 4)

My column name its not escaped..

Should Zend DB do that? :| It's strange since i use this same method in other projects and it always works..

Upvotes: 0

Views: 446

Answers (2)

Rob Allen
Rob Allen

Reputation: 12778

"value" is indeed a reserved word in MySQL. As a result you need to escape it using back ticks.

I would expect this to work:

$fieldName = $this->getAdapter()->quoteIdentifier('value');
$query->where($fieldName = ?", $number);

Upvotes: 1

Mouna Cheikhna
Mouna Cheikhna

Reputation: 39678

From zend manual :

Note: The values and identifiers in the SQL expression are not quoted for you. If you have values or identifiers that require quoting, you are responsible for doing this. Use the quote(), quoteInto(), and quoteIdentifier() methods of the database adapter.

So for example you can use quoteInto :

$number = 4;
$query = $this->select();
$where = $this->getAdapter()->quoteInto('value = ?', $number);
$query->where($where);
$row = $this->fetchRow($query);

Upvotes: 3

Related Questions