Icy Phoenix :: View topic - Mysql Query With Rank

KugeLSichA [ Fri 01 Apr, 2011 23:22 ]
Post subject: 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...

Informpro [ Sat 02 Apr, 2011 01:14 ]
Post subject: Re: Mysql Query With Rank

Code: [Hide]

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

Code: [Hide]

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

 }

KugeLSichA [ Sun 03 Apr, 2011 16:13 ]
Post subject: 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

Spoiler: [ Show ]

Code: [Hide]

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

Informpro [ Sun 03 Apr, 2011 16:17 ]
Post subject: Re: Mysql Query With Rank

(use code syntax=php !)
For the group query, use something like

Code: [Hide]

AND g.group_id NOT IN (1, 2, 3, 4, 5, 43)

Much more readable than

Code: [Hide]

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

KugeLSichA [ Sun 03 Apr, 2011 16:23 ]
Post subject: Re: Mysql Query With Rank

Ok thanks

Icy Phoenix

Archived phpBB Topics (Styles, Mods, Support) - Mysql Query With Rank