magento mysql queries

In magento to Read or Write any query we need database connection.

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

1	$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 Mysql Queries.

Execute a Query

To execute any raw query in magento we can use any connection. For example to truncate a table.

1	$read->query("truncate table TABLE_NAME");

The query() function simply executes any raw query.

Select Query

1	$result = $read->fetchAll("select * from TABLE_NAME where Field = 'Value'");

This query will return a multidimensional Array with table as keys and table values as value. It is equivalent to mysql_fetch_array() function on the mysql. The fetchAll() function executes the query and returns all the rows of the table.

MysqlNumRows

To get the effected number of rows use the php count method to count the array.

1	count($result);

Insert Query

As before we used the $read object to execute the fetch query so the same $write object will be used to execute insert query.

1	$write->insert("TABLE_NAME", array("field" => "value", "field" => "value");

or

1	$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.

1 2 3 4 5

$data = array("field" => "value", "field" => "value");   $where = "id = 5";   $write->update("TABLE_NAME", $data, $where);

or

1	$write->query("update TABLE_NAME  set field = 'value'");

Delete Query

1 2 3

$where = "id = 5";   $write->delete("TABLE_NAME", $where);

1	$write->query("delete from TABLE_NAME where field = 'value'");

Executing the query in Zend way

http://framework.zend.com/manual/en/zend.db.html
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.
These methods can be executed using the $read object.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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

Get the right Table Name

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.

1 2 3 4 5

$tableName = $read->getTableName("catalog/product");   // OR   $tableName = $this->getTable("catalog/product");

Example of full query

1 2 3 4 5 6 7

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

List of DB Methods

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

// return all rows of the table in object form   $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);