Zen 购物车:我想从特定类别中查询其产品名称、价格、图片、描述和属性 [英] Zen cart: I would like to query from a specific category its products name, price, image, description, and attributes

问题描述

我正在使用以下查询:

<?php
 // retrieve products from database
 global $db;
$q1 = $db->Execute("select * from products");
$q1_items = array();
$q2_items = array();

while (!$q1->EOF){
$q1_items[] = $q1->fields;
$q1->MoveNext();
}
foreach ($q1_items as $item => $items) {
echo '<p><a href="index.php?main_page=product_info&products_id='. $items['products_id'] .'"><img src="images/'. $items['products_image'].'" alt="'. $items['products_name'].'" title="'. $items['products_name'].'" /></a>';
    echo ''.$items['products_price']. ''; ?>

<?php }
?>

我遇到的问题是它不会提取产品名称，我希望能够从 TABLE_PRODUCTS_DESCRIPTION 和 Products_attributes 中查询每个 product_description.

The issue I am having is it doesn't not pull the product name and I would like to be able to query each product_description from TABLE_PRODUCTS_DESCRIPTION and Products_attributes.

推荐答案

那是因为 products_name 和 products_description 在表 products_description 中(或者更具体地在 TABLE_PRODUCTS_DESCRIPTION 中)，而不是在表 products (TABLE_PRODUCTS) 中.

That's because products_name and products_description is in table products_description (or to be more specific in TABLE_PRODUCTS_DESCRIPTION), not in table products (TABLE_PRODUCTS).

要获取所有基本信息(属性除外)，您应该执行以下查询:

To get all basic information (except attributes) You should execute following query:

$q = $db->Execute("SELECT * FROM " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON(p.products_id = pd.products_id) WHERE language_id = " . (int)$_SESSION['languages_id']);
$items = array();
while(!$q->EOF) {
    // let's get all attribues for product
    $productInfo = $q->fields;
    $qattr = $db->Execute("SELECT * FROM " . TABLE_PRODUCTS_ATTRIBUTES . " WHERE products_id = " . (int)$q->fields['products_id']);
    $attr = array();
    while(!$qattr->EOF) {
        $attr[] = $qattr->fields;
        $qattr->MoveNext();
    }
    $productInfo['attributes'] = $attr;
    $items[] = $productInfo;
    $q->MoveNext();
}

// now let's output it
foreach($items as $item) {
    echo '<p><a href="index.php?main_page=product_info&products_id='. $item['products_id'] .'"><img src="images/'. $item['products_image'].'" alt="'. $item['products_name'].'" title="'. $items['products_name'].'" /></a>';
    echo $items['products_price'] . '</p>';
}

但是请注意，此代码不从特定类别获取产品 - 它获取所有产品，甚至那些已停用的产品.有多种方法可以从特定类别中获取产品，但它们的性能各不相同.不幸的是，没有最好的方法来做到这一点，因为这取决于数据.如果您希望检索的产品属于categories_id 为5 的类别并且它是它们的主要类别，则添加到第一个查询WHERE master_categories_id = 5"就足够了.但是如果类别不是这些产品的主类别，事情会变得更加复杂，因为我们需要访问 products_to_categories 表，这会导致具有许多产品的网站的性能下降.如果您不太了解/不太关心性能，您可以将第一个查询更改为:(假设您已经知道您的类别的 category_id):

Note however that this code does NOT get products from specific category - it gets all products, even those that are deactivated. There are several ways to get products from specific category but they vary in performance. Unfortunately there's no best way to do this because it depends on the data. If the products You wish to retrieve belong to category with categories_id of 5 and it's their primary category it's enough to add to first query "WHERE master_categories_id = 5". But if the category is not master category for those products things get bit more complicated because We need to access products_to_categories table which causes performance hit for sites with many products. If You don't know/don't care about performance that much You can change first query to: (assuming that You already know categories_id of Your category):

$q = $db->Execute("SELECT * FROM " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON(p.products_id = pd.products_id LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON(p.products_id = p2c.products_id AND p2c.categories_id = YOUR_CATEGORY_ID) WHERE pd.products_id IS NOT NULL AND p2c.products_id IS NOT NULL AND language_id = " . (int)$_SESSION['languages_id']);

要删除不活跃的产品，请执行

To get rid of inactive products execute

$q = $db->Execute("SELECT * FROM " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON(p.products_id = pd.products_id LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON(p.products_id = p2c.products_id AND p2c.categories_id = YOUR_CATEGORY_ID) WHERE products_status = 1 AND pd.products_id IS NOT NULL AND p2c.products_id IS NOT NULL AND language_id = " . (int)$_SESSION['languages_id']);

(实际上不需要检查 pd.products_id IS NOT NULL 因为我们已经检查了 pd.language_id.)

(Actually check on pd.products_id IS NOT NULL is not needed because We already check pd.language_id.)

没有属性的编辑版本

$q = $db->Execute("SELECT * FROM " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON(p.products_id = pd.products_id) WHERE language_id = " . (int)$_SESSION['languages_id']);
$items = array();
while(!$q->EOF) {
    $items[] = $q->fields;
    $q->MoveNext();
}

// now let's output it
foreach($items as $item) {
    echo '<p><a href="index.php?main_page=product_info&products_id='. $item['products_id'] .'"><img src="images/'. $item['products_image'].'" alt="'. $item['products_name'].'" title="'. $items['products_name'].'" /></a>';
    echo $items['products_price'] . '</p>';
}

这篇关于Zen 购物车:我想从特定类别中查询其产品名称、价格、图片、描述和属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！