Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Random New Products (HELP)


Guest

Recommended Posts

Nice worked.

 

Quick question. Would it be possible to have the subcategories also show random products. Not for price but just random. If you check out my site www.premierwebbing.com/shop/ and go to the baseball section you'll see what I mean.

 

The first 2 are random but after that they are the same. Listed alphabetically.

 

Thanks again. Also do you have a paypal account would love to donate to you for the help.

Link to comment
Share on other sites

Nice worked.

 

Quick question. Would it be possible to have the subcategories also show random products. Not for price but just random. If you check out my site www.premierwebbing.com/shop/ and go to the baseball section you'll see what I mean.

 

The first 2 are random but after that they are the same. Listed alphabetically.

 

Thanks again. Also do you have a paypal account would love to donate to you for the help.

Hmmm, I went to the site, but can't see what you're talking about... I don't see any new product's box anywhere, so I'm assuming that maybe the product listing I'm seeing is that, but they appear to completely randomize everytime I load the page, rather than just the first 2 like you'd said... You should be able to stick this into subcategories as well though, using 1 of 2 methods... One is that you can just make a copy of that module file, and remove the checks for price (or just set price = 0), since you don't want subcategories to worry about price, and the other is just to do a check against $category_depth, and if it's at the top level, declare the query with price, otherwise, declare it without price, or set price to 0 in those circumstances...

 

Richard.

Richard Lindsey

Link to comment
Share on other sites

Hmmm, I went to the site, but can't see what you're talking about... I don't see any new product's box anywhere, so I'm assuming that maybe the product listing I'm seeing is that, but they appear to completely randomize everytime I load the page, rather than just the first 2 like you'd said... You should be able to stick this into subcategories as well though, using 1 of 2 methods... One is that you can just make a copy of that module file, and remove the checks for price (or just set price = 0), since you don't want subcategories to worry about price, and the other is just to do a check against $category_depth, and if it's at the top level, declare the query with price, otherwise, declare it without price, or set price to 0 in those circumstances...

 

Richard.

 

Ok if you got time check this out

 

Baseball » Singles

http://premierwebbing.com/shop/index.php?cPath=21_42

Baseball » Singles » Modern (1980 - Present)

http://premierwebbing.com/shop/index.php?cPath=21_42_53

 

Bot of those are the same and all the rest after that.

Link to comment
Share on other sites

Ok if you got time check this out

 

Baseball » Singles

http://premierwebbing.com/shop/index.php?cPath=21_42

Baseball » Singles » Modern (1980 - Present)

http://premierwebbing.com/shop/index.php?cPath=21_42_53

 

Bot of those are the same and all the rest after that.

Ok I see what you mean, yes, that can be fixed to randomly display as well, although right now I'm in the middle of a training at work and don't have time to think it out :D I'll check back here later today though when I'm through with that...

 

Richard.

Richard Lindsey

Link to comment
Share on other sites

  • 2 weeks later...
Lol thanks but lost on that one.

Well... here's an example query from up above:

 

$new_products_query = tep_db_query("select * from (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' and p.products_price > '" . $price . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") order by rand());

 

You want to add in a check for the products_image value like so:

 

$new_products_query = tep_db_query("select * from (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' and p.products_price > '" . $price . "' and p.products_image != '' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") order by rand());

 

Richard.

Richard Lindsey

Link to comment
Share on other sites

Just like that? because it's not working

Do you have anything in place to take the place of images that aren't there? Like some people make a default.jpg that's just an image that says "Picture Not Available" or something like that... If you've never uploaded an image for a product, it's products_image field should be '', so that query should exclude it... But if your images are defaulted to something, you'll need to exclude that default image name instead, like "and p.products_image != 'default.jpg'" or something... Also, the example query I showed you, did you put that products_image exclusion into *both* queries? Remember, you had the problem before where you made a change in 1 query and not the other, so make sure you've got that exclusion in both... If that is the case, and you don't have a default unavailable image, then take a look at the source code for the page when it renders with a product with no image, and see what it's trying to put for the image... Copy and paste the <img tag into your next post if that's the case...

 

Richard.

Richard Lindsey

Link to comment
Share on other sites

  • 2 weeks later...

Hello,

How would I modify this so I could randomly display all products added after a specific date?

Thanks

 

Josh

 

 

Sorry for the late reply, long day at work and a late dinner :) In order to randomly display the most recent X number of items, change my last posted queries to this (note, depending on your mysql version, this may not work... If it doesn't, I can show you a workaround, but you'll waste a query in the process):

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select * from (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 . ") as newtbl order by rand()");
} else {
$new_products_query = tep_db_query("select * from (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 . ") order by rand());

As for displaying products over a certain $ amount, you'd want to do something like this (this will do the same as the previous queries, grab the most recent X products over a dollar amount, and randomly sort them for display):

