Hack Forums
[PHP] SELECT MAX(int) FROM table - Printable Version

+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Web Development (https://hackforums.net/forumdisplay.php?fid=183)
+---- Thread: [PHP] SELECT MAX(int) FROM table (/showthread.php?tid=4611217)

SELECT MAX(int) FROM table - Nozemi™ - Dec 30, 2014

I'm trying to make a highscores page for my RSPS, however, I don't want to include the admin and developer groups. Also I like the ability to exclude usernames. So I made this function, which obviously doesn't work, I understand why, but I'm stuck with how to solve it at the moment. I have been sitting with this highscores for too many hours straight now to think straight.

PHP Code

<?php 
public function get_top_players($skill) {
            global $db;

            $C = new Classes;

            $query = "
                SELECT
                    MAX($skill),
                    username,
                    usergroup
                FROM mybb_users
            ";
            $query_params = array(
                //':skill' => $skill
            );
            try {
                $stmt = $db->prepare($query);
                $result = $stmt->execute($query_params);
            } catch(PDOException $ex) {
                die($ex->getMessage());
            }
            $row = $stmt->fetch();

            $level = $C->get_level($row[$skill]);

            if(!in_array($row['usergroup'], $this->exclude_groups)) {
            if(!in_array($row['username'], $this->exclude_users)) {
                echo '<td class="trow1" align="center"><span class="smalltext"><strong>'.$row['username'].'</strong></span></td>'."\r\n";
                echo '<td class="trow1" align="center"><span class="smalltext"><strong>'.$level.'</strong></span></td>'."\r\n";
            }
            }
        } 

Above is my current function. If anyone could help me solve this while I'm too tired, it would be greatly appreciated!

Solution:

PHP Code

<?php 
public function get_top_players($skill) {
            global $db;
            $C = new Classes;

            if($skill == 'm_total' || $skill == 's_total') {
                if($skill == 'm_total') {
                    $skill = 'totalxp';
                    $query = "
                        SELECT
                        max($skill),
                        totalxp,
                        total,
                        username,
                        usergroup
                        FROM mybb_users
                        WHERE `usergroup` NOT IN (". implode(',', $this->exclude_groups) .")
                        AND `username` NOT IN (". implode(',', array_map('Classes::quote_string', $this->exclude_users)) .")
                    ";
                } elseif($skill == 's_total') {
                    $skill = 'totalxp';
                    $query = "
                        SELECT
                        max($skill),
                        totalxp,
                        total,
                        username,
                        usergroup
                        FROM mybb_users
                        WHERE `usergroup` NOT IN (". implode(',', $this->exclude_groups) .")
                        AND `username` NOT IN (". implode(',', array_map('Classes::quote_string', $this->exclude_users)) .")
                        AND `combat` = 3
                    ";
                }
                try {
                    $stmt = $db->prepare($query);
                    $result = $stmt->execute($query_params);
                } catch(PDOException $ex) {
                    die($ex->getMessage());
                }
                $row = $stmt->fetch();
                echo '<td class="trow1" align="center"><span class="smalltext"><strong><a href="#">'.$row['username'].'</a></strong></span></td>'."\r\n";
                echo '<td class="trow1" align="center"><span class="smalltext"><strong>'.$row['total'].'</strong></span></td>'."\r\n";

            } else {
                $query = "
                    SELECT
                        max($skill),
                        $skill,
                        username,
                        usergroup
                    FROM mybb_users
                    WHERE `usergroup` NOT IN (". implode(',', $this->exclude_groups) .")
                        AND `username` NOT IN (". implode(',', array_map('Classes::quote_string', $this->exclude_users)) .")
                ";
                try {
                    $stmt = $db->prepare($query);
                    $result = $stmt->execute($query_params);
                } catch(PDOException $ex) {
                    die($query.'<br />'.$ex->getMessage());
                }
                $row = $stmt->fetch();
                $level = $C->get_level($row[$skill]);
                echo '<td class="trow1" align="center"><span class="smalltext"><strong><a href="#">'.$row['username'].'</a></strong></span></td>'."\r\n";
                echo '<td class="trow1" align="center"><span class="smalltext"><strong>'.$level.'</strong></span></td>'."\r\n";
            } 

The above function is what I ended up with. It could likely be improved slightly, but it works just fine bor me I believe.

Thanks to TOPMOBBSS for his help I could combine some of his code with my code. I really appreciate everyones attempt to assist me out!

* - szyku01 - Dec 30, 2014

Lol wat, I don't get this query actually. If you want to get players with highest score, for example 10 of them, then just order by skill column and limit the amount of results to 10.
PHP Code

<?php 
$query = '
    SELECT
      `username`,
      `usergroup`
    FROM mybb_users
    ORDER BY `'.$skill.'`
    LIMIT 10
'; 


* - Nozemi™ - Dec 30, 2014

(Dec 30, 2014 06:13 PM)szyku01 Wrote:  Lol wat, I don't get this query actually. If you want to get players with highest score, for example 10 of them, then just order by skill column and limit the amount of results to 10.
PHP Code

<?php 
$query = '
    SELECT
      `username`,
      `usergroup`
    FROM mybb_users
    ORDER BY `'.$skill.'`
    LIMIT 10
'; 

Yeah, I'm done with this part. I just want to display the top user for each skill. If you don't know what RSPS (RuneScape Private Server), it has multiple skills. What I'm trying to return is the top player in each of those skills.

* - pettanko - Dec 31, 2014

SELECT MAX(column_name) FROM table_name;

Are you sure you're using it correctly? Its pretty easy to use.
MAX(string). Using a (int) will force that result.

So if MAX(1001) it will return 1001.

I don't see any error :/ But I might be blind.

* - Gox. - Dec 31, 2014

You can do most of the login within your MySQL statement.

PHP Code

<?php 
$sql = 'SELECT '. $skill .',
      username,
      usergroup
FROM mybb_users
WHERE `usergroup` NOT IN ('. implode(',', $this->exclude_groups) .')
    AND `username` NOT IN ('. implode(',', array_map('quote_string', $this->exclude_users)) .')
ORDER BY `'. $skill .'` DESC
LIMIT 10';

function quote_string($str) {
   return "'". $str ."'";
} 

So, this query will;
  • Select the top 10 users with the highest skill rank

  • Not select users who's in the group $this->exclude_groups

  • Not select users who's username is in $this->exclude_users



You're now free to output as you like, and not check each row on each iteration.

* - Sakuto - Dec 31, 2014

What's the structure of our database, each job have his own column in the users table ?

* - Nozemi™ - Dec 31, 2014

(Dec 31, 2014 04:44 AM)Marshall Lee Wrote:  SELECT MAX(column_name) FROM table_name;

Are you sure you're using it correctly? Its pretty easy to use.
MAX(string). Using a (int) will force that result.

So if MAX(1001) it will return 1001.

I don't see any error :/ But I might be blind.

It's not any error there. It's just that I want to exclude some users by usernames and/or usergroup id. My code works perfectly fine, except that it doesn't filter out any users.

I ended up adding a "WHERE usergroup != X AND usergroup != X AND username != 'USERNAME'" to the query. That works for now, but it's a hassle to edit the excluding, because I'd need to edit the excludes in multiple places. I'd like to get around this, but I'm far from any PHP master haha.

(Dec 31, 2014 10:27 AM)TOPMOBBSS Wrote:  You can do most of the login within your MySQL statement.

PHP Code

<?php 
$sql = 'SELECT '. $skill .',
      username,
      usergroup
FROM mybb_users
WHERE `usergroup` NOT IN ('. implode(',', $this->exclude_groups) .')
    AND `username` NOT IN ('. implode(',', array_map('quote_string', $this->exclude_users)) .')
ORDER BY `'. $skill .'` DESC
LIMIT 10';

function quote_string($str) {
   return "'". $str ."'";
} 

So, this query will;
  • Select the top 10 users with the highest skill rank

  • Not select users who's in the group $this->exclude_groups

  • Not select users who's username is in $this->exclude_users



You're now free to output as you like, and not check each row on each iteration.

Yeah, I have it all working at the moment, but I think I can manage to make it work even better with parts of the code you added to your reply. It looks nice!

(Dec 31, 2014 11:40 AM)Sakuto Wrote:  What's the structure of our database, each job have his own column in the users table ?

It's the default MyBB structure, but I added a few custom columns. Each user as their own row, and each skill has it's own column.

And also thanks to every single of you for your replies, much appreciated!

Happy New Year!