Displaying 11 consecutive rows from a query where middle row contains a variable

[ArizonaJohn]

On my web page, there is a variable called $submission. I would like to display exactly 11 rows from the query below: the row where $submission equals $row["title"], 5 rows above it, and 5 rows below it. All ranked by points descending.

 

How can I do this?

 

$sqlStr = "SELECT title, points, submissionid
             FROM submission 
         ORDER BY points DESC"; 


$result = mysql_query($sqlStr);

$arr = array();

$count=1;

echo "<table class=\"samplesrec\">";

while ($row = mysql_fetch_array($result)) { 


    echo '<tr >';

    echo '<td>'.$count++.'.</td>';

    echo '<td class="sitename1">'.$row["title"].'</td>';

    echo '<td class="sitename2"><div class="pointlink2">'.number_format($row["points"]).'</div></td>';
    echo '</tr>';

}

echo "</table>";

[Psycho]

Someone may have a better idea, but the only solution I can see is to run separate queries for the 5 above and the 5 below. But, you can do them both in a single query using the UNION clause. However, you would have to pre-process the results to get them in the proper order.

 

EDIT: If records can have the same points this probably wont work (you could have the same records show above and below the target). So, can records have the same points? If so, I'll look into a different solution. Otherwise I can provide some sample code.

[ArizonaJohn]

Yes, some of the records can have the came points. 

 

If the records have the same points, I would want to sort them by title descending. 

[Psycho]

OK, I have no idea how well this will scale, but it should work. You could run two queries. The first one will get the position of the record based upon your sorting criteria. Then the second query would run the query to get the data for the records using an appropriate LIMIT using that position.

 

NOTE: You will want to add some logic to creating the LIMIT of the second query in case the record you are using as the target is at the beginning of the order.

 

$range = 10;

//Run query to get the position of the target record in sorted results
$query = "SELECT position
          FROM (SELECT @rownum := @rownum + 1 AS position
                FROM submission
                JOIN (SELECT @rownum := -1) r
                ORDER BY points DESC, title, DESC) AS t
          WHERE title = '{$submission}'"; 
$result = mysql_query($query);
$position = mysql_result($result);

//Define limit parameters
$limit_start = max(0, $position-$range);
$limit_total = ($rang*2)+1;

//Run query to get the records based on target position
$query = "SELECT title, points
          FROM submission
          ORDER BY points DESC, title, DESC
          LIMIT $limit_start, $limit_total"; 
$result = mysql_query($sqlStr);

//output the results
$count = 0;
echo "<table>\n";
while ($row = mysql_fetch_array($result))
{
    $count++;
    $formattedPoints = number_format($row['points']);
    echo "<tr>\n";
    echo "  <td>{$count}.</td>\n";
    echo "  <td class='sitename1'>{$row['title']}</td>\n";
    echo "  <td class='sitename2'><div class='pointlink2'>{$formattedPoints}</div></td>\n";
    echo "</tr>\n";
}
echo "</table>";

 

I tested the queries against different databases, so I know this will work. But, I did not test against your specific database so there may be typos or syntax errors.