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; ?> • 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');
