Date based random products

zeberrun

list($usec, $sec) = explode(' ', microtime());
srand( (float) $sec + ((float) $usec * 100000) );
$mtm= rand();

if ( (!isset($new_products_category_id)) || ($new_products_category_id == '0') ) { // in index.php

$new_products_query = db_query("select p.products_id, p.fr_currencies_id, p.products_list_price, p.products_model, 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, rand() DESC limit " . MAX_DISPLAY_NEW_PRODUCTS);

Hello, this is my first title.
i have a box, which shows 10 new products, but i add mostly 40 products.

i want to select 10 random products from last added 40 products.
* "by p.products_date_added, rand()" gets all the products from the table regardless the last added.
what i must do?

Desdinova

Try something like this

WHERE ID BETWEEN MAX(ID) AND (MAX(ID)-40) ORDER BY Rand()

I don't know if it'll give you an error, you'll have to see for yourself.

eidt
oh and do use your own fieldames 😉

zeberrun

thanks for reply, these are real field names.
but this is not my exact goal, i want to select randomly last added products from products table. if 40 products added yeterdays date, then the selection must contain 10 products of yesterdays products.

regards

Desdinova

ok, makes it easier if the date is specified. could you tell us how a date is stored? ie. in unixstamp?

You should be able to find what you need here:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

you probably need to perform some math, something like

WHERE `Date`= UTC_DATE() - 1

so you'll get today, minus one. it's something in this direction. The order by rand() will choose random products, limit your query to get only 10 products

WHERE `Date` =  (check the manual) ORDER BY Rand() limit 0,10