CODEWITHSUNDEEP
phpmysqlpdosum
Luke Bateson
Luke Bateson

Reputation: 69

Echo SUM of MySQL rows with foreach statement

I'm a little stuck on how to do this; I'm new to all this PDO stuff and not quite used to it yet. Basically, I have two tables, flights and users. I'd like to find the names of all the flights, and then look at the users table, and SUM the 'points' column where flight equals one of the flight names. This is what I have so far and I just can't progress any further

<?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>

Upvotes: 1

Views: 94

Answers (1)

Agam Banga
Agam Banga

Reputation: 2693

You need to fetch the sum field in sql. You can add the alias to it for better clarity & get that value after fetchAll & then simply put it to the $flightTotalPoints array

$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'];
}

Upvotes: 4

Related Questions