Php Arrays With Basic Mysql Queries Or Mysql Advanced Queries?

[kalster]

i am using php for the main programming language. i would like to display a html table for the logged in users level rank. so if that user is at a rank of 20, i would like to display a table from rank 15 to 25. that would display the logged in user at the middle of the table. however, if the user has a rank of 2 then i would need to display a rank table from 1 to 10. So i am not sure if mysql can do this kind of query.

 

would it be more easer for me to create php arrays from a mysql results where each array would hold the usernames rank and then display those arrays in a html table? My idea is if i had the arrays holding the users mysql information then i could use those arrays to make all kinds of user information html tables, including some advanced php searches, instead of using a different mysql query for the results. The problem with arrays is that the memory usage would be double that of a query. so what would be the best way to go about this. php arrays using a few basic mysql queries or mysql advanced queries?

[Barand]

this will help with the ranking

 

http://forums.phpfreaks.com/topic/269683-give-same-rank-where-points-are-the-same/?do=findComment&comment=1386466

 

I'll experiment with this in conjunction with the LIMIT clause and get back

[Barand]

I'd do something like this

 

$mysqli = new mysqli('host', 'user', 'pwd', 'database');

$targetId = intval($_GET['id']);	 // get the target id

/******************************
* Find row number of target id
******************************/
$sql = "SELECT row
FROM (
SELECT pid,
 @rownum := @rownum + 1 AS row
FROM bg_points
 INNER JOIN (
	 SELECT @rownum := 0
	 ) as init
ORDER BY points DESC
) as findrow
WHERE pid = $targetId";

$res = $mysqli->query($sql);
$row = $res->fetch_assoc();

/******************************
* get the start row for query
******************************/
$start = $row['row'] < 5 ? 0 : $row['row'] - 5;
$res->free();

/***********************************
* get rows either side of target
************************************/
$sql = "SELECT ranking, pid, points
FROM (
SELECT pid,
 @rownum := @rownum + 1 AS row,
 @rank := if (@prev<>points, @rownum, @rank) AS ranking,
 @prev:= points AS points
FROM bg_points
 INNER JOIN (
	 SELECT @rownum := 0, @prev := -1, @rank := 0
	 ) as init
ORDER BY points DESC
) as findranks
LIMIT $start, 10";

$res = $mysqli->query($sql);
while ($row = $res->fetch_assoc()) {
// output row data
}

Edited December 16, 2012 by Barand