Reputation: 69
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
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