Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

All products Listing


iwik

Recommended Posts

Hey there folks,

 

I've got the All Products Enhanced contribution installed, and it's working great. No errors or anything.

 

But, it's showing multiple instances of each product!

For example if I have one product linked to 4 categories, that product will be listed 4 times on the all_products.php file.

 

So far, I've made it show everything but 1 certain category, but is there something else I can add to my sql query that will only show 1 instance of the product, rather than multiple times because it's linked to more than 1 category?

You can see what I mean at the development site: http://store.ixplodingiwik.com/all_products.php

 

My sql query is as follows:

 

$listing_sql = "select p.products_id, pd.products_name, pd.products_info, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, c.categories_id, m.authors_name from " . TABLE_PRODUCTS . " p left join " . TABLE_AUTHORS . " m on p.authors_id = m.authors_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_PRODUCTS_TO_CATEGORIES . " c on p.products_id = c.products_id where p.products_status = '1' and c.categories_id != '42' order by pd.products_name";

 

Or, would it be better off being used in the display of the listing itself?

 

The code for that is:

 

if ($listing_split->number_of_rows > 0) {

$row = 0;
$col = 0;

   $listing = tep_db_query($listing_split->sql_query);
   while ($listing_values = tep_db_fetch_array($listing)) {

     $listing_values['products_name'] = tep_get_products_name($listing_values['products_id']);

     $lc_text= '<table width="100%" borders="0" cellpadding="0" cellspacing="0"><tr><td width="110" valign="top">' . tep_image(DIR_WS_IMAGES . $listing_values['products_image'], $listing_values['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></td><td align="left"> <span class="ListingsTitle">' . $listing_values['products_name'] . '</span><br><span class="ListingsAuthor">' . $listing_values['authors_name'] . 
'</span><br><span class="ListingsText">ISBN: ' . $listing_values['products_model'] . '<br>' . $listing_values['products_info'] . '</span></td>';

 if ($listing_values['specials_new_products_price']) {
       $lc_text .= '<td width="20" align="right" valign="top" class="ListingsPrice"><s>' .  $currencies->display_price($listing_values['products_price'], tep_get_tax_rate($listing_values['products_tax_class_id'])) . '</s><span class="productSpecialPrice">' . $currencies->display_price($listing_values['specials_new_products_price'], tep_get_tax_rate($listing_values['products_tax_class_id'])) . '</span></td>';
  } else {
       $lc_text .= '<td width="20" align="right" valign="top" class="ListingsPrice">' .$currencies->display_price($listing_values['products_price'], tep_get_tax_rate($listing_values['products_tax_class_id'])) . '</td>';
     }

$lc_text .= '<td width="110" valign="top" align="right"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $listing_values['products_id']) . '"><img src="../includes/languages/english/images/buttons/button_more_info.gif" border="0"></a><br><br><a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $listing_values['products_id'], 'NONSSL') . '">' . tep_image_button('button_buy_now.gif', TEXT_BUY . $listing_values['products_name'] . TEXT_NOW) . '</a></td></tr><tr><td> </td></tr></table>';

     $info_box_contents[$row][$col] = array('align' => 'center', 'params' => 'class="smallText" width="100%" valign="top"',
                                          'text' => $lc_text);

     $col ++;
     if ($col > PRODUCT_LIST_COL_NUM-1) {
       $col = 0;
       $row ++;
     }
   }

 

I guess it would have something to do with looking at the product_id and then telling it to only display the first occurance of it.. or something? like..

if product_id has already been shown {

go on to the next one

} else {

show the product

}

 

Any guidance would be awesome.

 

Cheers,

Jane

Link to comment
Share on other sites

Hey there folks,

 

I've got the All Products Enhanced contribution installed, and it's working great. No errors or anything.

 

But, it's showing multiple instances of each product!

For example if I have one product linked to 4 categories, that product will be listed 4 times on the all_products.php file.

 

So far, I've made it show everything but 1 certain category, but is there something else I can add to my sql query that will only show 1 instance of the product, rather than multiple times because it's linked to more than 1 category?

You can see what I mean at the development site: http://store.ixplodingiwik.com/all_products.php

 

My sql query is as follows:

 

$listing_sql = "select p.products_id, pd.products_name, pd.products_info, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, c.categories_id, m.authors_name from " . TABLE_PRODUCTS . " p left join " . TABLE_AUTHORS . " m on p.authors_id = m.authors_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_PRODUCTS_TO_CATEGORIES . " c on p.products_id = c.products_id where p.products_status = '1' and c.categories_id != '42' order by pd.products_name";

 

Or, would it be better off being used in the display of the listing itself? 

 

The code for that is:

 

if ($listing_split->number_of_rows > 0) {

$row = 0;
$col = 0;

   $listing = tep_db_query($listing_split->sql_query);
   while ($listing_values = tep_db_fetch_array($listing)) {

     $listing_values['products_name'] = tep_get_products_name($listing_values['products_id']);

     $lc_text= '<table width="100%" borders="0" cellpadding="0" cellspacing="0"><tr><td width="110" valign="top">' . tep_image(DIR_WS_IMAGES . $listing_values['products_image'], $listing_values['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a></td><td align="left"> <span class="ListingsTitle">' . $listing_values['products_name'] . '</span><br><span class="ListingsAuthor">' . $listing_values['authors_name'] . 
'</span><br><span class="ListingsText">ISBN: ' . $listing_values['products_model'] . '<br>' . $listing_values['products_info'] . '</span></td>';

 if ($listing_values['specials_new_products_price']) {
       $lc_text .= '<td width="20" align="right" valign="top" class="ListingsPrice"><s>' .  $currencies->display_price($listing_values['products_price'], tep_get_tax_rate($listing_values['products_tax_class_id'])) . '</s><span class="productSpecialPrice">' . $currencies->display_price($listing_values['specials_new_products_price'], tep_get_tax_rate($listing_values['products_tax_class_id'])) . '</span></td>';
  } else {
       $lc_text .= '<td width="20" align="right" valign="top" class="ListingsPrice">' .$currencies->display_price($listing_values['products_price'], tep_get_tax_rate($listing_values['products_tax_class_id'])) . '</td>';
     }

$lc_text .= '<td width="110" valign="top" align="right"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $listing_values['products_id']) . '"><img src="../includes/languages/english/images/buttons/button_more_info.gif" border="0"></a><br><br><a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $listing_values['products_id'], 'NONSSL') . '">' . tep_image_button('button_buy_now.gif', TEXT_BUY . $listing_values['products_name'] . TEXT_NOW) . '</a></td></tr><tr><td> </td></tr></table>';

     $info_box_contents[$row][$col] = array('align' => 'center', 'params' => 'class="smallText" width="100%" valign="top"',
                                          'text' => $lc_text);

     $col ++;
     if ($col > PRODUCT_LIST_COL_NUM-1) {
       $col = 0;
       $row ++;
     }
   }

 

I guess it would have something to do with looking at the product_id and then telling it to only display the first occurance of it.. or something? like..

if product_id has already been shown {

go on to the next one

} else {

show the product

}

 

Any guidance would be awesome.

 

Cheers,

Jane

 

 

you could try "select distinct"

Treasurer MFC

Link to comment
Share on other sites

Ok.. I'm dumb. What exactly does that do? Does it stop it grabbing two of the same product_id array-type things?

 

And how do I use it? Do I just insert it at the beginning of my query?

 

eg

$listing_sql = "select distinct p.products_id, pd.products_name, pd.products_info, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status....

Link to comment
Share on other sites

Ok.. I'm dumb. What exactly does that do? Does it stop it grabbing two of the same product_id array-type things?

 

And how do I use it? Do I just insert it at the beginning of my query?

 

eg

$listing_sql = "select distinct p.products_id, pd.products_name, pd.products_info, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status....

 

yes

Treasurer MFC

Link to comment
Share on other sites

  • 1 month later...

I've just tried adding that 'select distinct' statement to the SQL query and it hasn't changed the listing at all.

 

I think the main problem is that there are several different versions of the data I guess, so the one title will have two or three different cpath or category values, so none of the rows are exactly the same as one another.

 

Thanks anyway Amanda.

 

Does anyone else have any ideas on how I can make it only show each product once, rather than mutiple times because the product is linked to more than one category?

 

Ok, thanks a bunch Amanda.

 

I'll give it a whirl and see how it goes.

Link to comment
Share on other sites

I've worked out how to achieve what I wanted. Through a lot of playing and endlessly flicking back and forth through a little 'Teach Yourself SQL in 10 minutes' book I've come up with this.

 

$listing_sql = "select distinct(p.products_id), pd.products_name, pd.products_info, p.products_model, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, p.products_date_added, c.categories_id, m.authors_name from " . TABLE_PRODUCTS . " p left join " . TABLE_AUTHORS . " m on p.authors_id = m.authors_id left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_PRODUCTS_TO_CATEGORIES . " c on p.products_id = c.products_id where p.products_status = '1' and c.categories_id != '42' group by p.products_id order by pd.products_name";

 

Which only makes one of each title show up, rather than multiple copies because it was linked to more than one category.

 

Thanks Amanda for giving me a starting point, I just had to use it correctly.

 

Btw, I have authors tables and fields rather than manufacturers, so change it back to suit if anyone ever needs to use this code for anything.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...