Zend DB where clause error

Question

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

Syntax error or access violation Check you have your connection string correct. This could be caused by a wrong username/password. — vascowhite
– vascowhite, Commented Mar 14, 2012 at 16:14
@vascowhite got my connection working, thats not the problem. :\ — MGP
– MGP, Commented Mar 14, 2012 at 16:17
try $query->where("value = '?', $number);. I can't see anything else that could be wrong. — vascowhite
– vascowhite, Commented Mar 14, 2012 at 16:22
If you run the query in your database directly does it work? — Tim Fountain
– Tim Fountain, Commented Mar 14, 2012 at 16:42

Mouna Cheikhna · Accepted Answer · 2012-03-14 21:46:15Z

3

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);

answered Mar 14, 2012 at 21:46

Mouna Cheikhna

39.8k10 gold badges50 silver badges69 bronze badges

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

2 Comments

RockyFord Over a year ago

This one will probably need to be ->quoteIdentifier(), I'm pretty sure that VALUE is a keyword in Mysql, I know that VALUES is.

MGP Over a year ago

That's in fact the reason why my code was all buggy. Thanks for the answer! :)

Rob Allen · Accepted Answer · 2012-03-17 08:20:17Z

1

"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);

answered Mar 17, 2012 at 8:20

Rob Allen

12.8k1 gold badge43 silver badges52 bronze badges

Collectives™ on Stack Overflow

Zend DB where clause error

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related