Mysql Query With Rank
| Article |
|---|
 Mysql Query With Rank |
Hey guys...
Yeah its me again   
Another thing i need to figure out...
the DB structure is with id, user_id, score,
i have a user_id and a score (number)... and i want to count the entries from the DB... order by score and gives back the Rank/place/count if the user_id matches...
for example:
id=1 user_id=2 score=50 <--------- 1. place
id=2 user_id=3 score=23 <--------- 3. place
id=3 user_id=7 score=34 <--------- 2. place
i hope you can understand what i mean, and help me with that... thanks...
|
|
KugeLSichA [ Fri 01 Apr, 2011 23:22 ]
|
 |
Sponsors
|
Icy Phoenix is an open source project, you can show your appreciation and support future development by donating to the project.
|
|
Mysql Query With Rank
| Comments |
|---|
Re: Mysql Query With Rank |
- SELECT user_id, username, user_rank, user_rank2, user_rank3 ...
- FROM USERS_TABLE
- ORDER BY score DESC
The PHP code should'nt be too difficult ;-). Something like
-
<?php $result = $db->sql_result($sql);
-
$place = 0;
-
while ($row = $db->sql_fetchrow($result))
-
{
-
echo 'Hi I\' ' . $row['username'] . ', id ' . $row['user_id'] . '. My place is ' . ++$place . ', and my first rank is ' . $row['user_rank'] . '<br />'; //feel free to display more ranks
-
}
|
|
Informpro [ Sat 02 Apr, 2011 01:14 ]
|
|
Re: Mysql Query With Rank |
Thanks Informpro,
you point me in the right direction 
I´ve done it like this... maybe you can take a look.. the code works well, but maybe its not perfect.. let me know if i can tweak it a bit
-
<?php
-
/*
-
########################
-
## Dynamic Signature
-
## KugeLSichA for sig.blademaniacs.de
-
## 2011/04/03
-
## Version: 1.0.2
-
########################
-
*/
-
-
// CTracker_Ignore: File checked by human
-
define('IN_ICYPHOENIX', true);
-
if (!defined('IP_ROOT_PATH')) define('IP_ROOT_PATH', './');
-
if (!defined('PHP_EXT')) define('PHP_EXT', substr(strrchr(__FILE__, '.'), 1));
-
include(IP_ROOT_PATH . 'common.' . PHP_EXT);
-
-
$this_img = imagecreatetruecolor(100, 15);
-
$color = imagecolorallocate($this_img, 255, 255, 255);
-
imagestring($this_img, 3, 0, 0, 'error', $color);
-
-
if(!empty($_REQUEST['username']))
-
{
-
$user = $_REQUEST['username'];
-
$sql = "SELECT hc.*, u.*
-
FROM " . COURSE_HC_TABLE . " hc, " . USERS_TABLE . " u
-
WHERE u.user_id = hc.user_id
-
ORDER by hc_x ASC";
-
if(!($result = $db->sql_query($sql)))
-
{
-
message_die(GENERAL_ERROR, 'Could not query users', '', __LINE__, __FILE__, $sql);
-
}
-
$counter = 0;
-
while ($row = $db->sql_fetchrow($result))
-
{
-
$userid = $row['user_id'];
-
$user_hc = $row['hc_x'];
-
$user_hc_rank = $counter++;
-
$username = $row['username'];
-
$user_so_char_icon = $row['user_so_char_icon'];
-
$user_from_flag = $row['user_from_flag'];
-
$user_so_rank_icon = $row['user_so_rank_icon'];
-
$user_so_level = $row['user_so_level'];
-
$last_update_time = $row['last_update_time'];
-
$overall_albatross = $row['overall_albatross'];
-
$overall_hio = $row['overall_hio'];
-
$max_drive = $row['max_drive'];
-
$hc_x = $row['hc_x'];
-
$hdcp = $row['hdcp'];
-
-
if($user == $username)
-
{
-
$sql = "SELECT * FROM " . GROUPS_TABLE . " g, " . USER_GROUP_TABLE . " ug
-
WHERE g.group_id = ug.group_id
-
AND g.group_single_user <> 1
-
AND ug.user_id = '$userid'
-
AND g.group_id <> 1
-
AND g.group_id <> 2
-
AND g.group_id <> 3
-
AND g.group_id <> 4
-
AND g.group_id <> 5
-
AND g.group_id <> 43
-
LIMIT 1";
-
if(!($result = $db->sql_query($sql)))
-
{
-
message_die(GENERAL_ERROR, 'Could not query users', '', __LINE__, __FILE__, $sql);
-
}
-
$grow = $db->sql_fetchrow($result);
-
-
$group = $grow['group_name'];
-
-
if($user_so_char_icon != '')
-
{
-
$char = './images/shotonline/chars/' . $user_so_char_icon;
-
}
-
else
-
{
-
$char = './images/shotonline/chars/blank.png';
-
}
-
$src = imagecreatefrompng('./images/flags/' . $user_from_flag);
-
$src2 = imagecreatefrompng('./images/shotonline/level/' . $user_so_rank_icon);
-
$src3 = imagecreatefrompng($char);
-
$src4 = imagecreatefrompng('./images/shotonline/courses/alba_small.png');
-
$src5 = imagecreatefrompng('./images/shotonline/courses/hio_small.png');
-
-
$font = './images/fonts/triplex_bold.ttf'; // Font URL
-
-
$this_img = imagecreatetruecolor(500, 100);
-
$color = imagecolorallocate($this_img, 0, 0, 0);
-
$bg = imagecolorallocatealpha($this_img, rand(250, 250), rand(200, 250), rand(200, 250), 50);
-
$blank = imagecolorallocate($this_img, 255, 255, 255);
-
$color_grey = imagecolorallocate($this_img, 128, 128, 128);
-
$color_orange = imagecolorallocate($this_img, 251, 87, 9);
-
$color_green = imagecolorallocate($this_img, 0, 153, 0);
-
$color_bluegrey = imagecolorallocate($this_img, 101, 129, 143);
-
$color_blue = imagecolorallocate($this_img, 9, 110, 161);
-
$_la = imagecolorallocate($this_img, 219, 224, 229);
-
$_lb = imagecolorallocate($this_img, 244, 246, 247);
-
imagefill($this_img, 0, 0, $blank);
-
imagefilledrectangle($this_img, 2, 2, 498, 98, $_la);
-
imagefilledrectangle($this_img, 3, 21, 497, 97, $_lb);
-
imagefilledrectangle($this_img, 9, 25, 490, 85, $_la);
-
imagefilledrectangle($this_img, 10, 26, 489, 84, $blank);
-
-
imagealphablending($this_img, 1);
-
imagealphablending($src, 1);
-
imagecopy($this_img, $src, 11, 6, 0, 0, 16, 11); //flag
-
imagealphablending($src2, 1);
-
imagecopy($this_img, $src2, 35, 5, 0, 0, 13, 13); //level
-
imagealphablending($src3, 1);
-
imagecopy($this_img, $src3, 13, 28, 0, 0, 60, 55); //char
-
imagealphablending($src4, 1);
-
imagecopy($this_img, $src4, 89, 31, 0, 0, 71, 9); //alba
-
imagealphablending($src5, 1);
-
imagecopy($this_img, $src5, 89, 45, 0, 0, 71, 9); //hio
-
-
if ($user_so_level > 100)
-
{
-
$lvl_color = $color_green;
-
}
-
else
-
{
-
$lvl_color = $color_blue;
-
}
-
imagettftext($this_img, 15, 0, 55, 18, $color_grey, $font, 'LvL:');
-
imagettftext($this_img, 15, 0, 88, 18, $lvl_color, $font, $user_so_level);
-
imagettftext($this_img, 15, 0, 120, 18, $color_orange, $font, $username);
-
-
imagettftext($this_img, 10, 0, 330, 16, $color_grey, $font, 'Updated: ');
-
imagettftext($this_img, 10, 0, 395, 15, $color_green, $font, create_date('d.m.Y G:i', $last_update_time, $board_config['board_timezone']));
-
-
imagettftext($this_img, 10, 0, 180, 38, $color_blue, $font, $overall_albatross);
-
imagettftext($this_img, 10, 0, 180, 52, $color_orange, $font, $overall_hio);
-
-
imagettftext($this_img, 10, 0, 90, 66, $color_grey, $font, 'max. Drive: ');
-
imagettftext($this_img, 10, 0, 180, 66, $color_green, $font, $max_drive . ' y');
-
-
imagettftext($this_img, 10, 0, 90, 80, $color_grey, $font, 'Charakter: ');
-
-
if($row['user_so_char_icon'] != '')
-
{
-
$char_rep = str_replace ('./images/shotonline/chars/', '', $char);
-
imagettftext($this_img, 10, 0, 180, 80, $color_green, $font, substr($char_rep, 0, -4));
-
}
-
-
imagettftext($this_img, 10, 0, 275, 38, $color_grey, $font, 'Ø Handicap: ');
-
imagettftext($this_img, 10, 0, 380, 38, $color_blue, $font, $hc_x);
-
-
imagettftext($this_img, 10, 0, 275, 52, $color_grey, $font, 'Ø Handicap Rank: ');
-
imagettftext($this_img, 10, 0, 380, 52, $color_orange, $font, $counter);
-
-
imagettftext($this_img, 10, 0, 275, 66, $color_grey, $font, 'HDCP: ');
-
imagettftext($this_img, 10, 0, 380, 66, $color_green, $font, $hdcp);
-
-
imagettftext($this_img, 10, 0, 275, 80, $color_grey, $font, 'Guild: ');
-
imagettftext($this_img, 10, 0, 320, 80, $color_blue, $font, $group);
-
-
// footer
-
imagettftext($this_img, 6, 0, 12, 95, $color_bluegrey, $font, 'powered by');
-
imagettftext($this_img, 8, 0, 75, 95, $color_bluegrey, $font, 'sig.blademaniacs.de');
-
}
-
/*else
-
{
-
$this_img = imagecreatetruecolor(100, 15);
-
$color = imagecolorallocate($this_img, 255, 255, 255);
-
imagestring($this_img, 0, 0, 0, 'Not in HC-List', $color);
-
}*/
-
}
-
}
-
-
header('Content-Type: image/png');
-
imagepng($this_img);
-
imagedestroy($this_img);
-
imagedestroy($src);
-
imagedestroy($src2);
-
imagedestroy($src3);
-
imagedestroy($src4);
-
imagedestroy($src5);
-
?>
Its for a User Dynamic Signature with data based from Users Profile & Course Handicap site for our Golf MMoG...
Last edited by KugeLSichA on Sun 03 Apr, 2011 16:22; edited 1 time in total |
|
KugeLSichA [ Sun 03 Apr, 2011 16:13 ]
|
|
Re: Mysql Query With Rank |
(use code syntax=php !)
For the group query, use something like - AND g.group_id NOT IN (1, 2, 3, 4, 5, 43)
Much more readable than - AND g.group_id <> 1
- AND g.group_id <> 2
- AND g.group_id <> 3
- AND g.group_id <> 4
- AND g.group_id <> 5
- AND g.group_id <> 43
For everything else, it looks good. Nice job ;-).
|
|
Informpro [ Sun 03 Apr, 2011 16:17 ]
|
|
Re: Mysql Query With Rank |
Ok thanks 
|
|
KugeLSichA [ Sun 03 Apr, 2011 16:23 ]
|
|
|
|
 Was this topic useful?
 Was this topic useful?
|
Page 1 of 1
|
|
|
