Zend Framework Magento Where statement returns error

Question

This is my Query in Magento.

        $where = "LIKE '%".$value."%'";
        foreach($tokens as $token) {
            $where .= " OR at_name.value LIKE '%$token%'";
        }

        $this->getCollection()->getSelect()
             ->joinInner(array('at_name' => 'mgmx_catalog_product_entity_text'), '(at_name.entity_id = at_visibility.entity_id)')
             ->where("at_name.value ?" ,$where);

If I run this Query, it will return an error

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''LIKE \'%REMAX GREY%\' OR at_name.value LIKE \'%REMAX%\' OR at_name.value LIKE \' at line 4

This is the query in Mysql.

SELECT COUNT(DISTINCT e.entity_id) 
FROM `mgmx_catalog_product_entity` AS `e`
INNER JOIN `mgmx_catalog_product_entity_int` AS `at_status` 
ON (`at_status`.`entity_id` = `e`.`entity_id`) AND 
(`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 0)
INNER JOIN `mgmx_catalog_product_entity_int` AS `at_visibility` 
ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND 
(`at_visibility`.`attribute_id` = '102') AND (`at_visibility`.`store_id` = 0)
INNER JOIN `mgmx_catalog_product_entity_text` AS `at_name` ON (at_name.entity_id = at_visibility.entity_id) WHERE (at_name.value 'LIKE 
\'%REMAX GREY%\' OR at_name.value LIKE \'%REMAX%\' OR at_name.value LIKE 
\'%GREY%\'')

The error is here somewhere

 (at_name.value 'LIKE 
\'%REMAX GREY%\' OR at_name.value LIKE \'%REMAX%\' OR at_name.value LIKE 
\'%GREY%\'')

If I remove the '' and the \ it will run normally. Like so

(at_name.value LIKE 
'%REMAX GREY%' OR at_name.value LIKE '%REMAX%' OR at_name.value LIKE 
'%GREY%')

I can't get rid of it as the zend framework is the one doing the '' and the backslashes. How do i deal with this?

Thanks.

I'm not familiar with zend-framework but that is not how the binding can be done. You need a placeholder for each value. The ? is for 1 value and is quoted. This results in your SQL statement being a string. If you did at_name.value = ? you wouldn't get the error but you also wouldn't get reults. — chris85
– chris85, Commented Oct 30, 2017 at 12:58

Vladimir Samsonov · Accepted Answer · 2017-10-30 13:27:42Z

1

Additional '' and escaping were added by method where(). But the second parameter is optional so simply append collected where statement to condition parameter like this:

$this->getCollection()->getSelect()
             ->joinInner(array('at_name' => 'mgmx_catalog_product_entity_text'), '(at_name.entity_id = at_visibility.entity_id)')
             ->where("at_name.value ".$where);

PS. still messing around with tokenized search in admin grid? :) I coded this quickly out of mind. There are even more elegant ways to implement this. check where() and _where() methods in class Zend_Db_Select

answered Oct 30, 2017 at 13:27

Vladimir Samsonov

1,3292 gold badges11 silver badges19 bronze badges

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

3 Comments

Pro_Newbie Over a year ago

Yes Sir. Still having a problem with this one Sir.

Vladimir Samsonov Over a year ago

;) don't forget to accept and up vote if my answer was helpful

Vladimir Samsonov Over a year ago

you may use print_r() to get contents of an object. but beware objects may be very large!

Collectives™ on Stack Overflow

Zend Framework Magento Where statement returns error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related