前几天刚做完 magento的订单导出 接着来做zencart的导出 经过对比后发现 magento 基本不会涉及到sql强大的函数几乎可以满足你的任何要求 所以相对zencart来说要轻松的多
而zencart呐 几乎可以通过sql满足你的任和要求
以下就是我做zencart导出的一些测试sql
#其这个也不用怎么解释看sql语句自然就知道是做什么的
#获取订单状态的状态对应值
select orders_status_id, orders_status_name
from orders_status
where language_id = '1' order by orders_status_id limit 0,10;
+------------------+---------------------------+
| orders_status_id | orders_status_name |
+------------------+---------------------------+
| 1 | Pending |
| 2 | Processing |
| 3 | Payment-Failed |
| 4 | Paid |
| 5 | Update |
| 6 | Shipped |
| 8 | Clearance-through-Customs |
| 9 | Delivered |
| 12 | Complete |
| 13 | Canceled |
+------------------+---------------------------+
#查询 orders 14731 是否存在
select orders_id from orders
where orders_id = '14731';
+-----------+
| orders_id |
+-----------+
| 14731 |
+-----------+
#查询顾客信息 及定单状态
select customers_name, customers_email_address, orders_status,
date_purchased from orders
where orders_id = '14731';
+--------------------+-------------------------+---------------+---------------------+
| customers_name | customers_email_address | orders_status | date_purchased |
+--------------------+-------------------------+---------------+---------------------+
| Jefey Ric | root@weicot.com | 2 | 2015-08-03 21:40:51 |
+--------------------+-------------------------+---------------+---------------------+
#查询产品的下载属性
SELECT orders_products_id,
orders_products_filename,
products_prid
from
orders_products_download
WHERE orders_products_download_id='';
+-----------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+----------------+
| orders_products_download_id | int(11) | NO | PRI | NULL | auto_increment |
| orders_id | int(11) | NO | MUL | 0 | |
| orders_products_id | int(11) | NO | MUL | 0 | |
| orders_products_filename | varchar(255) | NO | | | |
| download_maxdays | int(2) | NO | | 0 | |
| download_count | int(2) | NO | | 0 | |
| products_prid | tinytext | NO | | NULL | |
+-----------------------------+--------------+------+-----+---------+----------------+
#查询订单客户信息
select
orders_id,
delivery_company,
delivery_street_address,
delivery_postcode,
delivery_name,
customers_telephone,
delivery_state,
delivery_city,
date_purchased,
delivery_country
from orders order by date_purchased asc limit 0,10;
![SV))HQ{X[]H~M]S_BF@FBS2](http://www.weicot.com/wp-content/uploads/2015/08/SVHQXHMS_BF@FBS2.jpg)
#查询定单产品信息 SELECT * FROM `orders_products` WHERE orders_id='14731';
![)TB(~JHBFHL~AXK]6661A[K](http://www.weicot.com/wp-content/uploads/2015/08/TBJHBFHLAXK6661AK.png)
来看下结构
#查询订单产品属性 SELECT * FROM orders_products_attributes WHERE orders_id = '14731' LIMIT 0 , 30; +----------------------------------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------------------------+---------------+------+-----+---------+----------------+ | orders_products_attributes_id | int(11) | NO | PRI | NULL | auto_increment | | orders_id | int(11) | NO | MUL | 0 | | | orders_products_id | int(11) | NO | | 0 | | | products_options | varchar(32) | NO | | | | | products_options_values | text | NO | | NULL | | | options_values_price | decimal(15,4) | NO | | 0.0000 | | | price_prefix | char(1) | NO | | | | | product_attribute_is_free | tinyint(1) | NO | | 0 | | | products_attributes_weight | float | NO | | 0 | | | products_attributes_weight_prefix | char(1) | NO | | | | | attributes_discounted | tinyint(1) | NO | | 1 | | | attributes_price_base_included | tinyint(1) | NO | | 1 | | | attributes_price_onetime | decimal(15,4) | NO | | 0.0000 | | | attributes_price_factor | decimal(15,4) | NO | | 0.0000 | | | attributes_price_factor_offset | decimal(15,4) | NO | | 0.0000 | | | attributes_price_factor_onetime | decimal(15,4) | NO | | 0.0000 | | | attributes_price_factor_onetime_offset | decimal(15,4) | NO | | 0.0000 | | | attributes_qty_prices | text | YES | | NULL | | | attributes_qty_prices_onetime | text | YES | | NULL | | | attributes_price_words | decimal(15,4) | NO | | 0.0000 | | | attributes_price_words_free | int(4) | NO | | 0 | | | attributes_price_letters | decimal(15,4) | NO | | 0.0000 | | | attributes_price_letters_free | int(4) | NO | | 0 | | | products_options_id | int(11) | NO | | 0 | | | products_options_values_id | int(11) | NO | | 0 | | | products_prid | tinytext | NO | | NULL | | +----------------------------------------+---------------+------+-----+---------+----------------+ #查询产品属性 SELECT * FROM products WHERE products_id = '30012'; +----------------------------------+---------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------------------+---------------+------+-----+---------------------+----------------+ | products_id | int(11) | NO | PRI | NULL | auto_increment | | products_type | int(11) | NO | | 1 | | | products_quantity | float | NO | | 0 | | | products_model | varchar(256) | YES | MUL | NULL | | | products_image | varchar(255) | YES | | NULL | | | products_price | decimal(15,4) | NO | | 0.0000 | | | products_virtual | tinyint(1) | NO | | 0 | | | products_date_added | datetime | NO | MUL | 0001-01-01 00:00:00 | | | products_last_modified | datetime | YES | | NULL | | | products_date_available | datetime | YES | MUL | NULL | | | products_weight | float | NO | | 0 | | | products_status | tinyint(1) | NO | MUL | 0 | | | products_tax_class_id | int(11) | NO | | 0 | | | manufacturers_id | int(11) | YES | MUL | NULL | | | products_ordered | float | NO | MUL | 0 | | | products_quantity_order_min | float | NO | | 1 | | | products_quantity_order_units | float | NO | | 1 | | | products_priced_by_attribute | tinyint(1) | NO | | 0 | | | product_is_free | tinyint(1) | NO | | 0 | | | product_is_call | tinyint(1) | NO | | 0 | | | products_quantity_mixed | tinyint(1) | NO | | 0 | | | product_is_always_free_shipping | tinyint(1) | NO | | 0 | | | products_qty_box_status | tinyint(1) | NO | | 1 | | | products_quantity_order_max | float | NO | | 0 | | | products_sort_order | int(11) | NO | MUL | 0 | | | products_discount_type | tinyint(1) | NO | | 0 | | | products_discount_type_from | tinyint(1) | NO | | 0 | | | products_price_sorter | decimal(15,4) | NO | MUL | 0.0000 | | | master_categories_id | int(11) | NO | MUL | 0 | | | products_mixed_discount_quantity | tinyint(1) | NO | | 1 | | | metatags_title_status | tinyint(1) | NO | | 0 | | | metatags_products_name_status | tinyint(1) | NO | | 0 | | | metatags_model_status | tinyint(1) | NO | | 0 | | | metatags_price_status | tinyint(1) | NO | | 0 | | | metatags_title_tagline_status | tinyint(1) | NO | | 0 | | +----------------------------------+---------------+------+-----+---------------------+----------------+