Take the 2-minute tour ×

I use a query like :

$querym = mysql_query("SELECT * FROM allmembers a LEFT JOIN favorites f ON (f.memberid=a.memberid) order by f.date desc LIMIT 10");

while($row = mysql_fetch_array($querym,MYSQL_ASSOC)) {

My purpose is getting 10 members with their last 5 favorites in an array. but as you can guess this query getting 10 row included with favorites. That means if a member have 15 favorites it only gets one member with 10 favorites instead of 10 members with his favorites.

I couldn't find an easy way to limit getting favorites for each member in that query. How can I limit?

Thanks in advance

share|improve this question

4 Answers 4

up vote 1 down vote accepted
  1. This is not MYSQL FETCH ARRAY LIMITATION, this is your QUERY limitation.

  2. Try something of your own on these lines :

    SELECT memberid,group_concat(favorites_field) FROM allmembers a LEFT JOIN favorites f ON (f.memberid=a.memberid) group by a.memberid order by f.date desc LIMIT 10
share|improve this answer
1- sorry for the title. I updated it. –  Matt Dagger Oct 17 '11 at 11:57

This query may give you a better approach

SELECT * FROM favorites f where f.memberid in (SELECT * FROM allmembers limit 10) ORDER BY f.date desc 

although you should limit the 5 favorites later, in php or whatever. This could work if you don't expect to have 10.000 favorites for one member (basically because you will be getting 10.000+ ROWS just to display, at maximum, 10*5 = 50 rows.)

Alternatively, you can do a query for each member, limiting it to 5 results... (this would mean doing 11 queries in total...)

share|improve this answer

The following query will do what you want:

    a.id as member_id, 
    f1.id as favorite_id, 
    allmembers a
    JOIN favorites f1 ON f1.member = a.id
    LEFT JOIN favorites f2 ON f2.member = f1.member AND f2.date < f1.date
    a.id, a.name, f1.id, f1.link
    COUNT(f2.id) < 10
    a.name, f1.id

It assumes the following database schema:

    id INT
    name VARCHAR

    id INT
    member INT
    link VARCHAR

Obviously you need to update the query according to your own database schema.

share|improve this answer

You have many ways to do that, and they can all be performant up to a limit, what i'd do in this specific case is this:

Get the favorite id's in a multiple left join as a seperate field, you can then easily for(...) the row array for each field and retrieve the id. The Query would LOOK LIKE this. Adapt it to your own use:

    f1.favoriteid as f1id,
    f2.favoriteid as f2id,
    f3.favoriteid as f3id,
    f4.favoriteid as f4id,
    f5.favoriteid as f5id
FROM allmembers a 
    LEFT JOIN favorites f1 ON (f.memberid=a.memberid) 
    LEFT JOIN favorites f2 ON (f.memberid=a.memberid) AND f1.favoriteid <> f2.favoriteid
    LEFT JOIN favorites f3 ON (f.memberid=a.memberid) AND f1.favoriteid <> f3.favoriteid AND f2.favoriteid <> f3.favoriteid
    LEFT JOIN favorites f4 ON (f.memberid=a.memberid) AND f1.favoriteid <> f4.favoriteid AND f2.favoriteid <> f4.favoriteid AND f3.favoriteid <> f4.favoriteid
    LEFT JOIN favorites f5 ON (f.memberid=a.memberid) AND f1.favoriteid <> f5.favoriteid AND f2.favoriteid <> f5.favoriteid AND f3.favoriteid <> f5.favoriteid AND f4.favoriteid <> f5.favoriteid

order by f.date desc LIMIT 10

Using this method you can also read any information from the favorites table or even left join on another table X number of times to get more information regarding that favorite. As long as you setup correct indexes, this method is extremely fast even with thousands of members and nearly millions of favorites.

You can also apply this strategy to many other scenarios. For example, we work with WordPress here at work and lots of information for users are kept as meta fields, so selecting one big table is impossible unless you perform this method.

Good luck

share|improve this answer

Your Answer


