MySQL Query & PHP for a list of data, best way to go about it?

cssExp · February 23, 2012, 12:41pm

I’m somewhat confused how to go about getting the MySQL right for something I want to do. Also I’m not sure what’s the best way to do it.

WHAT I’M TRYING TO DO

I have a database with users who have various ranks, I want to take… say 50 newest users and display them separated by their rank, not only that I also want to sort of split it into two columns (not halfway, after a rank list is done). But I also want to put a limit of max 15 users per rank.

I could probably figure out the php part, but will love suggestions.

TABLE users


    `order` (int), `user` (varchar), `rank` (varchar), `url` (text), `joindate` (timestanp)

DESIRED/SAMPLE MARKUP

&lt;div id="container"&gt;
        &lt;div class="left"&gt;
            &lt;h2&gt;NOVICE&lt;/h2&gt;
    
            &lt;ul class="userlist"&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 1&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 2&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 3&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 4&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 5&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 6&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 7&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 8&lt;/a&gt;&lt;/li&gt;
            &lt;/ul&gt;
    
            &lt;h2&gt;INTERMEDIATE&lt;/h2&gt;
    
            &lt;ul class="userlist"&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 9&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 10&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 11&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 12&lt;/a&gt;&lt;/li&gt;
            &lt;/ul&gt;
        &lt;/div&gt;
    
        &lt;div class="right"&gt;
            &lt;h2&gt;SKILLED&lt;/h2&gt;
    
            &lt;ul class="userlist"&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 13&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 14&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 15&lt;/a&gt;&lt;/li&gt;
            &lt;/ul&gt;
    
            &lt;h2&gt;EXPERT&lt;/h2&gt;
    
            &lt;ul class="userlist"&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 16&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 17&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 18&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 19&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 20&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 21&lt;/a&gt;&lt;/li&gt;
                &lt;li&gt;&lt;a href="THE_URL"&gt;USERNAME 22&lt;/a&gt;&lt;/li&gt;
            &lt;/ul&gt;
        &lt;/div&gt;
    &lt;/div&gt;

MAIN QUESTION

What is the best way to take top 50 new entries, group them into the rank (should only take max 15 users with the same rank), and return the SQL result? Can be done with single MySQL query? or no way to do it other than using multiple queries?

r937 · February 23, 2012, 6:02pm

how many different ranks are there?

i’m thinking separate queries for each, with LIMIT 15

cssExp · February 23, 2012, 6:55pm

There will be “custom ranks” sort of, even though there is 4 now, it can increase.

There is absolutely no way to other than separate queries? I’m trying to keep number of queries as small as possible. I guess I’ll have to work with what’s possible.

r937 · February 23, 2012, 9:13pm

if you can re-think the “top 50 with 15 each” idea, i can re-think separate queries

cssExp · February 24, 2012, 5:55pm

If SQL Consultant such as yourself suggest me to go for multiple queries, then thats exactly what I’ll do. Thanks!

Mittineague · September 24, 2014, 2:11am