I’m trying to figure out how to have 2 SQL statements back to back, which creates a table with the info from the first SQL statement, and the last cell be the average of one of the columns from said table.
I can display the info needed to make the table. No problem. It’s this:
$sql = "SELECT golfer_name, golfer_handicap, golfer_ghin FROM golfers WHERE trip_name_table_ID = '$userid'";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($rowitem = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td style='text-align: left'>" . $rowitem['golfer_name'] . "</td>";
echo "<td>" . $rowitem['golfer_handicap'] . "</td>";
echo "<td>" . $rowitem['golfer_ghin'] . "</td>";
echo "</tr>";
I can get the first colum to total the number of golfers, no problem:
echo "<tr>";
echo "<td>Total Golfers: $result->num_rows </td>";
Now the trouble is trying to get the average of the handicaps in the last cell of that column. I created a 2nd SQL directly below the 1st one, but changed the variables so that they wouldn’t mess with the original table data — and inside my PHPmyAdmin SQL section, this statement returns exactly what I’m looking for:
$sql2 = "SELECT CAST(AVG(golfer_handicap) AS DECIMAL (3,1)) FROM golfers WHERE trip_name_table_ID = '$userid'";
But for the absolute life of me, I cannot figure out how to echo
that single piece of data.
2
Answers
Solution: Thanks to @Dawson_Irvine, please see how they solved my problem here rather than read through the comments. Enjoy!
In the
$sql2
query I have made the query return the average as‘handicap’
key in the array. Try$average[‘handicap’]
or changeAS handicap
toAS golfer_handicap
New
echo
that worked:You would just do it like you did the first query; keeping your variables different as to not interfere with your loop.
Then just call/echo
$total["handicap"]
wherever you want to display the average.**I have not tested the code.