Magento Database Interaction

Here at ecommerce website design we came across the need to output data directly from the database. I’m still not sure why this need arose, but what I do know is that the techniques we used are very useful for other situations too.

What I’ll do in this short blog post is basically show you what data we retrieved from the database – in doing so hopefully you will come up with some idea’s of your own as to the implementations of this little script. Obviously you can nip/tuck and make of this what you will.

The overview:

1. Create a seperate php file on your root layer (I named mine getproductstuff.php)
2. Create a connection with magento
3. Create a connection with the magento database
4. Query the data
5. Output the data

This could be used for automated forms or (as I think my memory is coming back now) pairing a big list of product id’s and sku’s so that the customer can do quick-look-up’s from a piece of printed paper.

The Code:

require_once 'app/Mage.php';
Mage::app('default');

$write = Mage::getSingleton('core/resource')->getConnection('core_write');
$readresult=$write->query("SELECT product_id, category_id FROM catalog_category_product ORDER BY product_id");
while ($row = $readresult->fetch() ) {
$write2 = Mage::getSingleton('core/resource')->getConnection('core_write');
$readresult2=$write2->query("SELECT sku FROM catalog_product_entity WHERE entity_id = ".$row['product_id']." LIMIT 1");
while ($row2 = $readresult2->fetch() ) {
$sku = $row2['sku'];
}
echo $row['product_id']." ".$sku."
";

}

The above code brings back the SKU and Product ID of every single product in the database.

You could then use the product_id to get anything else you like – echo the data out into tables or even copy the data into other databases.

The choice, as they say, is all yours…

Thanks for visiting our Magento Blog – take care!

Robert Kent Author Page

• Scheduled Magento Database Backups
• The Magento EAV Database Structure
• Establish a Magento Database Connection