Mysql Query With Rank

Posts: 803 Location: Dresden

Fri 01 Apr, 2011 23:22

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

Profile PM Website ICQ

Informpro

Quote

Posts: 1110

Sat 02 Apr, 2011 01:14

Subject: 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

 }

Profile PM

KugeLSichA

Quote

Posts: 803 Location: Dresden

Sun 03 Apr, 2011 16:13

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

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

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

Profile PM Website ICQ

Informpro

Quote

Posts: 1110

Sun 03 Apr, 2011 16:17

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

Profile PM

KugeLSichA

Quote

Posts: 803 Location: Dresden

Sun 03 Apr, 2011 16:23

Subject: Re: Mysql Query With Rank

Ok thanks :)

Profile PM Website ICQ

This topic is locked: you cannot edit posts or make replies.

Page 1 of 1

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

This is a "Lo-Fi" version of our main content. To view the full version with more information, formatting and images, please click here.

Powered by Icy Phoenix based on phpBB
Generation Time: 0.2129s (PHP: 15% SQL: 85%)
SQL queries: 11 - Debug Off - GZIP Enabled

Icy Phoenix

Icy Phoenix - Powering Communities

Mysql Query With Rank