SQL query error in Magento grid

Question

I made a grid in magento backend module but there's an 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 'procedure LIKE '%1%')' at line 1
0 \lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
1 \lib\Zend\Db\Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
2 \lib\Zend\Db\Adapter\Abstract.php(479): Zend_Db_Statement->execute(Array)
3 \lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query('SELECT COUNT(*)...', Array)
4 \lib\Varien\Db\Adapter\Pdo\Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query('SELECT COUNT(*)...', Array)
5 \lib\Zend\Db\Adapter\Abstract.php(825): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
6 \lib\Varien\Data\Collection\Db.php(225): Zend_Db_Adapter_Abstract->fetchOne(Object(Varien_Db_Select), Array)
7 \lib\Varien\Data\Collection.php(225): Varien_Data_Collection_Db->getSize()
8 \lib\Varien\Data\Collection.php(211): Varien_Data_Collection->getLastPageNumber()
9 \lib\Varien\Data\Collection\Db.php(516): Varien_Data_Collection->getCurPage()
10 \lib\Varien\Data\Collection\Db.php(563): Varien_Data_Collection_Db->_renderLimit()
11 \app\code\core\Mage\Adminhtml\Block\Widget\Grid.php(533): Varien_Data_Collection_Db->load()
12 \app\code\local\Acc\Logger\Block\Adminhtml\Logger\Grid.php(21): Mage_Adminhtml_Block_Widget_Grid->_prepareCollection()
13 \app\code\core\Mage\Adminhtml\Block\Widget\Grid.php(626): Acc_Logger_Block_Adminhtml_Logger_Grid->_prepareCollection()
14 \app\code\core\Mage\Adminhtml\Block\Widget\Grid.php(632): Mage_Adminhtml_Block_Widget_Grid->_prepareGrid()
15 \app\code\core\Mage\Core\Block\Abstract.php(862): Mage_Adminhtml_Block_Widget_Grid->_beforeToHtml()
16 \app\code\core\Mage\Core\Block\Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
17 \app\code\core\Mage\Core\Block\Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml('grid', true)
18 \app\code\core\Mage\Adminhtml\Block\Widget\Grid\Container.php(77): Mage_Core_Block_Abstract->getChildHtml('grid')
19 \app\design\adminhtml\default\default\template\widget\grid\container.phtml(36): Mage_Adminhtml_Block_Widget_Grid_Container->getGridHtml()
20 \app\code\core\Mage\Core\Block\Template.php(241): include('C:\xampp\htdocs...')
21 \app\code\core\Mage\Core\Block\Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml\defau...')
22 \app\code\core\Mage\Core\Block\Template.php(286): Mage_Core_Block_Template->renderView()
23 \app\code\core\Mage\Adminhtml\Block\Template.php(81): Mage_Core_Block_Template->_toHtml()
24 \app\code\core\Mage\Adminhtml\Block\Widget\Container.php(308): Mage_Adminhtml_Block_Template->_toHtml()
25 \app\code\core\Mage\Core\Block\Abstract.php(863): Mage_Adminhtml_Block_Widget_Container->_toHtml()
26 \app\code\core\Mage\Core\Block\Text\List.php(43): Mage_Core_Block_Abstract->toHtml()
27 \app\code\core\Mage\Core\Block\Abstract.php(863): Mage_Core_Block_Text_List->_toHtml()
28 \app\code\core\Mage\Core\Block\Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
29 \app\code\core\Mage\Core\Block\Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml('content', true)
30 \app\design\adminhtml\default\default\template\page.phtml(74): Mage_Core_Block_Abstract->getChildHtml('content')
31 \app\code\core\Mage\Core\Block\Template.php(241): include('C:\xampp\htdocs...')
32 \app\code\core\Mage\Core\Block\Template.php(272): Mage_Core_Block_Template->fetchView('adminhtml\defau...')
33 \app\code\core\Mage\Core\Block\Template.php(286): Mage_Core_Block_Template->renderView()
34 \app\code\core\Mage\Adminhtml\Block\Template.php(81): Mage_Core_Block_Template->_toHtml()
35 \app\code\core\Mage\Core\Block\Abstract.php(863): Mage_Adminhtml_Block_Template->_toHtml()
36 \app\code\core\Mage\Core\Model\Layout.php(555): Mage_Core_Block_Abstract->toHtml()
37 \app\code\core\Mage\Core\Controller\Varien\Action.php(390): Mage_Core_Model_Layout->getOutput()
38 \app\code\local\Acc\Logger\controllers\Adminhtml\LoggerbackendController.php(8): Mage_Core_Controller_Varien_Action->renderLayout()
39 \app\code\core\Mage\Core\Controller\Varien\Action.php(419): Acc_Logger_Adminhtml_LoggerbackendController->indexAction()
40 \app\code\core\Mage\Core\Controller\Varien\Router\Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch('index')
41 \app\code\core\Mage\Core\Controller\Varien\Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))
42 \app\code\core\Mage\Core\Model\App.php(354): Mage_Core_Controller_Varien_Front->dispatch()
43 \app\Mage.php(683): Mage_Core_Model_App->run(Array)
44 \index.php(87): Mage::run('', 'store')
45 {main}

