Magento – Database Queries
Magento is a complete and awesome MVC application. It’s fully moduler and easy to extend its features and functionality. All the CRUD operations are performed using the modules, model classes. But many a time it requires a developer to execute the db query in traditional form.
Here is the list of methods and queries.
In magento to Read or Write any query we need database connection.
<?php //database read adapter
$read = Mage::getSingleton('core/resource')->getConnection('core_read')
//database write adapter
$write = Mage::getSingleton('core/resource')->getConnection('core_write'); ?>
These connections return special instances of the Mage_Core_Model_Resource class, that contains functions used to execute raw Database Queries.
Execute a Query
To execute any raw query in Magento, Read Adapter connection.
For example to truncate a table.
<?php $read->query("truncate table TABLE_NAME"); ?>
The query() function simply executes any raw query.
Select Query
<?php $result = $read->fetchAll("select * from TABLE_NAME where Field = 'Value'"); ?>
This query will return a multidimensional Array with columns as keys and data as value. It is similar to mysql_fetch_array() function of mysql. The fetchAll() function executes the query and returns all the rows of the table.
NumRows
To get the effected number of rows use the php count method to count the array.
<?php
$result = $read->fetchAll("select * from TABLE_NAME where Field = 'Value'");
$total_rows = count($result);
?>
Insert Query
As earlier I used the Read Adapter to execute the select query so the same Write Adapter will be used to execute insert query.
<?php
//first argument tablename
//second argument associated array to insert
$write->insert("TABLE_NAME", array("field" => "value", "field" => "value");
//or
$write->query("insert into TABLE_NAME (field1, field2) values('value1', 'value2'");
?>
Update Query
Update and Delete queries will also be executed in the same manner.
<?php
$data = array("field" => "value", "field" => "value");
$where = "id = 5";
$write->update("TABLE_NAME", $data, $where);
//or
$write->query("update TABLE_NAME set field = 'value'");
?>
Delete Query
<?php
$where = "id = 5";
$write->delete("TABLE_NAME", $where);
//or
$write->query("delete from TABLE_NAME where field = 'value'");
?>
