Php mysql Order by Rand() last 30 days

[mikster]

This is the code i have now

$ransql = bb_db_query("select * from album where active='Y' and find_in_set('117',categories) ORDER BY album_id DESC LIMIT 5");

I was wondering if its possible to do a ORDER BY RAND() limiting it to the last 30 days.

[djr33]

Change your current ORDER BY to RAND(), and add to the WHERE conditions that the time must be greater than 30 days ago. If this is a unix time stamp, then that will be something like: WHERE `time`>($currenttime-(30*24*60*60)), but if your time is stored in another format you'll need to convert it.

[mikster]

I want to keep the random too how would I do that?? Should I be doing Less then if I want it to be no further then 30 days ago??

[djr33]

The WHERE section limits the results to certain entries. The ORDER BY section decides which, of those, is first and next, through the last one. I don't understand the problem. You can use both.

[mikster]

I put in your line of code and got an error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE `time`>(-(30*24*60*60)) DESC LIMIT 5' at line 1

select * from album where active='Y' and find_in_set('117',categories) ORDER BY album_id WHERE `time`>(-(30*24*60*60)) DESC LIMIT 5


Can you tell me what i did wrong? thanks in advance.

[djr33]

There are two problems:

1. The order of statements in MySQL is important:
select * from album where active='Y' and find_in_set('117',categories) and `time`>(-(30*24*60*60)) ORDER BY RAND();

2. What I gave you was an example of the time. You will need to apply this for your system. How do you store the time? Do you store the time? If not, you will need to add a column for time and then use that to compare. And you will also need to get the real time from PHP and add that. For example:
"...... `time`>(".time()."-(30*24*60*60)) .......";


I recommend an introductory MySQL tutorial to learn the basics. Or look at some examples of working code to understand how the queries work. There are also sites that have full documentation for MySQL syntax, but that can be confusing if it's new. Overall, I think MySQL is counterintuitive and confusing when you first start, but you can't do it without understanding it, so be patient and take it one step at a time. MySQL is not "difficult" really... it's just different.