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

Question

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>";

Do you have a specific "midpoint" point value that you want results around? Is that the value of $submission? — , Dec 09 '11 at 00:58
@ rdlowery the midpoint is where $submission equals the title. They're both text. — John, Dec 09 '11 at 01:03
+1 There is some subtlety here if several `submission` records share the same value for `points`. — pilcrow, Dec 09 '11 at 15:08
Possible dublicate: http://stackoverflow.com/questions/7184965/how-can-i-get-the-nearest-rows-in-mysql (no accepted answer there, but better search title). — XzKto, Dec 20 '11 at 09:49
consider using DataTables + Twitter Bootstrap due to implement great tables with pagination. Check this out http://datatables.net/blog/Twitter_Bootstrap — Pabluez, Dec 25 '11 at 13:21

score 3 · Answer 1 · answered Dec 10 '11 at 05:06

This is a little tricky if several rows share the same value for "points":

points | title | submissionid
------ + ----- + ------------
    ...
   50  | 'foo' | ABCD01234     <-- If (50, 'foo') is the "midpoint" record,
   50  | 'bar' | EF7654321     <-- does (50, 'bar') come before or after?
    ...

In this case, we need to impose an order. For convenience we're going to order by "points" descending and then "title" descending.

Assuming your "midpoint record" has a points value of '@points' and a title '@title', we'll say the records that come "before" the midpoint are those records whose (points, title) > (@points, @title). Similarly, those records "after" the midpoint have their (points, title) < (@points, @title).

Putting it together, we have:

-- First, initialize user variables with the points and
-- title of our midpoint (including midpoint)
--
SELECT @title := title,
       @points := points
  FROM submission
 WHERE title = ?          -- bind your $submission variable bere
 LIMIT 1;

-- Now, select six records greater than or equal to our
-- midpoint.
--
  SELECT title, points, submissionid
        FROM (  SELECT title, points, submissionid
                  FROM submission
                 WHERE (points, title) >= (@points, @title)
              ORDER BY points ASC, title ASC
                 LIMIT 6) gte
-- and UNION those records with five records less than
-- our midpoint
--
   UNION
  SELECT title, points, submissionid
        FROM (  SELECT title, points, submissionid
                  FROM submission
                 WHERE (points, title) < (@points, @title)
              ORDER BY points DESC, title DESC
                 LIMIT 5) lt
-- Finally sort the result set
--
ORDER BY points DESC, title DESC

Could you be a little more specific? Thanks. – John Dec 19 '11 at 22:34 — John, Dec 19 '11 at 22:34
@John, a little more specific about which part? – pilcrow Dec 19 '11 at 22:36 — pilcrow, Dec 19 '11 at 22:36
@ pilcrow How could I combine all this into one query? – John Dec 19 '11 at 22:39 — John, Dec 19 '11 at 22:39

score 2 · Answer 2 · answered Dec 09 '11 at 00:57

You need to use a UNION

(
    SELECT title, points, submissionid
    FROM submission
    WHERE points < (SELECT points FROM submission WHERE title = <row_title> LIMIT 1)
    ORDER BY points DESC LIMIT 5
)UNION(
    SELECT title, points, submissionid
    FROM submission
    WHERE points > (SELECT points FROM submission WHERE title = <row_title> LIMIT 1)
    ORDER BY points ASC LIMIT 5
) ORDER BY points DESC

I haven't tested it, but this is the gist of it. You will get 10 records (or less), you'll have to figure out in PHP which records should go above your $submission and which below, since if you get 9 records you wont know if 4 are higher, or 5.

Alternatively you could just do it with 2 queries of course >_>

score 2 · Answer 3 · answered Dec 20 '11 at 11:11

SELECT 
    title, points, submissionid
FROM
    submission 
WHERE 
    ROWID >= (SELECT ROWID FROM submission WHERE ..... ORDER BY points DESC) - 5
AND
    ROWID <= (SELECT ROWID FROM submission WHERE ..... ORDER BY points DESC) + 5
ORDER BY 
    points DESC

Brian Fisher · Answer 4 · 2011-12-26T06:14:50.117

I think its simplest to just do 2 queries against the database, but you could do it in PHP:

$entryFound = false;
$counter = 0;
$priorEntries = array();

