Selecting Rows Containing Each Unique Field Value & Sorting By Another Field With A Limit

Question

I have the following fields/structure in my table:

(`map`, `authid`, `name`, `time`, `date`, `weapon`)

Every time a player completes a map in a game, this information is stored in a sql row in this table so long as for every map the time the player took to complete it is lower than any previously stored time. This means that there will only ever be one row per map for any given player (authid/name).

Now, on to my question. Being the SQL noob I am, I wish to figure out a query to run that will select the lowest 15 times per map (obviously if there are less than 15 entries per map all of them will be returned). I know I can create a query like the following in PHP:

SELECT * FROM tablename WHERE map='". $map ."' ORDER BY time LIMIT 15;

But I need to end up with a multi-dimensional array (PHP) or object (JS) containing every unique map value to work with later in my code (and it needs to be sorted by time), ie.:

{"map1":[
    {"authid1":"val",
     "name1":"dude1",
     "time":"23.46",
     "date":"val",
     ...},
     {"authid1":"val",
     "name1":"dude2",
     "time":"42.33",
     "date":"val",
     ...},
     ...],
 "map2":[
    ...
    ...]
}

If I can't do this purely via SQL queries, then how could I utilize PHP or JS to finish my objective?

Answer 1

$q1 = "SELECT map from tablename GROUP BY map ORDER BY map";
$r1 = $db->mysqli_query($q1);
while ($mapRow = mysqli_fetch_assoc($r1)) {
    $map = $mapRow['map'];
    $q2 = "SELECT * FROM tablename WHERE map='". $map ."' ORDER BY time LIMIT 15";
    $r2 = $db->mysqli_query($q2);
    while ($dataRow = mysqli_fetch_assoc($r2)) {
        $map[] = $dataRow;
    }
}