How to search for a specific value within a SQL result?

Question

I have a SQL database with the following structure and information:

idClub   Pts   GD
1        3      2
2        4      0
3        5     -1
4        0     -3
5        9      5
6        1      2
7        3      1
8        0     -2
9        6      0
10       7      5

If if do:

$result = $mysqli->query("SELECT * FROM table ORDER BY Pts Desc, GD Desc");

It will deliver this:

idClub   Pts   GD
5        9      5    //Row Number = 1
10       7      5    //Row Number = 2
9        6      0    //Row Number = 3
3        5     -1    //Row Number = 4
2        4      0    //Row Number = 5
1        3      2    //Row Number = 6
7        3      1    //Row Number = 7
6        1      2    //Row Number = 8
8        0     -2    //Row Number = 9
4        0     -3    //Row Number = 10

Each idClub has its own profile page - profile.php?Id=X - in which I need to show the club with the id but also the 2 teams that are above and below in the table.

Is there any way of knowing the row number (position) of a given value in a $result? For example, profile.php?Id=3 it will be 4; for profile.php?Id=1 it will be 6, and so on..

For instance, for profile.php?Id=3 I need a $result that delivers the following:

idClub   Pts   GD
10       7      5
9        6      0
3        5     -1
2        4      0
1        3      2

Thanks in advance.

Answer 1

First, take your present query,

SELECT
  *
FROM
  atable
ORDER BY
  Pts DESC,
  GD DESC

and add row numbering to it using a well-known technique that involves variable assignment:

SELECT
  atable.*,
  @row := @row + 1 AS row
FROM
  atable
CROSS JOIN
  (SELECT @row := 0) AS p
ORDER BY
  Pts DESC,
  GD DESC

This will assign rankings to your rows according to the specified order. Now, if we knew the row number associated with the specified idClub, we'd just use the above query as a derived table and filter its result set on the condition of

WHERE
  row BETWEEN @idXrow - 2 AND @idXrow + 2

And we can find out that @idXrow value. For that, we can add one more variable assignment to the subquery:

@idXrow := CASE idClub WHEN 3 THEN @row ELSE @idXrow END

where 3 is the specified idClub value – in your final query it might need to be replaced with a parameter reference. Anyway, the complete query to do the job would look like this:

SELECT
  row,
  idClub,
  Pts,
  GD
FROM
  (
    SELECT
      atable.*,
      @row := @row + 1 AS row,
      @idXrow := CASE idClub WHEN 3 THEN @row ELSE @idXrow END
    FROM
      atable
    CROSS JOIN
      (SELECT @row := 0) AS p
    ORDER BY
      Pts DESC,
      GD DESC
  ) AS s
WHERE
  row BETWEEN @idXrow - 2 AND @idXrow + 2
;

Answer 2

If you're talking about just the query in $result, you can keep track of a variable that is iterated in your print loop like this:

$result = $mysqli->query("SELECT * FROM table ORDER BY Pts Desc, GD Desc");
$c = 0;

echo "<table>\n<tr>\n<td>idClub</td>\n<td>Pts</td>\n<td>GD</td>\n<td>Row #</td>\n</tr>\n";

while($row = mysqli_fetch_assoc($result)) {
    echo "<tr>\n<td>$row[idClub]</td>\n<td>$row[Pts]</td>\n<td>$row[GD]</td>\n<td>$c</td>\n</tr>\n";
    $c++;
}

echo "</table>";

Answer 3

Another possible solution:

$selClub = $_GET["Id"];
$result = $mysqli->query("SELECT idClub FROM table");
for ($i = 1; $i <= $result->num_rows; $i++) {
    if ( $result->fetch_object()->idClub == $selClub )
        $position = $i;
}

if ( $position <= 2 ) {
    $result = $mysqli->query("SELECT * FROM table ORDER BY Pts Desc, GD Desc LIMIT 0, 5");
} elseif ( $position >= $result->num_rows - 1 ) {
    $result = $mysqli->query("SELECT * FROM table ORDER BY Pts Desc, GD Desc LIMIT ($result->num_rows-5), 5");
} else {
    $result = $mysqli->query("SELECT * FROM table ORDER BY Pts Desc, GD Desc LIMIT ($position-3), 5");
}

Is it actually a problem to use the same name for the first and second $result?