Displaying results with a maximum of 3 from each user?

deshg

Hey everyone,

I have a page on my website which displays all photo galleries submitted by all users with a maximum of 12 on each page ordered by date (with page numbers and prev/next links to go to the other pages). Some users have a ridiculously large number of galleries, so i only ever want to display a maximum of 3 galleries per user in the results list and (if the user does have more than 3) a little banner saying click here to view their remaining galleries.

I was wondering if there is anyway of getting the final gallery list in one mysql query? I currently have

$query = "SELECT $table.* FROM $table WHERE FZipExclusive='0' ORDER BY FDateUpdated DESC";

Which obviously returns all galleries ordered by date. I could do:

$query = "SELECT * FROM $table WHERE FZipExclusive='0' GROUP BY FUsername ORDER BY FDateUpdated DESC";

Which displays one gallery for each user and orders it by date.

Is there anyway of doing a similar thing to what's above, however displaying the first 3 galleries for each user (ordered by date ideally) .

I appreciate i could do it quite easily by using mulitple queries, or processing the results in php, but i would much rather do it in one mysql query if possible?

Thanks so much for your help,

Dave

seco

LIMIT?

 $query = "SELECT * FROM $table WHERE FZipExclusive='0' GROUP BY FUsername ORDER BY FDateUpdated DESC LIMIT 3";

and a WHILE

deshg

Unfortunately not as this will just limit the whole result set to 3. What i need to do is limit the number of galleries associated to any one user within the result set to 3.

So if i had a list of galleries:

Gallery1 owned by UserA
Gallery2 owned by UserA
Gallery3 owned by UserA
Gallery4 owned by UserA
Gallery5 owned by UserA
Gallery6 owned by UserB
Gallery7 owned by UserC

Then my final resultset should end up as:

Gallery1 owned by UserA
Gallery2 owned by UserA
Gallery3 owned by UserA
Gallery6 owned by UserB
Gallery7 owned by UserC

As it shouldn't display any more than 3 galleries from any one user.

Sorry if i didn't explain it properly, does anyone have any other ideas?

Thanks,

Dave

johanafm

As far as I know, you'd need to

SELECT stuff
FROM tables
WHERE gallery_id IN (SELECT gallery_id ... LIMIT 0,3)

Unfortunately, limit clause is not supported in nested queries in MySQL, at least not in versions 5.x. Possibly it's been added in 6. Chceck their documentation to find out (or just plain try it), if you're using 6. Else, stick with two queries.

deshg

Johanafm: Thanks so much for your help, unfortunately as you suspected LIMIT does not work IN with the mysql version i'm using.

My question then is how do you do this in just 2 queries as you suggest? The only feasible way i can see to do it is pull all of the results and then sort them (remove the undesired ones) once the data is in php.

Am i missing a more sensible way of doing this?

Thanks so much for your help,

Dave

Roger_Ramjet

What you need to do is to assign an order or sequence number to each post by each user which you then use to filter the results. See my posts in this thread for the full method of doing this on the fly.