My grid file:

public function __construct() {
    parent::__construct();

    $this->setId('logger_grid');
    $this->setUseAjax(FALSE);
    $this->setDefaultSort('log_id');
    $this->setDefaultDir(Varien_Data_Collection::SORT_ORDER_ASC);
    $this->setSaveParametersInSession(TRUE);
}

public function _prepareCollection() {
    $collection = Mage::getModel('logger/logger')->getCollection();
    $this->setCollection($collection);
    return parent::_prepareCollection();
}

public function _prepareColumns() {
    $this->addColumn('log_id', array(
        'header' => Mage::helper('logger')->__('ID'),
        'sortable' => TRUE,
        'index' => 'log_id',
        'editable' => FALSE,
    ));

    $this->addColumn('procedure', array(
        'header' => Mage::helper('logger')->__('Procedure'),
        'sortable' => TRUE,
        'index' => 'procedure',
    ));

    $this->addColumn('type', array(
        'header' => Mage::helper('logger')->__('Type'),
        'sortable' => TRUE,
        'index' => 'type',
        'editable' => FALSE,
    ));

    $this->addColumn('description', array(
        'header' => Mage::helper('logger')->__('Description'),
        'sortable' => TRUE,
        'index' => 'description',
        'editable' => FALSE,
    ));

    $this->addColumn('time', array(
        'header' => Mage::helper('logger')->__('Time'),
        'sortable' => TRUE,
        'index' => 'time',
        'editable' => FALSE,
        'type' => 'datetime',
    ));

    return parent::_prepareColumns();
}

public function getRowUrl($row) {
    return $this->getUrl('*/*/edit', array('id' => $row->getId()));
}

When addColumn('procedure', ...) is commented out then there is no error at all. I have no idea what's the problem... I don't know why in query is fragment like: 'procedure LIKE '%1%')'. 'Procedure' field is varchar. Table structure:

create table `{$installer->getTable('logger/logger')}`(
`log_id` int not null auto_increment, 
`procedure` varchar(254) null,
`type` varchar(254) null, 
`description` text null,
 `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
primary key(`log_id`));

Guerra · Accepted Answer · 2012-08-10 16:54:12Z

1

Try do that:

$collection = Mage::getModel('logger/logger');
$this->setCollection($collection);
return parent::_prepareCollection();

The core of magento do getCollection auto, Or:

   $collection = Mage::getModel('logger/logger')->getCollection()
        ->addAttributeToSelect('procedure');

answered Aug 10, 2012 at 16:54

Guerra

2,7882 gold badges24 silver badges32 bronze badges

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

1 Comment

Iwo Kucharski Over a year ago

Your post wasn't the solution, but it inspired me ;) Thank you

Guerra · Accepted Answer · 2012-08-10 14:36:32Z

0

Its about your collection, try http://www.magentocommerce.com/wiki/1_-_installation_and_configuration/using_collections_in_magento , If u need more help post the sql statment: using $collection->getSelect()->__toString();

answered Aug 10, 2012 at 14:36

Guerra

2,7882 gold badges24 silver badges32 bronze badges

1 Comment

Iwo Kucharski Over a year ago

SELECT main_table.* FROM z_logger AS main_table

Iwo Kucharski · Accepted Answer · 2012-08-13 10:10:52Z

0

Solution is really easy. All I need to add is ->load() so _prepareCollection() function becomes:

public function _prepareCollection() {
        $collection = Mage::getModel('logger/logger')->getCollection()->load();
        $this->setCollection($collection);
        return parent::_prepareCollection();
    }

Thank you for all responses :)

EDIT:

Error was more complex, because I used reserved word as column name ('procedure').

edited Aug 13, 2012 at 10:10

answered Aug 13, 2012 at 7:14

Iwo Kucharski

3,8353 gold badges54 silver badges68 bronze badges

Collectives™ on Stack Overflow

SQL query error in Magento grid

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related