phpmyadmin Question posted in
The official documentation can be found here.

MYSQL/PHP Echo Average of foreign key parameters – Phpmyadmin

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Solution: Thanks to @Dawson_Irvine, please see how they solved my problem here rather than read through the comments. Enjoy!

    <?php
$sql = "SELECT golfer_name, golfer_handicap, golfer_ghin FROM golfers WHERE trip_name_table_ID = '$userid'";
$result = $mysqli->query($sql);

$sql2 = "SELECT CAST(AVG(golfer_handicap) AS DECIMAL (3,1)) AS handicap FROM golfers WHERE trip_name_table_ID = '$userid'";
$result2 = $mysqli->query($sql2);
$total = mysqli_fetch_array($result2);

    In the $sql2 query I have made the query return the average as ‘handicap’ key in the array. Try $average[‘handicap’] or change AS handicap to AS golfer_handicap

    New echo that worked:

    echo "<td colspan = '2'> Average Handicap: " ."<b>". $average['handicap'] ."</b></td>";

  2. 0

    You would just do it like you did the first query; keeping your variables different as to not interfere with your loop.

    <?php
$sql = "SELECT golfer_name, golfer_handicap, golfer_ghin FROM golfers WHERE trip_name_table_ID = '$userid'";
$result = $mysqli->query($sql);

$sql2 = "SELECT CAST(AVG(golfer_handicap) AS DECIMAL (3,1)) AS handicap FROM golfers WHERE trip_name_table_ID = '$userid'";
$result2 = $mysqli->query($sql2);
$total = mysqli_fetch_array($result2);

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>";

    Then just call/echo $total["handicap"] wherever you want to display the average.

    **I have not tested the code.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search