Mysql Query With Rank

The time now is Sat 21 Dec, 2024 13:28 • All times are UTC + 1 Hour

Mysql Query With Rank

Page 1 of 1

KugeLSichA

Joined: August 2006
Posts: 803
Location:

Dresden

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

____________

Play Games at GamesCampus!

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

Informpro

Joined: October 2008
Posts: 1110
Location:

Re: Mysql Query With Rank

Code: [Download] [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: [Download] [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

 }

#2 Sat 02 Apr, 2011 01:14

KugeLSichA

Joined: August 2006
Posts: 803
Location:

Dresden

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: [Download] [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...

____________

Play Games at GamesCampus!

Last edited by KugeLSichA on Sun 03 Apr, 2011 16:22; edited 1 time in total

#3 Sun 03 Apr, 2011 16:13

Informpro

Joined: October 2008
Posts: 1110
Location:

Re: Mysql Query With Rank

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

Code: [Download] [Hide]

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

Much more readable than

Code: [Download] [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 ;-).

#4 Sun 03 Apr, 2011 16:17

KugeLSichA Joined: August 2006 Posts: 803 Location: Dresden	Re: Mysql Query With Rank Ok thanks ____________ Play Games at GamesCampus!
#5 Sun 03 Apr, 2011 16:23

Page 1 of 1

Was this topic useful?

Link this topic
URL
BBCode
HTML

The time now is Sat 21 Dec, 2024 13:28 • All times are UTC + 1 Hour

Permissions List

You cannot post new topics
You cannot reply to topics
You cannot edit your posts
You cannot delete your posts
You cannot vote in polls
You cannot attach files
You can download files
You cannot post calendar events

Icy Phoenix

Mysql Query With Rank

Was this topic useful?

Was this topic useful?