OK, Why doesn't this work?

[pmakulski]

My install is SQL 4.1

 

I'm trying to get "New products" to be a random sample of products instead.

I found in the MySQL help a syntax that says:

ORDER BY RAND() LIMIT n

 

So, in products_new.php I tried changing the line:

$products_new_query_raw = ... "' order by p.products_date_added DESC, pd.products_name";

 

to

$products_new_query_raw = ... "' order by RAND() LIMIT " . MAX_DISPLAY_PRODUCTS_NEW;

 

I expected the products shown to be differennt each time, but they're not.

 

Should this work?

[♥geoffreywalton]

Nice thought.

 

Rand() will generate a random number

 

rand(5,500) will generate a random number between 5 and 500

 

Select syntax is

 

SELECT column_name(s)

FROM table_name

ORDER BY column_name

 

Can't order by a random number, has to be a field name.

 

order by pd.products_name";

 

This is what I thnk you want to do but it is not valid

 

order by pd.products_name limit(rand(1," . MAX_DISPLAY_PRODUCTS_NEW.",1)";

 

as limit can't have a variable in it.

 

Check out

 

http://dev.mysql.com/doc/refman/5.0/en/select.html

 

for more info.

 

This is how the manul shows this being achieved

 

SET @skip=1; SET @numrows=5;

PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?, ?';

EXECUTE STMT USING @skip, @numrows;

 

This returns row 2 to 6

[pmakulski]

Check out

 

http://dev.mysql.com/doc/refman/5.0/en/select.html

 

Down in the comments of the link you sent (which is a very nice site I might add), there are several comments basically describing the syntax that I'm trying to use for the purpose I'm trying to use it for.

 

e.g.

Also, as pointed earlier in the thread, keep in mind that if your table isn't that large, just performing the following works very well (e.g. selecting 5 random rows on a moderately large table):

SELECT * FROM foo ORDER BY RAND() LIMIT 5;

 

Be that as it may, it still doesn't work. :(

[germ]

Click me

 

If you're in a hurry, skip to the last post in the thread:

 

I've been having the same problem with ORDER BY RAND. Seeding at, as the MySQL.com doc page suggests did not seem to help at all.

 

One solution (though, I will warn you up front, it is a hack) that has been working great for me is to use the MySQL REVERSE command on the result of the RAND(). This works because the least significant digits RAND() returns do change every time, while the more significant digits do not. So, your query would look something like:

 

Code:SELECT blah, REVERSE(RAND(NOW())) AS rnd FROM table ORDER BY rnd

Works good for me, and is significantly faster than the MD5 suggestion mentioned elsewhere

Maybe it will work for you.

[pmakulski]

Ah! I got it to work.

Turns out I was changing the wrong module.

I was changing products_new.php when I should have been changing includes\modules\new_products.php

Doh!

 

Change this

  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);
 }

to this:

  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 rand() 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 rand() limit " . MAX_DISPLAY_NEW_PRODUCTS);
 }

 

Apparently this ORDER BY RAND() can be rather inefficient, especially as tables get large. I'll be watching for performance issues.