- 浏览: 13638951 次
- 性别:
- 来自: 洛杉矶
文章分类
- 全部博客 (1994)
- Php / Pear / Mysql / Node.js (378)
- Javascript /Jquery / Bootstrap / Web (435)
- Phone / IOS / Objective-C / Swift (137)
- Ubuntu / Mac / Github / Aptana / Nginx / Shell / Linux (335)
- Perl / Koha / Ruby / Markdown (8)
- Java / Jsp (12)
- Python 2 / Wxpython (25)
- Codeigniter / CakePHP (32)
- Div / Css / XML / HTML5 (179)
- WP / Joomla! / Magento / Shopify / Drupal / Moodle / Zimbra (275)
- Apache / VPN / Software (31)
- AS3.0/2.0 / Flex / Flash (45)
- Smarty (6)
- SEO (24)
- Google / Facebook / Pinterest / SNS (80)
- Tools (22)
最新评论
-
1455975567:
xuezhongyu01 写道wocan23 写道我想问下那个 ...
Mysql: LBS实现查找附近的人 (两经纬度之间的距离) -
xuezhongyu01:
wocan23 写道我想问下那个111.1是怎么得来的我也看不 ...
Mysql: LBS实现查找附近的人 (两经纬度之间的距离) -
18335864773:
试试 pageoffice 在线打开 PDF 文件吧. pag ...
jquery在线预览PDF文件,打开PDF文件 -
青春依旧:
opacity: 0.5; 个人喜欢这种方式!关于其他css特 ...
css透明度的设置 (兼容所有浏览器) -
July01:
推荐用StratoIO打印控件,浏览器和系统的兼容性都很好,而 ...
搞定网页打印自动分页问题
原理:
magento是基于Zend Framework的,所以底层用的还是zend的zend db
在文件app/code/core/Mage/Catalog/model/Resource/Eav /Mysql4/Config.php 中追踪到下面的函数 getAttributesUsedInListing()
/** * Retrieve Product Attributes Used in Catalog Product listing * * @return array */ public function getAttributesUsedInListing() { $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); }
Magento操 作数据库是在 Zend DB(Zend Framework)的基础上简单的做了下封装了。Zend DB 有自己的一套规则,来组合生成最终的SQL查询语句,可以看到上面的代码中有 from() join() joinLeft() where() 等函数,乱七八糟的一大堆东西,需要对 Zend DB的规则非常熟悉,才能知道实际执行的SQL语句,有没有办法直接打印出SQL语句?找了下,还真有,就是assemble()函数。在上面代码中最后 部分可以看到。顺被把SQL也附上来
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)
Magento中打印SQL语句来调试
有时为了调试magento商城系统,需要获取当前的查询sql语句,magento中获取SQL语句,这里我们通过
$collection->getSelectSql(true)来调试sql
$collection=Mage::getResourceModel('reports/product_collection'); $query=$collection->getSelectSql(true); echo $query;
magento获取SQL语句的另外一种方法是设置打印SQL为true
$collection=Mage::getResourceModel('reports/product_collection'); $collection->printlogquery(true);
得到的SQL语句 :
SELECT `e`.* FROM `catalog_product_entity` AS `e`
这里只是打印查询产品的SQL,如果要获取其他地方的SQL语句,道理也是一样的,我们根据上面的sql语句可以看到,其实magento的性能很差,"select *",magetno又是基于EAV架构的,可以想象下这速度
操作:
Magento的Models 和Collection 很强大,使用它们可以很方便的查询和操作数据库。但是有些场合,因为一些特殊需求或对Magento的了解不够深,可能会需要自己手写SQL语句来查询和操作数据库。以下分别是读写数据库的代码。
// For Read // fetch read database connection that is used in Mage_Core module $read= Mage::getSingleton('core/resource')->getConnection('core_read'); // first way $query = $read->query("select name from core_website"); while ($row = $query->fetch()) { $row = new Varien_Object($row); echo "<strong>" . $row->getName() . "</strong><br/>"; } // second way $results = $read->fetchAll("SELECT * FROM core_website;"); foreach ($results as $row) { echo $row['name'] . "<br/>"; }
// For Write // fetch write database connection that is used in Mage_Core module $write = Mage::getSingleton('core/resource')->getConnection('core_write'); // now $write is an instance of Zend_Db_Adapter_Abstract $write->query("insert into tablename values ('aaa','bbb','ccc')");
注意上面的getConnection()方法中的参数 "core_read",表明了Magento将要使用的资源。与之相对应,当我们修改数据库的时候使用参数"core_write".一般情况下 getConnection方法的参数应设成"core_read" 或 "core_write"(应该不指定也是可以的,但是如果Magento有多个数据库就必须指定了)。
作为新的entension module,在config.xml对"core_read" "core_write" 进行定义是个好的习惯。定义如下:
<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>
对应上面新增的module的名字.使用下面相对应的语句在read或write Database:
$conn = Mage::getSingleton('core/resource')->getConnection('extension_read'); $conn = Mage::getSingleton('core/resource')->getConnection('extension_write');
一般情况是绝大多数的module都定义成"core_read" "core_write"方便且节省资源。当然特殊情况除外:
- 给每个module不同的读写权限
- 需要用多个Database
实例:
<?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 was '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 was '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 table.
<?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 is similar to fetchAll except that instead of returning all of the results, it returns the first column from each result row. In the code example below, we use Varien_Db_Select::fetchCol to retrieve all of the SKU'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 are in a single array, rather than each row having 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 two methods, Varien_Db_Select::fetchOne returns one value from the first row only. This value is returned on it's own and is not wrapped in an array. In the code example below, we take a product ID of 44 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 out this example, ensure you change the product ID to an ID that exists in your database!
You may think that fetchOne works the same as fetchCol or fetchAll would if you only added 1 column to the SELECT query and added a 'LIMIT 1', however you would be wrong. The main difference with this function is that 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 be a lot of background data being saved that 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 needs to take place. This is okay if you need all the data saving, but if you only want to update the SKU of a product, this can be wasteful.
The example code below will show you how when given a product ID, you can alter the SKU. This is 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 is assumed that you are hard coding the new SKU 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 has 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 has been touched on in this article is a far better option for extracting/wriiting information. Not only is it easy to use, it also provides a layered of security, which if used correctly, is impenetrable. More will be covered on Varien_Db_Select (aka Zend_Db_Select) in a future article.
来源:http://fishpig.co.uk/blog/direct-sql-queries-magento.html
发表评论
-
Magento: 后台显示图片不能找到 Image file was not found on product tab
2016-08-30 02:22 2889I was uploading some images f ... -
理解WordPress的PingBack和TrackBack
2016-08-26 02:21 5674pingback和trackback的功 ... -
零基础 Amazon Web Services (AWS) 入门教程图文版(四)
2016-06-07 11:40 988自上一篇之后,5天过去了,这篇文章总算是挤出来了... 其实 ... -
零基础 Amazon Web Services (AWS) 入门教程图文版(三)
2016-06-07 11:41 2007原则上WDCP安装好了,就可以直接使用了,FTP、MySQL ... -
零基础 Amazon Web Services (AWS) 入门教程图文版(二)
2016-06-07 01:33 953上一篇讲到,主机正常运转了。但是此时如果直接访问公网IP是 ... -
零基础 Amazon Web Services (AWS) 入门教程图文版(一)
2016-06-07 01:31 21554现在小站唯一的流量都靠AWS这个关键词了,刚好要用AWS重新 ... -
零基础 Amazon Web Services (AWS) 入门教程 (列表)
2016-06-07 01:32 2038在 Amazon Web Services 上托 ... -
Magento 1.9:新订单通知 Admin Order Notifier
2016-06-02 02:48 804Here is a little Mag ... -
Magento 1.9.X 系列教程
2016-05-14 02:44 2251Magento安装下载教学: Magento教程 1 ... -
Magento: 产品页面下jquery change函数失效 Call javascript function onchange product option
2016-05-05 06:39 1702明显的原因是change函数跟magento默认的oncha ... -
Magento: 判断是否为手机浏览 Optimise Web's Mobile Detect Class for Magento
2016-04-29 07:01 1127项目地址:Optimise Web's Mobile Det ... -
Magento: Gird 和 form 区域 Module Development Series – Magento Admin Module
2016-04-28 02:39 638In this tutorial, we are goi ... -
Magento: addAttributeToFilter 和 addFieldToFilter 的区别 Difference between addAttri
2016-04-28 02:34 988addAttributeToFilter is use ... -
Magento: 后台获取menu链接 Getting the admin panel urls
2016-04-28 02:34 739The url for customer page in t ... -
Magento : 调用文件上传 upload file frontend
2016-04-27 01:25 1319bool mkdir ( string $pathname ... -
Magento: 自定义用户登录导向页面 Redirect Customer to Previous Page After Login
2016-04-26 02:45 1822Configuration Settings – L ... -
Magento: 代替flash上传 How to disable Flash uploader in Magento (product images and
2016-04-06 05:04 10871. 替换产品页flash上传按钮 - 使用 Du ... -
Magento: 在客户账户中添加自定义链接 My Account Add Link
2016-04-05 14:05 1203This extension add new link an ... -
Magento: 根据产品属性加载产品信息 Load A Category or Product by an Attribute
2016-03-26 01:35 900Load a Product by ID <?ph ... -
Magento模块开发之数据库SQL操作方法说明
2016-03-26 01:31 1298今天主要来看Magento中的Mysql4/Resource ...
相关推荐
magento导出数据sql,magento批量导出数据,magento批量上传数据。
magento删除订单sql,通过phpmyadmin清空订单
magento SQL常用命令,数据库操作
3 专业与社群支援不像是其他的开放原始码解决方案, Magento 提供专业、可信赖的支援,您也可以从热情的社群中取得协助,国内也有Magento的爱好者创建中文社区,比如Magentoo.com。4 完整的扩充性无论网站经过了一夜...
像是产品标签、多送货地址或产品比较系统等功能,您不需要支付额外的费用来取得,在现成的 Magento 系统中,您可以发现更多。 3 专业与社群支援 不像是其他的开放原始码解决方案, Magento 提供专业、可信赖的支援...
magento用户使用手册
magento产品名称及描述批量修改sql
magento中文用户指南
magento中文手册 magento教程
有magento大师编写的免费magento中文开发教程。
本文是Magento的中文使用说明书,资料来源于网络,并对翻译错误的地方进行了修正
magento用户中文操作手册,学习magento后台管理
custom-reports, 使用SQL定制Magento报告 使用Google图表 API 定制Magento报告使用custom或者使用magento管理网格( ),轻松创建报表,并使用magento来显示它们。 功能报告结果表定义列配置或者允许自动配置的能力。...
NULL 博文链接:https://vb2005xu.iteye.com/blog/1624479
magento中文操作手册,详细介绍magento的具体操作
magento通过mysql语句批量导出产品及其自定义属性
magento最新版中文语言包 Magento (麦进斗) 是一套专业开源的电子商务系统。Magento设计得非常灵活,具有模块化架构体系和丰富的功能。易于与第三方应用系统无缝集成。其面向企业级应用,可处理各方面的需求,...
magento 官方中文语言包
第一次上传文件,请多多关照。 自己写的magento2中batch压缩图片,以及把压缩文件的原图备份起来。
本文档提供了创建和 Magento的应用程序进行安装定制的店面主题的说明。它描述 Magento的内容呈现过 程,并说明该系统的对有效地建立一个主题要求的范围内的观点层。该文件还走过日常前端开发人员的任 务。