Populating query results with empty rows

Question

I have the following query which I use to return the highest scores in a game but I would like it to be populated by empty rows if there is less than 10 records:

SELECT id, MAX(score) mscore FROM scores WHERE id IN (".implode(',', $data).") GROUP BY id ORDER BY mscore DESC"

The logic is that an array of user IDs will be passed to the query and then the highest scoring IDs will be sorted and a top ten will be formed, a lot of IDs do not have a score yet and therefore some top ten query return only 3 or 4 rows. How can I fill out the query results until i have 10? The ID references photos and so on.

Answer 1

In MySQL, you can select the top 10 rows using limit.

SELECT  id, MAX(score) mscore
FROM    scores
WHERE   id IN (".implode(',', $data).") 
GROUP BY
        id
ORDER BY
        mscore DESC
LIMIT   10

Answer 2

Don't do it in mysql. When you use php to get the resultset you simply add empty results as needed. (means empty array fields)

Answer 3

while($row = mysql_fetch_array($result))
{
    $data[] = $row;
}

Then just keep adding empty rows until theres exactly 10 results in the set.

while(count($data) <= 10)
{
    $data[] = array();
}

another alternative is to use a for loop and an if statement when your outputting, instead of a foreach!

for($i=0; $i<=10; $i++)
{
    if(isset($data[$i]))
    {
        //Show data from $data[$i]
    }else
    {
        show block row.
    }
}

Answer 4

Like @Thariaman said (+1) don't do that in MySql as it's much more hassle and harder to support in the future.

I'd try something like this (filling the array with empty keys so you script doesn't throw Warnings if you try to access them (or something))

function getMaxScores($gameid) {

    $result = query("SELECT id, MAX(score) mscore FROM scores ".
        "WHERE id IN (".implode(',', $data).")".
        "GROUP BY id ORDER BY mscore DESC LIMIT 10"
    );
    if(count($result) < 10) {
        $result = array_pad($result, 10, array("id" => "", "mscore" => "");
    }
    return $result;
}

Answer 5

Though I agree that you should handle this in your script and not within the query itself, one thing that came to mind was:

SELECT * FROM (
  (
    SELECT id, MAX(score) mscore FROM scores
    WHERE id IN (1,4) GROUP BY id ORDER BY mscore DESC
  )
  UNION ALL(SELECT '--', '')UNION ALL(SELECT '--', '')UNION ALL(SELECT '--', '')UNION ALL(SELECT '--', '')
  UNION ALL(SELECT '--', '')UNION ALL(SELECT '--', '')UNION ALL(SELECT '--', '')UNION ALL(SELECT '--', '')
  UNION ALL(SELECT '--', '')UNION ALL(SELECT '--', '')
) as t LIMIT 10

But if I'd seen code like that in a review the developer would get a "o rly?" note ;-)

Answer 6

I'd create a default(s) table with ten rows of base default data. Then select the records you want with a union of the ten default rows and limit the output by 10 if you're into hard coding or by a count on the default table if you're are not.