https://www.icyphoenix.com/viewtopic.php?f=26&t=7759
-----------------------------------
KugeLSichA
Fri 01 Apr, 2011 23:22

Mysql Query With Rank
-----------------------------------
Hey guys...

Yeah its me again  :shock:  :mrviolet:  8)

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...


-----------------------------------
Informpro
Sat 02 Apr, 2011 01:14

Re: Mysql Query With Rank
-----------------------------------
[code syntax="sql"]SELECT user_id, username, user_rank, user_rank2, user_rank3 ...
FROM USERS_TABLE
ORDER BY score DESC[/code]The PHP code should'nt be too difficult ;-). Something like
[code syntax="php"]<?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
}[/code]


-----------------------------------
KugeLSichA
Sun 03 Apr, 2011 16:13

Re: Mysql Query With Rank
-----------------------------------
Thanks [b]Informpro[/b],

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  8) 

[spoiler][code syntax=php]<?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);
?>[/code][/spoiler]

Its for a User Dynamic Signature with data based from Users Profile & [url=http://www.blademaniacs.de/course_hc.php]Course Handicap site for our Golf MMoG...

[img]http://blademaniacs.de/user/HerzScheisse.png[/img]


-----------------------------------
Informpro
Sun 03 Apr, 2011 16:17

Re: Mysql Query With Rank
-----------------------------------
(use code syntax=php !)
For the group query, use something like [code]AND g.group_id NOT IN (1, 2, 3, 4, 5, 43)[/code]Much more readable than [code]                    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[/code]For everything else, it looks good. Nice job ;-).


-----------------------------------
KugeLSichA
Sun 03 Apr, 2011 16:23

Re: Mysql Query With Rank
-----------------------------------
Ok thanks :)


