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.
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
|
$select = $read ->select();
$select ->from( "TABLE_NAME" );
$select ->where( " .. condition .. " );
$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" );
$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
|
$read ->fetchAll( " .. query .." );
$read ->fetchAssoc( " .. query .." );
$read ->fetchRow( " .. query .." );
$write ->insert( "TABLE_NAME" , array ( "field" => "value" , "field" => "value" );
$write ->lastInsertId();
$write ->update( "TABLE_NAME" , array ( "field" => "value" , "field" => "value" ), "where clause" );
$write -> delete ( "TABLE_NAME" , $where );
count ( $result );
|