Magento Forum

Issues with reading magento Database
 
Lhari
Jr. Member
 
Total Posts:  21
Joined:  2012-01-17
 

Hey everyone.
Ive been making a price slider for a fairly bizarre site. its a site that functions as an auction site, but would like a price slider to work on current bids of a product. I’ve found a freeware slider and started recoding it to do what I’d like it to do, and I’m almost at the end, I’m just missing the tiny part now that is actually showing the correct products.

the original code for finding products look like this

$collection->getSelect()->where(' final_price >= "'.$min.'" AND final_price <= "'.$max.'" ');

I have changed it to look like this

$collection->getSelect()->from('belvg_auction_products')->where(' start_bid >= "'.$min.'" AND start_bid <= "'.$max.'" ');

Everything loads fine, but when I get to the point of actually listing products, I get the following error log.

a:5:{i:0;s:101:"SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘start_bid’ in where clause is ambiguous";i:1;s:4658:"#0 C:\wamp\www\magento\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)
#1 C:\wamp\www\magento\lib\Zend\Db\Statement.php(300): Varien_Db_Statement_Pdo_Mysql->_execute(Array)
#2 C:\wamp\www\magento\lib\Zend\Db\Adapter\Abstract.php(479): Zend_Db_Statement->execute(Array)
#3 C:\wamp\www\magento\lib\Zend\Db\Adapter\Pdo\Abstract.php(238): Zend_Db_Adapter_Abstract->query(’SELECT `count_t...’, Array)
#4 C:\wamp\www\magento\lib\Varien\Db\Adapter\Pdo\Mysql.php(419): Zend_Db_Adapter_Pdo_Abstract->query(’SELECT `count_t...’, Array)
#5 C:\wamp\www\magento\lib\Zend\Db\Adapter\Abstract.php(808): Varien_Db_Adapter_Pdo_Mysql->query(Object(Varien_Db_Select), Array)
#6 C:\wamp\www\magento\app\code\core\Mage\Catalog\Model\Resource\Product\Collection.php(1043): Zend_Db_Adapter_Abstract->fetchPairs(Object(Varien_Db_Select))
#7 C:\wamp\www\magento\app\code\core\Mage\Catalog\Model\Layer\Filter\Category.php(166): Mage_Catalog_Model_Resource_Product_Collection->addCountToCategories(Object(Mage_Catalog_Model_Resource_Category_Collection))
#8 C:\wamp\www\magento\app\code\core\Mage\Catalog\Model\Layer\Filter\Abstract.php(151): Mage_Catalog_Model_Layer_Filter_Category->_getItemsData()
#9 C:\wamp\www\magento\app\code\core\Mage\Catalog\Model\Layer\Filter\Abstract.php(120): Mage_Catalog_Model_Layer_Filter_Abstract->_initItems()
#10 C:\wamp\www\magento\app\code\core\Mage\Catalog\Model\Layer\Filter\Abstract.php(109): Mage_Catalog_Model_Layer_Filter_Abstract->getItems()
#11 C:\wamp\www\magento\app\code\core\Mage\Catalog\Block\Layer\Filter\Abstract.php(132): Mage_Catalog_Model_Layer_Filter_Abstract->getItemsCount()
#12 C:\wamp\www\magento\app\code\core\Mage\Catalog\Block\Layer\View.php(219): Mage_Catalog_Block_Layer_Filter_Abstract->getItemsCount()
#13 C:\wamp\www\magento\app\code\core\Mage\Catalog\Block\Layer\View.php(234): Mage_Catalog_Block_Layer_View->canShowOptions()
#14 C:\wamp\www\magento\app\design\frontend\zederkof\default\template\catalog\layer\view.phtml(4): Mage_Catalog_Block_Layer_View->canShowBlock()
#15 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Template.php(241): include(’C:\wamp\www\mag...’)
#16 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Template.php(272): Mage_Core_Block_Template->fetchView(’frontend\zederk...’)
#17 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Template.php(286): Mage_Core_Block_Template->renderView()
#18 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Abstract.php(863): Mage_Core_Block_Template->_toHtml()
#19 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Text\List.php(43): Mage_Core_Block_Abstract->toHtml()
#20 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Abstract.php(863): Mage_Core_Block_Text_List->_toHtml()
#21 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Abstract.php(582): Mage_Core_Block_Abstract->toHtml()
#22 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Abstract.php(526): Mage_Core_Block_Abstract->_getChildHtml(’left’, true)
#23 C:\wamp\www\magento\app\design\frontend\zederkof\default\template\page\2rows.phtml(46): Mage_Core_Block_Abstract->getChildHtml(’left’)
#24 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Template.php(241): include(’C:\wamp\www\mag...’)
#25 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Template.php(272): Mage_Core_Block_Template->fetchView(’frontend\zederk...’)
#26 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Template.php(286): Mage_Core_Block_Template->renderView()
#27 C:\wamp\www\magento\app\code\core\Mage\Core\Block\Abstract.php(863): Mage_Core_Block_Template->_toHtml()
#28 C:\wamp\www\magento\app\code\core\Mage\Core\Model\Layout.php(555): Mage_Core_Block_Abstract->toHtml()
#29 C:\wamp\www\magento\app\code\core\Mage\Core\Controller\Varien\Action.php(390): Mage_Core_Model_Layout->getOutput()
#30 C:\wamp\www\magento\app\code\community\Magehouse\Slider\controllers\Catalog\CategoryController.php(112): Mage_Core_Controller_Varien_Action->renderLayout()
#31 C:\wamp\www\magento\app\code\core\Mage\Core\Controller\Varien\Action.php(419): Magehouse_Slider_Catalog_CategoryController->viewAction()
#32 C:\wamp\www\magento\app\code\core\Mage\Core\Controller\Varien\Router\Standard.php(250): Mage_Core_Controller_Varien_Action->dispatch(’view’)
#33 C:\wamp\www\magento\app\code\core\Mage\Core\Controller\Varien\Front.php(176): Mage_Core_Controller_Varien_Router_Standard->match(Object(Mage_Core_Controller_Request_Http))

I’ve added a screenshot for the database.

Image Attachments
db_screenshot.png
 
Magento Community Magento Community
Magento Community
Magento Community
 
Lhari
Jr. Member
 
Total Posts:  21
Joined:  2012-01-17
 

Anyone got any ideas for this by now?

 
Magento Community Magento Community
Magento Community
Magento Community
 
aposadi
Jr. Member
 
Total Posts:  19
Joined:  2013-01-06
 

A little suggestion, not clean, but maybe will work...:

$collection->getSelect()->where(' belvg_auction_products.start_bid >= "'.$min.'" AND belvg_auction_products.start_bid <= "'.$max.'" ');
 
Magento Community Magento Community
Magento Community
Magento Community
 
Lhari
Jr. Member
 
Total Posts:  21
Joined:  2012-01-17
 

my error now changed to

a:5:{i:0;s:107:"SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘belvg_auction_products.start_bid’ in ‘where clause‘“;i:1;s:3262:"#0 C:\wamp\www\magento\lib\Varien\Db\Statement\Pdo\Mysql.php(110): Zend_Db_Statement_Pdo->_execute(Array)

So im guessing it cant actually target the column, the way you suggested :(

 
Magento Community Magento Community
Magento Community
Magento Community
 
aposadi
Jr. Member
 
Total Posts:  19
Joined:  2013-01-06
 

Can you log your $collection? maybe there is anything wrong with this object.

Did you change anything of the configuration of your module?

 
Magento Community Magento Community
Magento Community
Magento Community
 
Lhari
Jr. Member
 
Total Posts:  21
Joined:  2012-01-17
 

I haven’t changed anything with the extension in how it reads and processes products.

a var dump off the collection looks normal

 
Magento Community Magento Community
Magento Community
Magento Community
 
aposadi
Jr. Member
 
Total Posts:  19
Joined:  2013-01-06
 

ok ok…

did you try to log the query?

In any blog i found a way for logging the query:

$query$collection->getSelect()->from(\'belvg_auction_products\')->where(\' start_bid >= \"\'.$min.\'\" AND start_bid <= \"\'.$max.\'\" \')->__toString();

Mage::log($query);

or something like that…

so you can have the query string and you can test it in your sql server

 
Magento Community Magento Community
Magento Community
Magento Community
 
Lhari
Jr. Member
 
Total Posts:  21
Joined:  2012-01-17
 

This is the log i got out.

2013-05-22T12:13:38+00:00 DEBUG (7): SELECT `e`.*, `belvg_auction_products`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `belvg_auction_products`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=’8’
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = ‘1’ AND price_index.customer_group_id = 0 WHERE ( start_bid >= “700” AND start_bid <= “800” )
2013-05-22T12:13:38+00:00 ERR (3): Notice: Undefined index:  in C:\wamp\www\magento\lib\Zend\Db\Select.php on line 818
2013-05-22T12:13:38+00:00 DEBUG (7): SELECT `belvg_auction_products_2`.*, `e`.*, `belvg_auction_products`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `belvg_auction_products` AS `belvg_auction_products_2`
INNER JOIN `catalog_product_entity` AS ``
INNER JOIN `belvg_auction_products`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=’8’
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = ‘1’ AND price_index.customer_group_id = 0 WHERE ( start_bid >= “700” AND start_bid <= “800” ) AND ( start_bid >= “700” AND start_bid <= “800” )

 
Magento Community Magento Community
Magento Community
Magento Community
 
aposadi
Jr. Member
 
Total Posts:  19
Joined:  2013-01-06
 

Did you try that query directly on your sql server, or phpmyadmin?

I didn’t find anything of wrong in this query…

but that error can be a clue :2013-05-22T12:13:38+00:00 ERR (3): Notice: Undefined index:  in C:\wamp\www\magento\lib\Zend\Db\Select.php on line 818

 
Magento Community Magento Community
Magento Community
Magento Community
 
Lhari
Jr. Member
 
Total Posts:  21
Joined:  2012-01-17
 

the query should be flawless, it seems its the FROM connection thats wrong, somehow.

In the original script I’ve worked from, there’s been a fuction called

$collection->getSelect()->where(' final_price >= "'.$min.'" AND final_price <= "'.$max.'" ');
        
}

and this worked without fail, the only thing I’ve done is tried to target the Belvg_auction table and changed final price to start_bid.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Lhari
Jr. Member
 
Total Posts:  21
Joined:  2012-01-17
 

After changing a few things around, my log now says

2013-05-23T07:44:30+00:00 ERR (3): Notice: Undefined index: loading_image in C:\wamp\www\magento\app\code\community\Magehouse\Slider\Block\Ajax.php on line 16
2013-05-23T07:44:30+00:00 DEBUG (7): SELECT `e`.*, `belvgprod`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `belvg_auction_products` AS `belvgprod`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=’11’
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = ‘1’ AND price_index.customer_group_id = 0 WHERE ( belvgprod.start_bid >= “15” AND belvgprod.start_bid <= “123” )

and the top line of the error report is now

a:5:{i:0;s:63:"You cannot define a correlation name ‘belvgprod’ more than once";i:1;s:4117:"#0 C:\wamp\www\magento\lib\Varien\Db\Select.php(281): Zend_Db_Select->_join(’from’, ‘belvg_auction_p...’, NULL, ‘*’, NULL)

The changes I have done is the following

$collection->getSelect()->from('belvg_auction_products AS belvgprod');
             
$query $collection->getSelect()->where(' belvgprod.start_bid >= "'.$min.'" AND belvgprod.start_bid <= "'.$max.'" ')->__toString();

            
Mage::log($query);
 
Magento Community Magento Community
Magento Community
Magento Community
 
aposadi
Jr. Member
 
Total Posts:  19
Joined:  2013-01-06
 

Did you try this?

$collection->getSelect()->where(\' start_bid >= \"\'.$min.\'\" AND start_bid <= \"\'.$max.\'\" \');

Maybe that \"from\" is useless.

The last error was:

a:5:{i:0;s:63:\"You cannot define a correlation name â€˜belvgprod’ more than once\";i:1;s:4117:\"#0 C:\\wamp\\www\\magento\\lib\\Varien\\Db\\Select.php(281): Zend_Db_Select->_join(’from’, â€˜belvg_auction_p...’, NULL, â€˜*’, NULL)

Looking for that I found this:

http://stackoverflow.com/questions/10524017/magento-layered-navigation-you-cannot-define-a-correlation-name-mycustomattrib

Maybe your table is yet inner joined in your collection select, maybe there is any configuraiton that put that table in that query…

 
Magento Community Magento Community
Magento Community
Magento Community
 
Lhari
Jr. Member
 
Total Posts:  21
Joined:  2012-01-17
 

However, the AS worked perfectly, if you notice, the inner join Belvg_auction_products has an alias now…

It does however seem to loop itself whenever I run the page once.

2013-05-23T08:35:44+00:00 ERR (3): Notice: Undefined index: loading_image in C:\wamp\www\magento\app\code\community\Magehouse\Slider\Block\Ajax.php on line 16
2013-05-23T08:35:44+00:00 DEBUG (7): SELECT `e`.*, `belvg_auction_products`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `belvg_auction_products`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=’11’
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = ‘1’ AND price_index.customer_group_id = 0 WHERE ( start_bid >= “22” AND start_bid <= “71” )

2013-05-23T08:35:45+00:00 ERR (3): Notice: Undefined index:  in C:\wamp\www\magento\lib\Zend\Db\Select.php on line 818
2013-05-23T08:35:45+00:00 DEBUG (7): SELECT `belvg_auction_products_2`.*, `e`.*, `belvg_auction_products`.*, `cat_index`.`position` AS `cat_index_position`, `price_index`.`price`, `price_index`.`tax_class_id`, `price_index`.`final_price`, IF(price_index.tier_price IS NOT NULL, LEAST(price_index.min_price, price_index.tier_price), price_index.min_price) AS `minimal_price`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price` FROM `belvg_auction_products` AS `belvg_auction_products_2`
INNER JOIN `catalog_product_entity` AS ``
INNER JOIN `belvg_auction_products`
INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.visibility IN(2, 4) AND cat_index.category_id=’11’
INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = ‘1’ AND price_index.customer_group_id = 0 WHERE ( start_bid >= “22” AND start_bid <= “71” ) AND ( start_bid >= “22” AND start_bid <= “71” )

 
Magento Community Magento Community
Magento Community
Magento Community
 
Lhari
Jr. Member
 
Total Posts:  21
Joined:  2012-01-17
 

Issue still hasen’t been solved.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top