Random return from MySql

[MDowner]

OK..

I want to return the top 10
random entries from a query.. the psuedo would be...

SELECT RANDOM EVENTS FROM events WHERE date is after today AND date is upto 1 month from now LIMIT 0,10

/>The idea is so the script displays records from the database from now until 1 month from now, randomly so no single refresh will get the same result (doesn't matter if it
does just as random as can be)

Thanks
Martin

[JasonD]

SELECT stuff FROM events WHERE date >= NOW() AND date
< NOW() + INTERVAL 1 MONTH ORDER BY RAND() LIMIT 10

[MDowner]

For some reason your way wouldn't return
a value.. but the following did.

Code:

$year = substr($date, 0,4);
$month = substr($date, 5, 2);
$day = substr($date, 8, 2);
/>
$time_next_month = mktime(0, 0, 0, $month+1, $day, $year);

$sql = "SELECT * FROM events WHERE scID=1 AND event_date >= $timenow AND
event_date < $timenow + $time_next_month ORDER BY RAND() LIMIT 0,$amount";

Thanks for pointing me in the right direction though!!

/>Martin

[JasonD]

No, your way doesn't work either.

   $year = substr($date, 0,4);
   $month = substr($date, 5, 2);
   $day = substr($date, 8, 2);

   $time_next_month = mktime(0, 0,
0, $month+1, $day, $year);

I think you mean

Code:

$time_next_month = strtotime('next month');

But that is not what you
want anyway.

If $timenow is a mysql date value (YYYY-MM-DD) then $timenow + $time_next_month is the same as $time_next_month + 2007, and a date field is always going
to be less than that.

Code:

mysql> SELECT '9999-12-31' < '2007-06-07' + 1183831815;
+------------------------------------------+
|
'9999-12-31' < '2007-06-07' + 1183831815 |
+------------------------------------------+
|               
                        1 |
+------------------------------------------+

If $timenow is
unixtime then $timenow + $time_next_month is sometime in the year 2044.

[MDowner]

Well then, i don't know why but it's
working for me......

I'm using a timestamp generated by time() not a mysql date.

I don't really know what else to say....

[JasonD]

Add enough future events so that randomly selecting 10
doesn't just happen to pick those from this month.

And when you agree that I'm not talking rubbish alter your query to

$sql = "SELECT * FROM
events WHERE scID=1 AND event_date >= $timenow AND event_date < $time_next_month ORDER BY RAND() LIMIT 0,$amount";

[MDowner]

I didn't think you were talking rubbish
at all, i hope you don't really think I was implying that.

You were correct it wasn't pulling the correct date..

Code:

echo date("d-m-
Y",$time_next_month);

this actually returned 07-08-2007 though, which is actually plus 2 months... so somewhere i'm not 100% whats going on..

[JasonD]

PHP < 4.4.0 has a buggy strtotime(). Use '1
month' instead of 'next month'.