Search code examples
phpmysqlpdoranking

Show trophies on top 5% and 10% in the world for the country [Ranking System by Percent]

Working on a php game so I have a table "country_development_areas" with trophies that should appear in users country pages. Each trophy starts from "value" 0 and up. The bigger the value the bigger the rank. I have attached an image with the table bellow:

enter image description here

There should be 2 lines showing on user country page: - 1st line showing trophies corresponding to country that are in top 5% in the world for the "value" field. - 2nd line showing trophies corresponding to country that are in top 10% in the world for the "value" field.

Rank should be done globally by "value" compared to all countries and shows the line of trophies corresponding to the country. It should also has in the link title the corresponding rank like "Most Efficient Economies: 3,420th".

Bellow is the code I have until now, it shows the trophies but it shows all so I don't have an idea how to make them show by percent ranking.

<?php
    // Get all rows from country_development_areas table
    try {
        $stmt = $db->prepare('SELECT * FROM country_development_areas WHERE country = :idCountry ORDER BY value DESC');
        $stmt->execute(array(':idCountry' => $row_country['id']));
    } catch(PDOException $e) {
        $error[] = $e->getMessage();
    }
?>


<div id="trophycabinet">

                        <?php
                        while($row_country_development_area = $stmt->fetch(PDO::FETCH_ASSOC))  {
                        ?>

                            <a class="top5percent" href="/nation=united_states_of_america/detail=trend?censusid=<?php echo $row_country_development_area['id']; ?>" title="<?php echo $row_country_development_area['title']; ?>: 3,420th">
                                <?php echo $row_country_development_area['icon']; ?>
                            </a>

                        <?php } ?>
                        
</div>

First for top 5% the user should see one line with trophies like in the bellow screenshot. It's what currently the script shows but only showing all trophies for the country, it doesn't take only the top 5% compared to the world since I don't know how to do it.

enter image description here

Secondly should be a second line like the previous that would show trophies line for top 10%.

Solution

  • Here is the complete code, including the rank positions. You can add as many top types as you want.

    • Don't use id = 0 in id columns!
    • Put no icon html in tables. Put just the icon names in the icon column, like "hand-peace-o".
    • For proper prepared statements + validations + exception handling see my answer (the EDIT part also, if you want to use a class).
    • I used only the icon name in the icon column.
    • You should use my recommendation from inside the catch blocks. Because an exception is usually an error type after which you would not want to continue any program steps, but just to display the error message on screen and exit.
    • Try to completely avoid using while statements in your codes, in any programming language. Except maybe there, where they are... unavoidable. E.g. if they are good documented, like, for example, on the PHP documentation website for different data access functions, and are really needed, then use them. But with care, because they can result in infinite loops which can completely overload your server.
    • Separate the PHP data access part from the HTML part. If you need data, fetch all of it in PHP arrays and in the upper part of the PHP page. In the HTML part of the PHP page just iterate through these arrays. So, in short, don't mix PHP data access codes with HTML codes. Like: no fetch() inside the HTML part of the PHP page.

    Good luck.

    PHP code:

    <?php
