Random New Products Shows Specials inc SubCats 1.0

[BigLar]

This shouldn't be the case with a default MS2.2 installation. Perhaps you've installed other contributions which deal with the look of your store?

 

That must be it. I'm using "Turbo Charged" from vn2designs.com

[garjowan]

// start random new products
$new_products_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and s.status = '1' order by rand() limit " . MAX_DISPLAY_SPECIAL_PRODUCTS);

// end random new products

 

You will notice on the last line I have chaned what is normally "order by s.products_id desc" or "order by p.products_date_added desc" to "order by rand()"

 

That is all you need to do to get random queries from the database! I hope this helps someone I had been trying to work it out for ages!

 

 

THANK YOU!...LOL!!!

I have been messing with contri's trying to get this to work with my templates and no go... this did the trick, I can't believe I didn't think of it!

 

Thanks again!

[motorcity]

As usual this is the place to find answers. Thank you all!

I've been looking for the reason why the MAX_DISPLAY_SPECIAL_PRODUCTS changed in admin or changed in the database had no effect on our scrolling specials.

We could only limit the number displayed by switching the active/inactive buttons on the products in specials.

We run monthly published circulars and displaying all those products in the scroll is clumsy to say the least, ridiculous if you have a good old fashioned dialup connection.

Inspired (in about 5 minutes of reading this thread) I changed our specials_scroll.php

*/

 $rp_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by rand() limit " . MAX_DISPLAY_SPECIAL_PRODUCTS);
 if (tep_db_num_rows($rp_query)) {
?>

Originally the last part was like this...

and s.status = '1' order by s.specials_date_added desc");
 if (tep_db_num_rows($rp_query)) {
?>

There was no reference to max display at all! Duh??

Now we have a limit and thier listed at random

Thanks again!

[Becki]

Hi,

 

Has anyone managed to combine this contribution with 'Chemos' query save for new_products.php - his instructions are below:

 

I have the random new_products.php working with the query save but it doesn't show the special price correctly. So if anyone has done it or quickly knows how it would be great...It saves 18 queries (if showing 9 new products) over the present new_products file. Maybe these 18 queries are not worth chasing after...?

 

Thanks

becki

 

USE join command

In includes/modules/new_products.php find this code:

CODE

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
  $new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
  $new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
}


...change it to this code:

CODE

  if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
  $new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id where products_status = '1' and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
} else {
  $new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price, pd.products_name from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join products_description pd on p.products_id = pd.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS);
}



Next, find this line and comment it out like htis:

CODE

#$new_products['products_name'] = tep_get_products_name($new_products['products_id']);

[Guest]

I have been looking through this support forum and the osCommerce contributions for a particular solution to, what appears to be, a unique problem.

 

I am currently building a osCommerce shop for a furniture retailer, on the home page I have created nine boxes that will display the latest or a random product from a particular category.

 

In my search for a solution to this problem I came across the 'Random New Products' contribution and have successfully modified this to display a random new product on my home page.

 

I now have repeated this code to the other eight boxes so that now I get nine individual boxes currently displaying the same random new product.

 

What I now need to do is amend the code in each of the nine boxes to display the latest or a random product from only one specific category, this modification will then be added the the remaining eight boxes to produce the lastest or random product from nine different categories.

 

The 'SubCats' contribution works well when you wish to show a random product when you are in that particular category, what I need is to show these random products on the home page.

 

Below is the code from one of the boxes that needs modifying, if you can help it would be greatly appreciated.

 

<!-- new_products //-->

<style type="text/css">
<!--
#apDiv1 {
position:absolute;
width:154px;
height:90px;
z-index:10;
left: 175px;
top: 710px;
}
-->
</style>
<div id="apDiv1">
 <?php
