Personal Results - Help!

[Mko]

Hey all,

I'm currently trying to get personal results based on the amount of 'points' a user has on my forum.

I then want to display their 'rank', or in what position they are in that point field.

 

Here's what I have tried but haven't succeeded with:

$points = array("a", "b", "c", "d");

$userid = (int) $_GET['userid'];

for ($i = 0; $i < count($points); $i++) {
		$point_query = mysqli_query($database, "SELECT userid, ". $points[$i] .", group FROM user_stats ORDER BY user_stats.". $points[$i] ." DESC, user_stats.group DESC") or print(mysqli_error($database));
		$point_array = mysqli_fetch_array($point_query);
		$point_cnt = mysqli_num_rows($point_query);

		for ($j = 0; $j < $point_cnt; $j++) {
			if ($point_array[0] == $userid) {
				$rank = $j;
				break;
			} else {
				continue;
			}
		} 

    	echo "<tr><td>";
    	echo "<img src='pointicon_".$points[$i].".gif'/> <a href='index.php?pageid=leaders&point=".$points[$i]."'>".ucfirst($points[$i])."</a>";
    	echo "";
    	echo "</td></tr>";
}

 

My guess is that my SQL query isn't performing correctly and that my loops aren't working as intended.

Could anyone help me fix these issues? I'd greatly appreciate it

 

Thanks,

Mark

[Barand]

One problem is that have a column "group" which is a mysql reserved word so you need backticks around it (`group`)

 

Two, you shouldn't run queries in loops.

[Christian F.]

You really should handle the MySQL errors from the query, then you'd see what's wrong. It's quite simple, if my suspicions are correct.

[Mko]

One problem is that have a column "group" which is a mysql reserved word so you need backticks around it (`group`)

 

Two, you shouldn't run queries in loops.

Alright, I'll change group to something else.

As for running queries in loops...what would you suggest doing instead?

 

You really should handle the MySQL errors from the query, then you'd see what's wrong. It's quite simple, if my suspicions are correct.

Changing "group" to something else should address the issue, correct?

 

[Christian F.]

Yes, if there isn't anything else wrong. To avoid running queries inside loops, you'll need to look up JOINs.

That said, you really should handle those SQL errors.

[Barand]

If you can explain what you are trying do, maybe we can help. Code that does not work only tells us what you don't want to do.

[Mko]

If you can explain what you are trying do, maybe we can help. Code that does not work only tells us what you don't want to do.

I'm currently trying to get personal results based on the amount of 'points' a user has on my forum. From these amounts, I want to order all of them in descending order, then find the 'rank' (or the row number using this sort) the user lies in based on this descending sort.

After doing that, I want to echo out the rank. I want this do be done for "a", "b", "c", and "d", preferably in a loop of some sort.

[Barand]

What is the significance of the a, b, c, d?

[Mko]

What is the significance of the a, b, c, d?

Different point 'values', as in A Points, B Points, C Points, and D Points.

[Barand]

Assuming your data looks like this

+---------+------------+------+------+------+------+

| user_id | user_group | a    | b    | c    | d    |

+---------+------------+------+------+------+------+

|      1 | A          |  25 |  95 |  47 |  85 |

|      2 | B          |  15 |  75 |  78 |  32 |

|      3 | A          |  35 |  50 |  15 |  20 |

|      4 | A          |  85 |  35 |  27 |  41 |

|      5 | B          |  12 |  40 |  95 |  23 |

|      6 | C          |  40 |  31 |  47 |  84 |

|      7 | B          |  30 |  22 |  63 |  22 |

|      8 | B          |  25 |  64 |  18 |  12 |

|      9 | C          |  20 |  44 |  10 |  18 |

+---------+------------+------+------+------+------+

 

then using a subquery to normalize your data (run the subquery on its own to see a better way to hold your data)

$sql = "SELECT x.user_id, x.user_group, x.point_type, x.points
FROM (
    SELECT 'A' as point_type, user_id, user_group, a as points
    FROM user_stats
    UNION
    SELECT 'B' as point_type, user_id, user_group, b as points
    FROM user_stats
    UNION
    SELECT 'C' as point_type, user_id, user_group, c as points
    FROM user_stats
    UNION
    SELECT 'D' as point_type, user_id, user_group, d as points
    FROM user_stats
    ) as x
ORDER BY point_type, points DESC";
$res = mysql_query($sql);

$user = 5;                                // target user id

$prev = '';

echo "<pre>";
printf ("%-5s %7s %5s\n\n", 'Type', 'Pts', 'Rank');
while (list($uid, $grp, $type, $pts) = mysql_fetch_row($res)) {
    if ($type != $prev) {
        $rank = 1;
        $prev = $type;
    }
    if ($uid == $user) {
        printf ("%-5s %7d %5d\n", $type, $pts, $rank);
    }
    $rank++;
}
echo '</pre>';

 

I get these results for user 5

Type      Pts  Rank

 

A          12    9

B          40    6

C          95    1

D          23    5

     

 

[Mko]

Assuming your data looks like this

+---------+------------+------+------+------+------+

| user_id | user_group | a    | b    | c    | d    |

+---------+------------+------+------+------+------+

|      1 | A          |  25 |  95 |  47 |  85 |

|      2 | B          |  15 |  75 |  78 |  32 |

|      3 | A          |  35 |  50 |  15 |  20 |

|      4 | A          |  85 |  35 |  27 |  41 |

|      5 | B          |  12 |  40 |  95 |  23 |

|      6 | C          |  40 |  31 |  47 |  84 |

|      7 | B          |  30 |  22 |  63 |  22 |

|      8 | B          |  25 |  64 |  18 |  12 |

|      9 | C          |  20 |  44 |  10 |  18 |

+---------+------------+------+------+------+------+

 

then using a subquery to normalize your data (run the subquery on its own to see a better way to hold your data)

$sql = "SELECT x.user_id, x.user_group, x.point_type, x.points
FROM (
    SELECT 'A' as point_type, user_id, user_group, a as points
    FROM user_stats
    UNION
    SELECT 'B' as point_type, user_id, user_group, b as points
    FROM user_stats
    UNION
    SELECT 'C' as point_type, user_id, user_group, c as points
    FROM user_stats
    UNION
    SELECT 'D' as point_type, user_id, user_group, d as points
    FROM user_stats
    ) as x
ORDER BY point_type, points DESC";
$res = mysql_query($sql);

$user = 5;                                // target user id

$prev = '';

echo "<pre>";
printf ("%-5s %7s %5s\n\n", 'Type', 'Pts', 'Rank');
while (list($uid, $grp, $type, $pts) = mysql_fetch_row($res)) {
    if ($type != $prev) {
        $rank = 1;
        $prev = $type;
    }
    if ($uid == $user) {
        printf ("%-5s %7d %5d\n", $type, $pts, $rank);
    }
    $rank++;
}
echo '</pre>';

 

I get these results for user 5

Type      Pts  Rank

 

A          12    9

B          40    6

C          95    1

D          23    5

   

 

That helped me resolve my issue, thanks a bunch!