Using SQL statements directly in Magento

principle:

The Magento is based on the Zend framework, so the underlying is still Zend Zend DB

Trace the following function to the file app/code/core/mage/catalog/model/resource/eav/mysql4/config.php getattributesusedinlisting ()

/**
* Retrieve product Attributes used in Catalog product listing
*
* @return Arrays
*/
Public Function g Etattributesusedinlisting () {
$select = $this->_getreadadapter ()->select ()
->from (Array (' Main_ Table ' = = $this->gettable (' Eav/attribute '))
->join (
Array (' additional_table ' = $this GetTable (' Catalog/eav_attribute ')),
' main_table.attribute_id = additional_table.attribute_id ',
Array ()

->joinleft (
Array (' al ' = = $this->gettable (' Eav/attribute_label ')),
' al.attribute_id = Main_ table.attribute_id and al.store_id = '. (int) $this->getstoreid (),
Array (' store_label ' = = new Zend_db_expr (' Ifnull (Al.value, Main_table.frontend_ Label)
)
->where (' main_table.entity_type_id=? ', $this->getentitytypeid ())
->where (' Additional_table.used_in_product_listing=? ', 1);
– $sql = $select->assemble ();
–echo $sql;
return $this->_getreadadapter ()->fetchall ($select);
}

The Magento operation database was simply encapsulated on the basis of Zend DB (Zend Framework). Zend DB has its own set of rules to combine to generate the final SQL query statement, you can see that the above code has a from () join () Joinleft () where () functions, a mess of a lot of things, need to Zend DB rules very familiar, To know what SQL statements are actually executed, is there any way to print out the SQL statements directly? Find the next, there is really, is the assemble () function. As you can see in the last section of the code above. Let's get SQL attached.

SELECT ' main_table '. *,
Ifnull (Al.value, Main_table.frontend_label) as ' Store_label '
From ' Eav_attribute ' as ' main_table '
INNER JOIN ' Catalog_eav_attribute ' as ' additional_table '
On main_table.attribute_id = additional_table.attribute_id
Left JOIN ' Eav_attribute_label ' as ' al '
On al.attribute_id = main_table.attribute_id and al.store_id = 1
WHERE (main_table.entity_type_id= ' 4′)
and (Additional_table.used_in_product_listing=1)

Print SQL statements in Magento to debug

Sometimes in order to debug the Magento Mall system, you need to get the current query SQL statement, Magento get the SQL statement, here we pass

$collection->getselectsql (True) to debug SQL

$collection =mage::getresourcemodel (' reports/product_collection ');
$query = $collection->getselectsql (true);
Echo $query;

Magento another way to get SQL statements is to set print SQL to True

$collection =mage::getresourcemodel (' reports/product_collection ');
$collection->printlogquery (TRUE);

The resulting SQL statement :

SELECT ' e '. * from ' catalog_product_entity ' as ' e '

Here is just print query product SQL, if you want to get other local SQL statements, the same is true, we can see according to the above SQL statement, in fact, Magento performance is poor, "select *", Magetno is based on EAV architecture, you can imagine this speed

Operation:

Magento's models and collection are powerful and can be used to easily query and manipulate databases. But on some occasions, because of some special needs or lack of understanding of Magento, you may need to write your own handwritten SQL statements to query and manipulate the database. The following are code that reads and writes the database.

For Read
Fetch read database connection that's used in Mage_core module

$read = Mage::getsingleton (' Core/resource ')->getconnection (' Core_read ');

First
$query = $read->query ("Select name from Core_website");
while ($row = $query->fetch ())
{
$row = new Varien_object ($row);
echo "<strong>". $row->getname (). "</strong><br/>";
}

Second
$results = $read->fetchall ("select * from Core_website;");
foreach ($results as $row)
{
echo $row [' name ']. "<br/>";
}

For Write
Fetch write database connection that's used in Mage_core module
$write = Mage::getsingleton (' Core/resource ')->getconnection (' Core_write ');

Now $write are an instance of Zend_db_adapter_abstract
$write->query ("INSERT INTO tablename values (' AAA ', ' BBB ', ' CCC ')");

Note the parameter "Core_read" in the Getconnection () method above, indicating the resource that the Magento will use. Corresponding to this, we use the parameter "Core_write" When we modify the database. In general, the parameters of the Getconnection method should be set to "Core_read" or "Core_write" (which should not be specified, However, if Magento has multiple databases, it must be specified.

As a new entension module, it is a good practice to define "Core_read" "core_write" in config. Defined as follows:

<config>
<global>
<resources>
<extension_setup>
<connection>
<use>core_setup</use>
</connection>
</extension_setup>
<extension_read>
<connection>
<use>core_read</use>
</connection>
</extension_read>
<extension_write>
<connection>
<use>core_write</use>
</connection>
</extension_write>
</resources>
</global>
</config>
Corresponds to the name of the module added above. Use the following statement in the read or write Database:

$conn = Mage::getsingleton (' Core/resource ')->getconnection (' Extension_read ');
$conn = Mage::getsingleton (' Core/resource ')->getconnection (' Extension_write ');

The general situation is that most of the module is defined as "Core_read" "core_write" convenient and save resources. Except, of course, special cases:

• Different read and Write permissions for each module
• Need to use multiple database

Instance:

<?php
/**
* Get the resource model
*/
$resource = Mage::getsingleton (' Core/resource ');

/**
* Retrieve the Read connection
*/
$readConnection = $resource->getconnection (' Core_read ');

/**
* Retrieve The Write connection
*/
$writeConnection = $resource->getconnection (' core_write ');

Get a table name from a string

<?php

/**
* Get the resource model
*/
$resource = Mage::getsingleton (' Core/resource ');

/**
* Get the table name
*/
$tableName = $resource->gettablename (' catalog_product_entity ');

/**
* If prefix is ' mage_ ' then the below statement
* Would print out mage_catalog_product_entity
*/
Echo $tableName;

Get a table name from an entity name

<?php

/**
* Get the resource model
*/
$resource = Mage::getsingleton (' Core/resource ');

/**
* Get the table name
*/
$tableName = $resource->gettablename (' catalog/product ');

/**
* If prefix is ' mage_ ' then the below statement
* Would print out mage_catalog_product_entity
*/
Echo $tableName;

Reading from the Database varien_db_select::fetchall

This method takes a query as it's parameter, executes it and then returns all of the results as an array. In the code example below, we use Varien_db_select::fetchall to return all of the records in the Catalog_product_entity TA ble.

<?php

/**
* Get the resource model
*/
$resource = Mage::getsingleton (' Core/resource ');

/**
* Retrieve the Read connection
*/
$readConnection = $resource->getconnection (' Core_read ');

$query = ' SELECT * from '. $resource->gettablename (' catalog/product ');

/**
* Execute the query and store the results in $results
*/
$results = $readConnection->fetchall ($query);

/**
* Print out the results
*/
echo sprintf (' <pre>%s</pre> ' Print_r ($results, true));

Varien_db_select::fetchcol

This method was similar to Fetchall except that instead of returning all of the results, it returns the first column from E Ach result row. In the code example below, we use Varien_db_select::fetchcol to retrieve all of the SKUs ' s in our database in an array.

<?php
/**
* Get the resource model
*/
$resource = Mage::getsingleton (' Core/resource ');

/**
* Retrieve the Read connection
*/
$readConnection = $resource->getconnection (' Core_read ');

/**
* Retrieve Our table name
*/
$table = $resource->gettablename (' catalog/product ');

/**
* Execute the query and store the results in $results
*/
$sku = $readConnection->fetchcol (' SELECT sku from '. $table. ‘);

/**
* Print out the results
*/
echo sprintf (' <pre>%s</pre> ' Print_r ($results, true));

Try This code and look at the results. Notice how all of the SKU's is in a single array, rather than each row has it's own array? If you don ' t understand this, try changing fetchcol for Fetchall and compare the differences.

Varien_db_select::fetchone

Unlike the previous-methods, Varien_db_select::fetchone returns one value from the first row only. This value was returned on it's own and is not a wrapped in an array. In the code example below, we take a product ID of the and return it's SKU.

<?php

/**
* Get the resource model
*/
$resource = Mage::getsingleton (' Core/resource ');

/**
* Retrieve the Read connection
*/
$readConnection = $resource->getconnection (' Core_read ');

/**
* Retrieve Our table name
*/
$table = $resource->gettablename (' catalog/product ');

/**
* Set the product ID
*/
$productId = 44;

$query = ' SELECT sku from '. $table. ' WHERE entity_id = '
. (int) $productId. ' LIMIT 1 ';

/**
* Execute the query and store the result in $sku
*/
$sku = $readConnection->fetchone ($query);

/**
* Print the SKU to the screen
*/
Echo ' SKU: '. $sku. ' <br/> ';

When trying-example, ensure the product ID to a ID that exists in your database!

May think this fetchone works the same as Fetchcol or Fetchall would if you have added 1 column to the SELECT query an D added a ' LIMIT 1 ', however you would be wrong. The main difference with this function is the value returned is the actual value, where as Varien_db_select::fetchcol and Varien_db_select::fetchall would wrap the value in an array. To understand this a little, try swapping the method ' s and comparing the results.

Writing to the Database

When saving a Magento model, there can is a lot of background data being saved so you weren ' t even aware of. For example, saving a product model can take several seconds due to the amount of related data saves and indexing that nee DS to take place. This was okay if you need all the data saving and if you have want to update the SKU of a product, this can be wasteful.

The example code below would show you what when given a product ID, you can alter the SKU. This was a trivial example but should illustrate how to execute write queries against your Magento database.

<?php

/**
* Get the resource model
*/
$resource = Mage::getsingleton (' Core/resource ');

/**
* Retrieve The Write connection
*/
$writeConnection = $resource->getconnection (' core_write ');

/**
* Retrieve Our table name
*/
$table = $resource->gettablename (' catalog/product ');

/**
* Set the product ID
*/
$productId = 44;

/**
* Set the new SKU
* It's assumed that's hard coding the new SKUs in
* If The input is not dynamic, consider using the
* Varien_db_select object to insert data
*/
$newSku = ' New-sku ';

$query = "UPDATE {$table} SET sku = ' {$sku} ' WHERE entity_id ="
. (int) $productId;

/**
* Execute the query
*/
$writeConnection->query ($query);

To test this have worked, use the knowledge gained from the first part of this tutorial to write a query to extract the SKU That has just been changed.

Varien_db_select

The Varien_db_select, which have been touched on in this article are a far better option for extracting/wriiting information . Not also provides a layered of security, which if used correctly, is impenetrable. More would be is covered on varien_db_select (aka Zend_db_select) in a future article.

Using SQL statements directly in Magento