Connect and query Magento data

Recently we had a need to incorporate a third party app into a Magento system. That got us thinking about using data between Magento and the app. To start, you should include the path to Magento. Specifically app/Mage.php file

require(dirname(__FILE__) . '/app/Mage.php');

You will need to initialize the Magento application object.

Mage::app();

We can now move on to handling Magento data.

Magento database connection

We will use Magento’s core resources to access the data. For read we will use ‘core_read’ and for write ‘core_write’ – quite explanatory.

Use the resource model and the read connection:

$conn = Mage::getSingleton('core/resource')->getConnection('core_read');

Use the resource model and the write connection:

$conn = Mage::getSingleton('core/resource')->getConnection('core_write');

Reading Data

There is more than one method available to return data from Magento. Depending on your needs you can use fetchOne, fetchRow, fetchCol, or fetchAll.

fetchOne

This method will return one value from the first row. This is a single value – string, int, etc… and not an array.

$result = $conn->fetchOne("SELECT entity_id FROM customer_entity WHERE email='[email protected]'");

fetchRow

This method will return an array of one row of data.

$result = $conn->fetchRow("SELECT * FROM customer_entity WHERE email='[email protected]'");

fetchCol

This method will return an array of one column of each row of data.

$result = $conn->fetchCol("SELECT email FROM customer_entity");

This will return all customers email addresses.

fetchAll

This method returns all Rows as arrays in a results array.

$result = $conn->fetchAll("SELECT * FROM customer_entity");

 Writing Data

Once you have your write connection set as mentioned above, you query the connection to do an insert or update.

$conn->query("INSERT INTO customer_entity  (email) VALUES (".$email.")");

$conn->query("UPDATE customer_entity SET email='".$email."' WHERE email='[email protected]'");

* The above are used for example purposes only. More fields, data, etc may be required.