Echo SUM of MySQL rows with foreach statement

Question

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>

Agam Banga · Accepted Answer · 2017-05-21 13:13:26Z

4

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

edited May 21, 2017 at 13:13

answered May 21, 2017 at 11:39

Agam Banga

2,6931 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Luke Bateson Over a year ago

Apart from my typo in the last $flightName (lowercase N, oops!), that worked perfectly! Many Thanks

Agam Banga Over a year ago

Ahh!! Didn't notice that. Updated that. Glad it works for you.

Jared Farrish Over a year ago

Using aliases is good (I do it regularly), however, $flight['SUM(points)'] will work.

Luke Bateson Over a year ago

I thought that at first, but don't think it will, because it's relying on a flightname string to SUM the correct points - happy to my corrected though, I'm not the expert!

Agam Banga Over a year ago

@JaredFarrish yes, i completely agree; i always prefer to use the alias so that it is clearly understandable.

|

Collectives™ on Stack Overflow

Echo SUM of MySQL rows with foreach statement

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related