while ($row = mysql_fetch_array($result)) { 
    $rowHtml = '<tr >';
    $rowHtml .=  '<td>'.$count++.'.</td>';
    $rowHtml .= '<td class="sitename1">'.$row["title"].'</td>';
    $rowHtml .= '<td class="sitename2"><div class="pointlink2">'.number_format($row["points"]).'</div></td>';
    $rowHtml .= '</tr>';

    if ($entryFound) {
            if ($counter < 5) {
                $counter++;
                echo $rowHtml;
            }
    } else {            
            array_unshift($priorEntries, $rowHtml); 
            if (strcmp($row["title"], $submission) == 0) {
                echo implode(array_reverse($priorEntries));
                $entryFound = true;
            }   
    array_splice($priorEntries, 5);
    }
}

I tried this and it didn't work. Am I supposed to leave the query the same? — John, Dec 19 '11 at 23:07
Yeah, that was the idea. What didn't work for you? I had forgotten a semicolon on the array_splice($priorEntries, 5); line, however, I've edited my answer now. — Brian Fisher, Dec 26 '11 at 06:15

score 1 · Answer 5 · answered Dec 23 '11 at 22:37

I think that the following might do what you are asking. It will run the query and start reading records from the result set up until it finds the first occurrence of a record with a title that equals the $submission variable that you have (if I understand it correctly the primary key of your table is the submissionid and the title is a simple field - i.e. you do not have a unique key on it, so there may be more than one records with the same title).

After it finds that first record it will read 5 more records and stop. Then it will provide you with the part of the array of records that you wish to print and finally it will print it.

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

$result = mysql_query($sqlStr);

$count = 1;

$found = false;

$continue = true;

$records = array();

$row = mysql_fetch_array($result);
while ($row !== false && $continue === true)
{ 
    if($found == false && $row['title'] == $submission)
    {
        $found = true;
    }
    elseif($found == true && $count < 6) 
    {
        $count++;
    }
    elseif($found == true && $count >= 6)
    {
        $continue = false;
    }

    $records[] = $row;
    $row = mysql_fetch_array($result);
}

if($found === true)
{
    if(array_count($records) > 11)
        $records = array_splice($records, -11);
}
else
{
    $records = array();
}

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

for($i = 1; $i <= count($records); $i++)
{
    echo '<tr >';

    echo '<td>'.$i.'.</td>';

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

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

    echo '</tr>';
}

echo "</table>";

score 1 · Answer 6 · answered Dec 24 '11 at 15:13

If there are no duplicate "title" values and if there are no duplicate "points"

SELECT title, points, submissionid
FROM submission 
WHERE points <=
      ( SELECT points
        FROM submission
        WHERE points >= 
              ( SELECT points
                FROM submission
                WHERE title = @row_title
              )
        ORDER BY points ASC
        LIMIT 1 OFFSET 5
      )
ORDER BY points DESC
LIMIT 11 OFFSET 0

score 0 · Answer 7 · answered Dec 26 '11 at 16:06

If the intention is to use single query and table is small and performance doesn't matters then use

SELECT b.title, b.points FROM (
  SELECT @rank1 := @rank1 + 1 as slno, temp1.* FROM (
    SELECT s1.title, s1.points, COUNT(s2.title) rank
    FROM submission s1
    JOIN submission s2 ON s1.points <= s2.points
    GROUP BY s1.title, s1.points
    ORDER BY rank, s1.title ASC
  ) as temp1 
  JOIN( SELECT @rank1 := 0 ) AS init
  ORDER BY slno 
) a
LEFT JOIN (
  SELECT @rank2 := @rank2 + 1 as slno, temp1.* FROM (
    SELECT s1.title, s1.points, COUNT(s2.title) rank
    FROM submission s1
    JOIN submission s2 ON s1.points <= s2.points
    GROUP BY s1.title, s1.points
    ORDER BY rank, s1.title ASC
  ) as temp1 
  JOIN( SELECT @rank2 := 0 ) AS init
  ORDER BY slno 
) b ON a.slno BETWEEN b.slno - 5 AND b.slno + 5
WHERE a.title = <row_title>;

This will return the row selected by and upto 5 above(if present) and upto 5 below(if present).

However, it is highly suggested to use temporary table to store ranks and the use it to display ranks.

More details about above query @ http://www.artfulsoftware.com/infotree/queries.php?&bw=1280#460

More details about optimized method @ http://onlamp.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html

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

7 Answers7

Linked