/*
 $Id: specials.php,v 1.31 2003/06/09 22:21:03 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 [url="http://www.oscommerce.com"]http://www.oscommerce.com[/url]

 Copyright © 2003 osCommerce

 Released under the GNU General Public License
*/

 if ($random_product = tep_random_select("select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added desc limit " . MAX_RANDOM_SELECT_SPECIALS)) {
?>
<!-- random wardrobe products //-->
<?php
   $info_box_contents = array();
   $info_box_contents[] = array('text' => BOX_HEADING_SPECIALS1);

   new infoBoxHeading($info_box_contents, false, false, tep_href_link(FILENAME_SPECIALS)); 

 if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
// start random new products
   $new_products_query = tep_db_query("select p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' order by p.products_date_added desc limit " . MAX_RANDOM_SELECT_NEW);
 } else {   

$num = (int) MAX_DISPLAY_NEW_PRODUCTS;
   $new_products_query = tep_db_query("select distinct p.products_id, p.products_image, p.products_tax_class_id, if(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . (int)$new_products_category_id . "' and p.products_status = '1' order by p.products_date_added desc limit " . MAX_RANDOM_SELECT_NEW);
// end random new products    
 }
// start random new products
 // To configure:
 //
 // Under Admin - Configuration - Maximum Values
 // Set the following:
 //  'Selection of Random New Products' 
 //   (MAX_RANDOM_SELECT_NEW) - Sets how many of the most recent new product entries are queried
 //  'New Products Module'
 //   (MAX_DISPLAY_NEW_PRODUCTS) - Sets how many random new products are displayed on screen
 //
 $row = 0;
 $col = 0;
 $pCount = 0;
 $info_box_contents = array();
 $found_products = array();
 $num_rows = tep_db_num_rows($new_products_query);
 if (MAX_DISPLAY_NEW_PRODUCTS > MAX_RANDOM_SELECT_NEW) {
   // don't allow more new products than will be queried
   $max_new_products = MAX_RANDOM_SELECT_NEW;
 } else {
   $max_new_products = MAX_DISPLAY_NEW_PRODUCTS;
 }
 if ($num_rows < $max_new_products) {
 	$max_new_products = $num_rows;
 }
//  echo "num_rows: " . $num_rows . " max_new_products " . $max_new_products . "<br>";
 if ($num_rows > 0) {        
   while ($pCount < $max_new_products) {    
     // choose a random row 
     $random_product = '';
     $random_row = tep_rand(0, ($num_rows - 1));
     tep_db_data_seek($new_products_query, $random_row);
     $random_product = tep_db_fetch_array($new_products_query);
     // see if found already, if not use else skip
     $found = 0;
     for ($ii=0; $ii < $pCount; $ii++) {
       if ($found_products[$ii] == $random_product['products_id']) {
         $found = 1;
         break;
       }
     }
     if ($found == 0) {
       // keep track of found id's
       $found_products[$pCount] += $random_product['products_id'];
       $pCount ++; 
       // add to display array
       $random_product['products_name'] = tep_get_products_name($random_product['products_id']);
       $info_box_contents[$row][$col] = array('align' => 'center',
                                          'params' => 'class="smallText" width="33%" valign="top"',
                                          'text' => '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $random_product['products_image'], $random_product['products_name'], 142, 141) . '</a><br><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $random_product['products_id']) . '">' . $random_product['products_name'] . '</a><br>' . $currencies->display_price($random_product['products_price'], tep_get_tax_rate($random_product['products_tax_class_id'])));
       $col ++;
       if ($col > 2) {
         $col = 0;
         $row ++;
       }
     } // found
   } // while pCount
 } // num_rows  
// end random new products

 new contentBox($info_box_contents);
?>
<!-- randon wardrobe products eof //-->
 <img src="http://www.lawrencesfurniturecentre.co.uk/oscommerce/images/a2BottomCorners.jpg" alt="Click Item for more Information" width="154" height="20" />
 <?php
 }
?>
</div>
<!-- new_products_eof //-->

 

If you wish to see the boxes in action you can see them at www.lawrencesfurniturecentre.co.uk, please note this site is underconstruction so some parts may look odd or currently don't work.

[fan4chevy]

Hi,

 

I like the contribution. However, I am having an issue where it appears to be working properly in all areas except for the actual individual product page. It seems to populate the same meta, description, etc as the main category page for the product.

 

Any idea what I need to do to get the meta, etc to populate for the actual individual product page?

 

Thanks,

 

Charles

[dailce]

Hi, I notice that if a product has a quantity less than 0 it will still be chosen. Is there a way to only select products that have a quantity greater than zero?

 

How would I modify the following to do that?

 

$sql_query[0] = tep_db_query("select p.products_id, p.products_quantity, p.products_image, p.products_tax_class_id, s.status, s.specials_new_products_price, p.products_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1'  order by p.products_ordered asc  limit " . MAX_DISPLAY_NEW_PRODUCTS);

[edmond1815]

Hello,

 

I have random specials, on my site, and it is ok.

But i guess i have a spelling mistake, or code error, because when i clik to show all specials i got this error:

 

"Fatal error: Call to undefined function tep_random_select() in /var/www/virtual/borner.ro/htdocs/specials.php on line 13"

 

Arround line 13 i have this code:

if ($random_product = tep_random_select("select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and p.products_id = s.products_id and pd.products_id = s.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added desc limit " . MAX_RANDOM_SELECT_SPECIALS)) {

?>

 

What is wrong?

 

 

Thank you,

 

Edmond