random from the last 30 days?

[JohnnyDoomo]

Can somebody help me with this, I'm not a programmer and am simply looking to get items pulled that are random, but I want them only from the last so many days.

 

I currently have this:

 

$res = ppmysql_query("SELECT * FROM {$Globals['pp_db_prefix']}photos WHERE approved=1 AND cat IN (501,502,511,503,562,504,506,505,508,2,510,509) ORDER BY rand() LIMIT 30", $link);

 

which pulls from my proper categories, and gives me random results, but they are random and some of the results are from years ago. I want the same thing, but I want results that are within the last 10 or 30 so days.

 

Can somebody help me with this?

 

After searching, it looks like people don't recommend the rand() as "it doesn't scale well", but it's what I have at the moment, and I'm stuck with it until I can afford a programmer to go through my entire code.

 

It just need it so that the way it's currently being done, is left intact, even tho there maybe better ways of doing it, but I need results to only be within a few days timeframe.

 

Thanks for any help you provide me, I'm thinking it's something super simple added to that line.

[Mahngiel]

add: 'where `date` > yyyy:mm:dd and `date` <yyyy:mm:dd '  to your query or so depending on how you're storing your timestamps

[Barand]

This method, with the select subquery, ran in a third of the time on my test

 

SELECT * FROM
(
    SELECT * FROM {$Globals['pp_db_prefix']}photos 
    WHERE approved=1 
        AND cat IN (501,502,511,503,562,504,506,505,508,2,510,509) 
        AND date BETWEEN (CURDATE() - INTERVAL 30 DAY) AND (CURDATE() - INTERVAL 10 DAY)
    ) as x
ORDER BY rand() 
LIMIT 30;

[Barand]

Forget my previous post. I ran a few more benchmarks and it's either same or slower. Thought it too good to be true.

[Mahngiel]

 

        AND date BETWEEN (CURDATE() - INTERVAL 30 DAY) AND (CURDATE() - INTERVAL 10 DAY)

 

I've seen you post that methodology several times before, and I'm just curious: Is there a timestamp format that must be abided by in order for that to work? a la yyyy-mm-dd hh:mm:ss ?

[Barand]

As with any mysql datetime calculation you have to use columns of type DATE, DATETIME or TIMESTAMP.

 

I use that method because I find it more readable and intuitive and easier than remembering the order of the args for DATE_ADD() and DATE_SUB().