php - 从总投票中 echo 每个记录所占总投票的百分比

我需要从总投票中回显每条记录占总投票的百分比。我知道公式，但无法弄清楚如何在PHP中执行。

<?php

$totalvotes = "SELECT SUM(votes) AS total FROM voting";


$totalvotesresults = mysql_query( $totalvotes )
or die( "Could not get total votes " .mysql_error() );



$data = mysql_fetch_object( $totalvotesresults );
echo "<div>Total number of votes is ". $data->total ."</div>\n";


?>

<?php

$artistname = "SELECT * FROM voting";

$artistnameresults = mysql_query( $artistname )
or die( "Could not get video games " .mysql_error() );

for( $i = 0; $i < mysql_numrows( $artistnameresults ); $i++ ) {



$data = mysql_fetch_array( $artistnameresults );
echo "<div>". $data['artist'] ." has " . $data['votes'] . "  votes ( % of total )</div>\n";


} // ends for loop

?>

最佳答案

如果我正确理解，则可以在一个查询中完成。

如果您的表格中每个艺术家仅包含一行

SELECT artist, votes, total, votes / total * 100 percent
  FROM voting v CROSS JOIN
(
  SELECT SUM(votes) total
    FROM voting
) t
 ORDER BY artist;

如果您的表格允许每个艺术家多行

SELECT artist, SUM(votes) votes, total, SUM(votes) / total * 100 percent
  FROM voting v CROSS JOIN
(
  SELECT SUM(votes) total
    FROM voting
) t
 GROUP BY artist;

两个查询的示例输出：

------------------------------------------
| artist       | votes | total | percent |
------------------------------------------
| Arcangel     | 17    | 63    | 26.9841 |
| Daddy Yankee | 4     | 63    | 6.3492  |
| Farruko      | 14    | 63    | 22.2222 |
| J Alvarez    | 13    | 63    | 20.6349 |
| Jory         | 15    | 63    | 23.8095 |
------------------------------------------

Here is SQLFiddle demo

---

Your php code then might look

$sql = "SELECT artist, votes, total, votes / total * 100 percent
          FROM voting v CROSS JOIN
        (
          SELECT SUM(votes) total
            FROM voting
        ) t
         ORDER BY artist";

$result = mysql_query($sql);
if(!$result) {
    die(mysql_error()); // TODO: better error handling
}

// grab the first row to spit out the total
if ($row = mysql_fetch_assoc($result)) {
    echo "<div>Total number of votes is {$row['total']} </div><hr>";
    displayArtist($row);
}
// display other rows
while($row = mysql_fetch_assoc($result)) {
    displayRow($row);
}

function displayRow($row) {
    echo "<div>{$row['artist']} has {$row['votes']} ({$row['percent']}% of total)</div>";
}

或者你可以简单地做到这一点

$sql = "SELECT artist, votes FROM voting ORDER BY artist";
$result = mysql_query($sql);
if (!$result) {
    die(mysql_error()); // TODO: better error handling
}

$total = 0;
$rows = array();
while($row = mysql_fetch_assoc($result)) {
    $rows[] = $row;
    $total += $row['votes'];
}
mysql_close();

echo "<div>Total number of votes is $total </div><hr>";
foreach ($rows as $row) {
    displayRow($row, $total);
}

function displayRow($row, $total) {
    $percent = round($row['votes'] / $total * 100, 4);
    echo "<div>{$row['artist']} has {$row['votes']} ($percent% of total)</div>";
}

输出：

投票总数63
------------------------------------
Arcangel有17（占总数的26.9841％）
爸爸洋基（Daddy Yankee）有4（占总数的6.492％）
Farruko有14（占总数的22.2222％）
J·阿尔瓦雷斯（J Alvarez）有13（占总数的20.6349％）
乔瑞（Jory）有15（占总数的23.8095％）


附带说明：学习和使用扩展名为PDO或mysqli的准备好的语句。 mysql_*已过时。

关于php - 从总投票中 echo 每个记录所占总投票的百分比，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22355682/