In order to run a SQL query against the Magento database, you first need a resource model then a database connection.
$db = Mage::getResourceSingleton('core/resource')->getConnection('core_write');
$result = $db->query('SELECT 'entity_id' FROM 'catalog_product_entity');
if(!$result) {
return FALSE;
}
$rows = $result->fetch(PDO::FETCH_ASSOC);
if(!$rows) {
return FALSE;
}
print_r($rows);
Thank you for sharing SQL query against the Magento database. Nice post. keep writing.
Just so everyone else knows. To get rows of data do a while loop or similar.
$db = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’);
// now $write is an instance of Zend_Db_Adapter_Abstract
$result = $db->query(“select `id`,`name` from `dealerdoc_categories`”);
if(!$result) {
return FALSE;
}
$categories=array();
while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
$categories[] = array(‘value’=>$row['id'],’label’=>$row['name']);
}
here is my php test code…
getConnection(‘core_write’);
?>
this is error message…
Fatal error: Call to a member function getResourceModelInstance() on a non-object in /var/www/magento.homeunix.com/app/Mage.php on line 460
help me….please….
reposting…
my code..is………..
error_reporting(E_ALL | E_STRICT);
ini_set(‘display_errors’, 1);
require_once “app/Mage.php”;
$db = Mage::getResourceSingleton(‘core/resource’)->getConnection(‘core_write’);
Fixed a couple problems in the above code:
1. I added a limit of 10 to the statement so it doesn’t crash your browser if you have a lot of products
2. Added “fetchAll” to the code so it fetches all the available rows (max 10 becuase of the above mentioned limit)
3. Added support for prefixes if your tables have a predefined prefix.
4. Changed to Mage::getSingleton(‘core/resource’). The above code is wrong.
5. Also, we should be using core_read here instead of core_write since we’re only reading data, but we’ll keep using core_write since it works for both.
$db = Mage::getSingleton(‘core/resource’)->getConnection(‘core_write’);
$table_prefix = Mage::getConfig()->getTablePrefix();
$result = $db->query(“SELECT entity_id FROM {$table_prefix}catalog_product_entity LIMIT 10″);
if(!$result) {
return FALSE;
}
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
if(!$rows) {
return FALSE;
}
print_r($rows);
Check out the Sweet Tooth reward points extension at http://www.getsweettooth.com which using this code somewhere in the source.
Cheers!