// Get all rows from country_development_areas table
try {
    // Read from HTTP POST.
    $country = 1;

    /*
     * Array containing the top types (top 5%, top 10%, top 20%, etc) as keys and
     * the areas lists for each top as values.
     */
    $tops = array(
        5 => array(),
        10 => array(),
        20 => array(),
        80 => array(),
    );

    // Create a PDO instance as db connection to a MySQL db.
    $db = new PDO(
            'mysql:host=localhost;port=3306;dbname=yourDB'
            , 'yourUser'
            , 'yourPass'
    );

    // Assign the driver options to the db connection.
    $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
    $db->setAttribute(PDO::ATTR_PERSISTENT, TRUE);

    // Iterate through tops and fetch the top areas.
    foreach ($tops as $top => $results) {
        // The sql statement - it will be prepared.
        $sql = 'SELECT 
                    cda.*,
                    (
                        SELECT (COUNT(DISTINCT value) + 1)
                        FROM country_development_areas
                        WHERE 
                            name = cda.name
                            AND value > cda.value
                    ) AS rankPositionInTheWorld,
                    (
                        SELECT (COUNT(DISTINCT value) + 1)
                        FROM country_development_areas
                        WHERE 
                            name = cda.name 
                            AND value > cda.value 
                            AND value >= :topDownLimit1 * (
                                SELECT SUM(IFNULL(value, 0)) 
                                FROM country_development_areas 
                                WHERE name = cda.name
                            )
                    ) AS rankPositionInTop 
                FROM country_development_areas AS cda
                WHERE
                    cda.country = :country AND
                    cda.value >= :topDownLimit2 * (
                        SELECT SUM(IFNULL(value, 0)) 
                        FROM country_development_areas 
                        WHERE name = cda.name
                    )';

        // The input parameters list for the prepared sql statement.
        $bindings = array(
            ':country' => $country,
            ':topDownLimit1' => 1 - $top / 100,
            ':topDownLimit2' => 1 - $top / 100,
        );

        // Prepare and validate the sql statement.
        $stmt = $db->prepare($sql);

        if (!$stmt) {
            throw new UnexpectedValueException('The sql statement could not be prepared!');
        }

        // Bind the input parameters to the prepared statement.
        foreach ($bindings as $key => $value) {
            // Get the name of the input parameter by its key in the bindings array.
            $inputParameterName = is_int($key) ? ($key + 1) : (':' . ltrim($key, ':'));

            // Get the PDO::PARAM_* constant, e.g the data type of the input parameter, by its value.
            if (is_int($value)) {
                $inputParameterDataType = PDO::PARAM_INT;
            } elseif (is_bool($value)) {
                $inputParameterDataType = PDO::PARAM_BOOL;
            } else {
                $inputParameterDataType = PDO::PARAM_STR;
            }

            // Bind and validate the binding of the input parameter.
            $bound = $stmt->bindValue($inputParameterName, $value, $inputParameterDataType);

            if (!$bound) {
                throw new UnexpectedValueException('An input parameter could not be bound!');
            }
        }

        // Execute the prepared statement.
        $executed = $stmt->execute();

        if (!$executed) {
            throw new UnexpectedValueException('The prepared statement could not be executed!');
        }

        /// Fetch and validate the areas list.
        $topAreas = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if ($topAreas === FALSE) {
            throw new UnexpectedValueException('Fetching data failed!');
        }

        $tops[$top] = $topAreas;
    }

    // Close connecion.
    $connection = NULL;
} catch (PDOException $e) {
    $error[] = $e->getMessage();

    // My recommendation:
    // echo $e->getMessage();
    // $logger->log($e);
    // exit();
} catch (Exception $e) {
    $error[] = $e->getMessage();

    // My recommendation:
    // echo $e->getMessage();
    // $logger->log($e);
    // exit();
}
?>
<!DOCTYPE html>
<html>
    <head>
        <meta charset="UTF-8">
        <title>Trophies test</title>

        <!-- ======================================= -->
        <!-- CSS resources -->
        <!-- ======================================= -->

        <!-- Font-Awesome -->
        <link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" type="text/css" rel="stylesheet" />

        <!-- Bootstrap -->
        <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" type="text/css" rel="stylesheet" />

        <!-- ======================================= -->
        <!-- JS resources -->
        <!-- ======================================= -->

        <!-- jQuery -->
        <script src="https://code.jquery.com/jquery-3.2.1.min.js" type="text/javascript" integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4=" crossorigin="anonymous"></script>

        <!-- Bootstrap -->
        <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" type="text/javascript"></script>

        <style type="text/css">
            body {
                padding: 30px 15px;
            }

            .legend {
                margin-bottom: 30px;
                max-width: 30%;
            }

            .legend-title {
                background-color: #eee;
            }

            .trophy-cabinet-title {
                margin-bottom: 10px;
            }

            .trophy-cabinet {
                margin-bottom: 20px;
            }

            .top-area {
                padding: 10px;
                border: 1px solid #ccc;
                border-radius: 4px;
                display: inline-block;
            }
        </style>
    </head>
    <body>

        <div class="list-group legend">
            <div class="list-group-item legend-title">
                Legend
            </div>
            <div class="list-group-item">
                WP: Rank position in the world
            </div>
            <div class="list-group-item">
                TP: Rank position in the top
            </div>
        </div>

        <?php
        // Just for testing, to see the areas list of all tops on screen.
        // echo '<pre>' . print_r($tops, TRUE) . '</pre>';
        ?>

        <?php
        foreach ($tops as $top => $areas) {
            /*
             * If the list of areas for the current top 
             * is not empty, then display the areas.
             */
            if ($areas) {
                ?>
                <div class="trophy-cabinet-title">
                    Trophy cabinet for top <?php echo $top; ?>%.
                </div>
                <div id="trophyCabinet<?php echo $top; ?>Percent" class="trophy-cabinet">
                    <?php
                    foreach ($areas as $area) {
                        $areaId = $area['id'];
                        $areaIcon = $area['icon'];
                        $areaTitle = $area['title'];
                        $areaRankPositionInTheWorld = $area['rankPositionInTheWorld'];
                        $areaRankPositionInTop = $area['rankPositionInTop'];
                        ?>
                        <a class="top-area top<?php echo $top; ?>percent" href="/nation=united_states_of_america/detail=trend?censusid=<?php echo $areaId; ?>" title="<?php echo $areaTitle; ?>: <?php echo $areaRankPositionInTheWorld; ?>">
                            <div class="trophy">
                                <i class="fa fa-<?php echo $areaIcon; ?>"></i> WP <?php echo $areaRankPositionInTheWorld; ?> &bullet; TP <?php echo $areaRankPositionInTop; ?>
                            </div>
                        </a>
                        <?php
                    }
                    ?>
                </div>
                <?php
            }
        }
        ?>

    </body>
</html>

    Used data:

    I used the following data:

    ========================================
Create table syntax
========================================

DROP TABLE IF EXISTS `country_development_areas`;

CREATE TABLE `country_development_areas` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country` int(11) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `value` int(11) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

========================================
Insert values syntax
========================================

INSERT INTO `country_development_areas` (`id`, `country`, `name`, `value`, `icon`, `title`)
VALUES
    (0,1,'Civil Rights',2,'hand-peace-o','Most Free People'),
    (1,1,'Economy',10,'area-chart','Most Efficient Economies'),
    (2,1,'Political Freedom',8,'handshake-o','Most Political Freedom'),
    (3,1,'Population',1,'users','Largest Population'),
    (4,2,'Civil Rights',100,'hand-peace-o','Most Free People'),
    (5,2,'Political Freedom',2,'handshake-o','Most Political Freedom');