CODEWITHSUNDEEP
phpmysql
Ned
Ned

Reputation: 371

MySQL all select rows in range

I need some help retrieving data from rows in range. Let's say this is my table:

enter image description here

That's what I need:

I need to sort the rows by ID and then get 9 rows, where the gameID is 1 and winner's unique_id must be in the middle. For example:

$gameID = 1;
$winner = "iii";

so it should return:

5.eee
6.fff
7.ggg
8.hhh
9.iii <--- winner in the middle
10.jjj
11.kkk
12.lll
13.mmm

How can I achieve this result?

Thanks.

EDIT:

$b = $db->query("SELECT * FROM test WHERE gameID = 1 AND unique_id = 'iii'");
$res = $b->fetch();
$winnerID = $res['ID'];
$b2 = $db->query("SELECT * FROM test WHERE ID BETWEEN $winnerID-4 AND $winnerID+4 ORDER BY ID ASC");
$data = $b2->fetchAll();

This works, but I was wondering if it's possible in a single row.

Upvotes: 1

Views: 58

Answers (1)

Dave Chen
Dave Chen

Reputation: 10975

Is this what you're looking for?

It first finds the id of the winner, then selects rows with IDs smaller and greater than 4. (4 + 4 + 1 = 9)

SELECT * 
FROM rows 
WHERE id BETWEEN 
    (SELECT @id := id FROM rows WHERE unique_id = "iii") - 4 AND @id + 4

Example

Upvotes: 1

Related Questions