1.这种方式的联合查询:
/**
* 通过order_id 获取某个订单的所有商品
* @param int $order_id
* @return array
*/
public function get_order_product($order_id, $param=1) {
if (empty($order_id)) {
return;
}
if ($param == 1) {
$sql = "SELECT
g.*, d.brand_name, d.brand_id, b.mini
FROM
(SELECT a.*
FROM " . TBL_PRE . "orders_item a
WHERE a.order_id = '{$order_id}') AS g
LEFT JOIN " . TBL_PRE . "products_images b ON g.product_id = b.product_id AND b.is_default = 1
LEFT JOIN " . TBL_PRE . "products e ON g.product_id = e.product_id
LEFT JOIN " . TBL_PRE . "goods c ON c.goods_id = e.goods_id
LEFT JOIN " . TBL_PRE . "goods_brand d ON d.brand_id = c.brand_id";
} elseif ($param == 2) {
$sql = "SELECT * FROM " . TBL_PRE . "orders_item WHERE order_sn = '{$order_id}'" ;
}
return $this->db->get_all($sql);
}
2.
/**
* 初始化购物车
*/
public function init_minicart ($member_id) {
if ( empty($member_id) ) {
return;
}
$sql = "SELECT
a.*, b.price, c.product_name, c.product_id, d.mini, f.brand_name, f.brand_id
FROM
( SELECT * FROM " . TBL_PRE . "cart WHERE member_id = '{$member_id}' ) AS a
LEFT JOIN " . TBL_PRE . "product_spec b ON a.product_spec_id = b.product_spec_id
LEFT JOIN " . TBL_PRE . "products c ON b.product_id = c.product_id
LEFT JOIN " . TBL_PRE . "products_images d ON b.product_id = d.product_id AND d.is_default = 1
LEFT JOIN " . TBL_PRE . "goods e ON e.goods_id = c.goods_id
LEFT JOIN " . TBL_PRE . "goods_brand f ON f.brand_id = e.brand_id";
$cart = $this->db->get_all($sql);
return $cart;
}
3.
/**
* 获得我的收藏夹
* @param $member_id
*/
function get_my_collec( $member_id, $page, $page_count ) {
if ( empty($member_id) ) {
return array();
}
$sql_collec = "SELECT
*
FROM
" . TBL_PRE . "member_collection
WHERE
member_id = '{$member_id}'
ORDER BY create_time DESC";
if ( !empty($page) && !empty($page_count) ) {
$page_start = (max(1,$page)-1)*$page_count;
$sql_collec .= " LIMIT {$page_start},{$page_count}";
}
$collec_list = $this->db->get_all($sql_collec);
if ( empty($collec_list) ) {
return;
}
return $collec_list;
}