我的科目表包含科目详细信息，subject_student 表包含学生选择的科目。我想选择超过 2 名学生选择的所有科目详细信息，并获得超过 2 名学生选择的每个科目的学生人数。
科目表
------------------------------
ID | Name | units
------------------------------
1 | web | 1
2 | programming | 1
3 | java | 1
4 | QA | 1
------------------------------
student_subject 表
主题表
------------------------------
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
------------------------------
所以这里的结果必须选择科目表的第一行和选择网络科目的学生人数 4 这是查询：
$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)
";
当我运行代码时，它给了我这个错误：注意：试图获取非对象的属性
这是PHP代码：
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>";
}
检查您的表和列名称的查询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
您还可以将print_rinwhile用于返回的测试名称mysqli_fetch_array
while($row = mysqli_fetch_array($result))
{
print_r($row);
...
这里不需要支架 )
$query= "... Having count(st.subject_id)>2)"; // <--
尝试删除它
$query= "... Having count(st.subject_id)>2";
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句