while loop and join output in mysql php

Question

I have a code that runs through database and outputs everything to a php page.

$avg = mysql_query("SELECT subject, gradeone, gradetwo, gradethree, ((gradeone + gradetwo + gradethree) / 3) as average FROM grades");


$q = mysql_query("SELECT * FROM newstudent AS n JOIN grades AS g ON n.id = g.id ORDER BY n.id") or die (mysql_error());

$last_student = null;

while ($row = mysql_fetch_assoc($q))
    {
        if ($row['id'] !== $last_student)
        {
            $last_student = $row['id'];
            echo "Student ID: ".$row['id']."<br/>";
            echo "First Name: ".$row['firstname']."<br/>";
            echo "Last Name: ".$row['lastname']."<br/>";
            echo "Email: ".$row['email']."<br/>";
            echo "<br/>";
        }
            print "<table id=reporttable>"; 

            print "<tr id=toprow> <td>subject</td> <td>gradeone</td> <td>gradetwo</td> <td>gradethree</td> <td>average</td></tr>"; 

            print   "<tr>";          

                print   " <td>";    
                    print   $row["subject"];
                print   "</td>"  ;

                print   " <td>";    
                    print   $row["gradeone"];
                print   "</td>"  ;

                print   " <td>";    
                    print   $row["gradetwo"];
                print   "</td>"  ;

                print   " <td>";    
                    print   $row["gradethree"];
                print   "</td>"  ;

                while ($r = mysql_fetch_array($avg))
                {
                    print   " <td>";    
                        print   $r['average'];
                    print   "</td>"  ;
                }

            print   " </tr>";   
            print "</table>";             

}?>

The desired outcome should look like this

Desired outcome the outcome from the following code is great, but there is only 1 minor issue.

the 2nd while loop is suppose to be calculating the average and outputting each record at a new row. instead it does this:

Wrong outcome

anyone knows a way to make it that each one of those average grades goes along with each row for students?

This will never work. You'll only get results for the $avg query ONCE. After it's results have been consumed and you go on to the next iteration of the outer $q query, there will be no more results in the $avg result set and that inner loop will never execute again. You can hack-fix the code by moving the $avg = mysql_query(...) query call INSIDE the main loop, but then you'll be running that query once for every iteration of the loop, which is highly inefficient. — Marc B
– Marc B, Commented Nov 1, 2013 at 14:42
Show us database tables please, so we could try to provide you with a single query to get all the information. — Bandydan
– Bandydan, Commented Nov 1, 2013 at 14:46
@Bandydan There are 2 different tables involved in this: Table 1 is named - newstudent, table 2 - grades: table 1 has the following attributes: id(primary key in both tables), firstname, lastname, email. The 2nd table has attributes - id, subject, gradeone, gradetwo, gradethree — Tim Valishin
– Tim Valishin, Commented Nov 1, 2013 at 14:47
Tell am I right or not: grades id is the same as id for newstudent? I mean, if I have newstudent with id=1, then this student has grades under id=1? — Bandydan
– Bandydan, Commented Nov 1, 2013 at 14:51

Bandydan · Accepted Answer · 2013-11-01 15:03:23Z

2

$q = mysql_query("
    SELECT
        n.id,
        n.firstname, 
        n.lastname,
        n.email,
        g.gradeone, 
        g.gradetwo, 
        g.gradethree, 
        ((g.gradeone + g.gradetwo + g.gradethree) / 3) AS average
    FROM
        newstudent n JOIN grades g USING (id)
    ORDER BY
        n.id
") or die (mysql_error());

Try to use this query and then output the results in one single loop - remove that

while ($r = mysql_fetch_array($avg))

with its braces.

Leave something like this:

....
print "<td>$row['gradetwo']</td>";
print "<td>$row['gradethree']</td>";

print "<td>$row['average']</td>";
....

edited Nov 1, 2013 at 15:03

answered Nov 1, 2013 at 14:56

Bandydan

6061 gold badge8 silver badges24 bronze badges

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

2 Comments

Tim Valishin Over a year ago

the only 1 question, the average comes out in the way of "99.9999" with 4 decimals after the point, is there any way to round it up? or only show 1 decimal after point? @bandydan

Bandydan Over a year ago

dev.mysql.com/doc/refman/5.0/en/… - check this link and try examples, you will get what you need.

Collectives™ on Stack Overflow

while loop and join output in mysql php

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related