php - Zen cart: I would like to query from a specific category its products name, price, image, description, and attributes -

i using following query:

<?php  // retrieve products database  global $db; $q1 = $db->execute("select * products"); $q1_items = array(); $q2_items = array();  while (!$q1->eof){ $q1_items[] = $q1->fields; $q1->movenext(); } foreach ($q1_items $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 } ?> 

the issue having doesn't not pull product name , able query each product_description table_products_description , products_attributes.

that's because products_name , products_description in table products_description (or more specific in table_products_description), not in table products (table_products).

to basic information (except attributes) should execute following query:

$q = $db->execute("select * " . table_products . " p left join " . table_products_description . " pd on(p.products_id = pd.products_id) language_id = " . (int)$_session['languages_id']); $items = array(); while(!$q->eof) {     // let's attribues product     $productinfo = $q->fields;     $qattr = $db->execute("select * " . table_products_attributes . " products_id = " . (int)$q->fields['products_id']);     $attr = array();     while(!$qattr->eof) {         $attr[] = $qattr->fields;         $qattr->movenext();     }     $productinfo['attributes'] = $attr;     $items[] = $productinfo;     $q->movenext(); }  // let's output foreach($items $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>'; } 

note code not products specific category - gets products, deactivated. there several ways products specific category vary in performance. unfortunately there's no best way because depends on data. if products wish retrieve belong category categories_id of 5 , it's primary category it's enough add first query "where master_categories_id = 5". if category not master category products things bit more complicated because need access products_to_categories table causes performance hit sites many products. if don't know/don't care performance can change first query to: (assuming know categories_id of category):

$q = $db->execute("select * " . 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 , p2c.categories_id = your_category_id) pd.products_id not null , p2c.products_id not null , language_id = " . (int)$_session['languages_id']); 

to rid of inactive products execute

$q = $db->execute("select * " . 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 , p2c.categories_id = your_category_id) products_status = 1 , pd.products_id not null , p2c.products_id not null , language_id = " . (int)$_session['languages_id']); 

(actually check on pd.products_id not null not needed because check pd.language_id.)

---

edit version without attributes

$q = $db->execute("select * " . table_products . " p left join " . table_products_description . " pd on(p.products_id = pd.products_id) language_id = " . (int)$_session['languages_id']); $items = array(); while(!$q->eof) {     $items[] = $q->fields;     $q->movenext(); }  // let's output foreach($items $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>'; }