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 we 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'");
?>

Executing the query in Zend style

Beside the raw query execution method we can use the Zend_Db to prepare the mysql statements. Zend_Db has methods to prepares the individual parts of the mysql query.

Reference: http://framework.zend.com/manual/en/zend.db.html

These methods can be executed using the Read Adapter object.

<?php
//will create a select object
$select = $read->select();
//add table name
$select->from("TABLE_NAME");
//conditional clauses
$select->where(" .. condition .. ");
//set order $select->order(" .. order criteria ..");
?>

Get the right Table Name

To get the name of the table with prefix we can use the getTableName(); method.

 

<?php
$tableName = $read->getTableName("catalog/product"); // OR
// inside a controller with $this object $tableName = $this->getTable("catalog/product");
?>

Example of full query

<?php
$result = $read->select() ->from($tableName) ->where("sku = ?", 5) ->order("name", "asc");
?>

Magento provides ability to add multiple store using a common database with table prefixs. So its better to let the magento find the accurate name of the table. To get the name of the table we can use the getTableName(); method.

List of DB Methods

Reference:http://framework.zend.com/manual/en/zend.db.table.html

<?php
//return all rows of the table as array
$read->fetchAll(" .. query ..");

// return all rows of the table in associated array
$read->fetchAssoc(" .. query ..");

// return single row
$read->fetchRow(" .. query ..");

// insert query
$write->insert("TABLE_NAME", array("field"=>"value", "field"=>"value");
// to get the last auto increment id
$write->lastInsertId();

// update query
$write->update("TABLE_NAME", array("field" => "value", "field" => "value"), "where clause");

// delete query
$write->delete("TABLE_NAME", $where);

// num rows
count($result);
?>

No Comments yet

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>