$price = 50;
if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {
$new_products_query = tep_db_query("select * from (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' and p.products_price > '" . $price . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") as newtbl order by rand()");
} else {
$new_products_query = tep_db_query("select * from (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' and p.products_price > '" . $price . "' order by p.products_date_added desc limit " . MAX_DISPLAY_NEW_PRODUCTS . ") order by rand());

Richard.

Link to comment
Share on other sites

Hello,

How would I modify this so I could randomly display all products added after a specific date?

Thanks

 

Josh

Look back to the very beginning of this thread, because that's the first way I rewrote these queries, then found out he wanted it done differently, so moved on from there, but the original solution is back at the beginning of the thread...

 

Richard.

Richard Lindsey

Link to comment
Share on other sites

Hello Richard,

Could you tellme how to specify the last 100 items, or if its easier, all items added after Jan 01, 2007?

 

With Gratitude,

 

Josh

Look back to the very beginning of this thread, because that's the first way I rewrote these queries, then found out he wanted it done differently, so moved on from there, but the original solution is back at the beginning of the thread...

 

Richard.

Link to comment
Share on other sites

Hello Richard,

Could you tellme how to specify the last 100 items, or if its easier, all items added after Jan 01, 2007?

 

With Gratitude,

 

Josh

For the last 100 items, you would just need to edit your admin console settings for new products, and tell it to list the last 100 versus the last 3 or 6 or whatever... That will grab just the most recent 100 additions all the time... If you wanted all items added after 1/1/2007, you would edit the query to say something like "and products_date_added >= '2007-01-01 00:00:00'"...

 

Richard.

Richard Lindsey

Link to comment
Share on other sites

hi, can you help me please?

this is the query i have in new_products.php

 

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {

$new_products_query = tep_db_query("select * from (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 . ")as newtbl order by rand()");

} else {

$new_products_query = tep_db_query("select * from (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 . ") order by rand()");

}

 

but when i enter the subcategories is gives the following error:

 

1248 - Every derived table must have its own alias

 

select * from (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 products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '31' and p.products_status = '1' order by p.products_date_added desc limit 8) order by rand()

 

My objective is to display the last 20 or X products randomized.

Link to comment
Share on other sites

  • 4 weeks later...

For those of you who are following this thread, here is my particular solution.

 

my server is using mysql version 4.0 something, so it does not support the nested selects which are a nice solution, but won't work for me.

 

similar to what someone else in this thread wanted to do, i wanted to select the 20 most recently added products, then display a random subset of 9 of them in a random order. i worked this out, based on a sketch by velveeta about the how to approach this, so i don't know if it's the best way, but it works. every time you refresh the page, the new-products box shows a different set of products. see it in action at My Site

 

this solution does require an additional query. my exact query may be slightly different from yours, so the relevant bits are in red.

 

$recent_prods_query = tep_db_query("SELECT products_id FROM products ORDER BY products_date_added DESC LIMIT 20");

$id_list = '';

$row=0;

while ($recent_prods = tep_db_fetch_array($recent_prods_query)) {

if ($row < 19) {

$id_list .= $recent_prods['products_id'] . ',';

$row++;

}

else {

$id_list .= $recent_prods['products_id'];

break;}

} // This gives me a comma-separated list of the product_id's for the 20 most recent products, then the query below selects from those and gives 9 random products to display.

 

$new_products_query = tep_db_query("SELECT DISTINCT p.products_id, p.products_image, p.products_tax_class_id, p.products_quantity, pd.products_name, pd.products_id,

IF( s.status, s.specials_new_products_price, p.products_price ) AS products_price

FROM products p, products_description pd

LEFT JOIN specials s ON p.products_id = s.products_id, products_to_categories p2c, categories c

WHERE p.products_id IN (" . $id_list . ")

AND p.products_id = p2c.products_id

AND p.products_id = pd.products_id

AND p2c.categories_id = c.categories_id

AND p.products_status = '1'

ORDER BY RAND()

LIMIT " . MAX_DISPLAY_NEW_PRODUCTS );

Link to comment
Share on other sites

  • 1 year later...
hi, can you help me please?

this is the query i have in new_products.php

 

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) {

$new_products_query = tep_db_query("select * from (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 . ")as newtbl order by rand()");

} else {

$new_products_query = tep_db_query("select * from (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 . ") order by rand()");

}

 

but when i enter the subcategories is gives the following error:

 

1248 - Every derived table must have its own alias

 

select * from (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 products p left join specials s on p.products_id = s.products_id, products_to_categories p2c, categories c where p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '31' and p.products_status = '1' order by p.products_date_added desc limit 8) order by rand()

 

My objective is to display the last 20 or X products randomized.

 

 

I had the same thing. I wanted the randomized first 20 products only for my homepage and left the second statement (after "else", in between the brackets ) as in the original file.

 

This will leave the new-products statement for the categories and subcategories unaffected...

 

Hope this help for you too (my first post) :-)

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...