php - 使用 foreach 语句回显 MySQL 行的 SUM

Question

我对如何做到这一点有点困惑；我对所有这些 PDO 东西都很陌生，还不太习惯。基本上，我有两个表，航类和用户。我想找到所有航类的名称，然后查看用户表，然后对“点数”列求和，其中航类等于其中一个航类名称。这是我目前所拥有的，我无法再进一步了

<?php
$qry = "SELECT * FROM flights ORDER by points DESC";
$stmt = $_dbConn->query($qry);
$flightRows = $stmt->fetchAll();

$flightTotalPoints = [];
$qry = "SELECT flight, SUM(points)  FROM users WHERE flight=:flightName ORDER by SUM(points) DESC";
$stmt = $_dbConn->prepare($qry);
foreach ($flightRows as $flight) {
    $flightName = $flight["name"];
    $stmt->execute(["flightName" => $flightName]);
    $flightTotalPoints[$flightname] = $stmt->fetchAll();
}


<?php if (!count($flightRows)): ?>
    <div class="alert alert-warning alert-block"><h4 class="alert-heading">Notice</h4>No flights have been created yet
    </div>
<?php else: ?>
    <div class="span12">
        <div class="widget-box">
            <div class="widget-title"><span class="icon"><i class="icon-th"></i></span>
                <h5>Current Point Rankings</h5>
            </div>
            <div class="widget-content nopadding">
                <table class="table table-bordered">
                    <thead>
                    <tr>
                        <th>Flight Name</th>
                        <th>Points</th>
                    </tr>
                    </thead>
                    <tbody>
                    <?php foreach ($flightRows as $flight): ?>
                        <tr>
                            <td><?php echo $flight["name"]; ?></td>
                            <td><?php echo $flightTotalPoints[$flight["name"]]; ?></td>
                        </tr>
                    <?php endforeach; ?>
                    </tbody>
                </table>
            </div>
        </div>

Answer 1

你需要在sql中获取sum字段。您可以为它添加别名以获得更好的清晰度，并在 fetchAll 之后获取该值，然后将其简单地放入 $flightTotalPoints 数组

$flightTotalPoints = [];
$qry = "SELECT flight, SUM(points) as total_sum  FROM users WHERE flight=:flightName ORDER by SUM(points) DESC";
$stmt = $_dbConn->prepare($qry); 
foreach ($flightRows as $flight) {
    $flightName = $flight["name"];
    $stmt->execute(["flightName" => $flightName]);
    $flightTotalPoints[$flightName] = $stmt->fetchAll()[0]['total_sum'];
}