CODEWITHSUNDEEP
phpmysql
Saleh Refaai
Saleh Refaai

Reputation: 709

MYSQL select from table and count from another

I have subjects table contains subjects details and subject_student table contains the subjects selected by students. I want to select all the the subjects details selected by more than 2 students and also get the count of students for each subject selected by more than 2 students.

Subjects table

------------------------------
ID  | Name          | units 
------------------------------
1   | web           | 1
2   | programming   | 1
3   | java          | 1
4   | QA            | 1
------------------------------

student_subject Table

Subject table

------------------------------
student_id | subject_id | status 
------------------------------
1          | 1          | current
1          | 2          | current
2          | 1          | current
2          | 3          | current
3          | 1          | current
3          | 3          | current
4          | 1          | current
5          | 5          | current
------------------------------

so the result here must select the first row of subjects table and the 4 which is the count of students selected web subject Here is the Query:

$query= "
SELECT s.sub_ID
     , s.Name
     , s.units
     , count(st.subject_id) as cc 
  from subjects as s 
  LEFT 
  JOIN students_subject as st
    ON s.ID = st.subject_id 
 GROUP 
    BY st.subject_id  
Having count(st.subject_id)>2)
";

when I run the code it gives me this error: Notice: Trying to get property of non-object

here is the PHP code:

global $con,$users;
    $query= "SELECT s.sub_ID,s.Name, s.units,s.dept, count(st.subject_id)as cc from subjects as s LEFT JOIN students_subject as st
    ON s.ID=st.subject_id GROUP BY st.subject_id  Having count(st.subject_id)>2)";
    //$query="SELECT * FROM subjects;";
    $result=mysqli_query($con,$query);
    if ( $result->num_rows == 0 ) // User doesn't exist
        echo "Subjects doesn't exist!";
    else { echo "
        <tr>
            <th>Subjects ID</th>
            <th>Title</th>
            <th>Units</th>
            <th>Department</th>
            <th>Check</th>
        </tr>";

       $r=0;
      while($row = mysqli_fetch_array($result))
        {


            echo "<tr>";
            echo "<td>" . $row['sub_ID'] . "</td>";
            echo "<td>" . $row['Name'] . "</td>";
            echo "<td>" . $row['units'] . "</td>";
            echo "<td>" . $row['cc'] . "</td>";
        }

Upvotes: 0

Views: 89

Answers (1)

Sergey Menshov
Sergey Menshov

Reputation: 3906

Check your query for names of tables and columns Subjects(ID,Name,units), students_subject(student_id,subject_id,status):

SELECT
  sb.id AS sub_ID, -- !!!
  sb.Name,
  sb.units,
  COUNT(st.student_id) AS cc
FROM Subjects sb
JOIN students_subject st ON st.subject_id=sb.id
GROUP BY sb.id,sb.name,sb.units
HAVING COUNT(st.student_id)>2

You also can use print_r in while for test names which were returned with mysqli_fetch_array

while($row = mysqli_fetch_array($result))
{
  print_r($row);
  ...

Here doesn't need a bracket )

$query= "...  Having count(st.subject_id)>2)"; // <--

Try to delete it

$query= "...  Having count(st.subject_id)>2";

Upvotes: 1

Related Questions