0

I have two parts to this question:

This is my code: 

$sql = "SELECT * FROM student_unique WHERE grade_level >='10' ORDER BY grade_level, last_name";
$result = $conn->query($sql);

if ($result->num_rows > 0) {

// output data of each row
while($row = $result->fetch_assoc()) {
   $temp = $row['student_number'];

    echo "<tr><td>";
    echo $row['first_name']." ".$row['last_name'];
    echo "</td>";

    $sql2 = "SELECT AVG(percent) AS number, COUNT(course_name) AS total FROM students_marks WHERE student_number='$temp'";
        $result2 = $conn->query($sql2);

        if ($result2->num_rows > 0) {
           echo "<td>";
            // output data of each row
            while($row2 = $result2->fetch_assoc()) {

                echo $row2['number']. " ". $row2['total'];


            }
          echo "</td>";
        } else {
            echo "<td> No Marks </td>";
        }


     // end of while loop    
    }

} else {
    echo "0 results";
}
$conn->close();

This will as you can see print an average and the number of courses that a student has which works. When I change the $sql2 SELECT to: 

$sql2 = "SELECT AVG(percent) AS number, COUNT(course_name) AS total, course_name FROM students_marks WHERE student_number='$temp'";
    $result2 = $conn->query($sql2);

The query fails and I get "no marks".

I am sure that I have seen some queries that do something similar to this but I am unsure why adding another column to the select function causes no rows to be displayed.

The second question involves adding a modifier at the end of the SELECT:

If I add WHERE clause to this it also returns "No marks". 

 $sql2 = "SELECT AVG(percent) AS number, COUNT(course_name) AS total FROM students_marks WHERE student_number='$temp' AND total>'7'";

Why would this not be working in the case of adding a column name or if I add a WHERE clause involving the total?

I would be greatly appreciative if someone could explain the "why" behind this as I have looked for the past couple of days for an explanation on what might be going on behind the code.

I will include the tables I am using, they might be part of the issue.

Tables: 

table: students_marks
id  stud_id gr  student_#   year   course  term  mark per

415 31703   9   3100992316  2500    PHYE9   F1  78  78
416 31703   9   3100992316  2500    FR9     F1  50  50
417 31703   9   3100992316  2500    ENG9    F1  55  55 

student_unique
id   student_#   First   Last   gr 
50  3100992316  Amanda  B       9

Thanks for the support

Vincent
  • 83
  • 1
  • 1
  • 9
  • You shouldn't be running nested queries like that. You could trivially do this with a single `join`ed query. – Marc B Oct 17 '16 at 21:38
  • If I did a join and that is OK, I end up with the same problem I am guessing with the second $sql2. – Vincent Oct 17 '16 at 21:44
  • Check your MySQL error message. If you have the `ONLY_FULL_GROUP_BY` SQL mode set, the query won't work. You need to use an aggregate function to select a specific course from the group, e.g. `MAX(course_name)`. – Barmar Oct 17 '16 at 22:01
  • For the second part about `AND total > 7`, you need to do that in `HAVING`, not `WHERE`. See http://stackoverflow.com/questions/9253244/sql-having-vs-where – Barmar Oct 17 '16 at 22:02

0 Answers0