如何在查询中迭代数组的值？

Question

我想从下面的查询中迭代数组$code和$per_section的值。我应该使用什么循环方法？或者这是正确的做法吗？

   //total students
    $count_query=mysql_query("select total_students from subject where teacherid = '$get_id'");
    while($count_row=mysql_fetch_array($count_query)){ 
    $total += $count_row['total_students'];
    }

    $query = mysql_query( "Select code,total_students from subject where teacherid='$get_id'");
        while($result=mysql_fetch_assoc($query))){
         $section = ($result['total_students']/ $total)*30;
         $per_section[] = round($section, 0, PHP_ROUND_HALF_UP);
         $code[] = $result['code'];
        }

    //perform this statement for a number of times depending on the number of array of $code..
        $user_query=mysql_query("select * from result where subject_id ='$code' and faculty_id = '$get_id'LIMIT $per_section ")or die(mysql_error());                   
        while($row=mysql_fetch_assoc($user_query)){
            ...
         }

示例数据:主题表

code    total_students   teacherid

IT230         45         11-0009
IT213         44         11-0009
IT214         40         11-0009

结果表

subject__id    faculty_id

IT230          11-0009
IT213          11-0009
IT214          11-0009

这是$per_section

IT230只需要学生= (45/129)*30 = 10.46，或者我只需要11个结果。

IT213只需要学生= (44/129)*30 = 10.23，或者我只需要10个结果。

IT214只需要学生= (40/129)*30 = 9.3，或者我只需要9个结果。

只有10的IT213和9的IT214。因为我只有3条记录，所以只会显示这3条记录。

Answer 1

使用一个连接两个表的查询：

SELECT s.total_students, s.code, r.*
FROM students AS s
LEFT JOIN result AS r ON r.subject_id = s.code
WHERE s.teacher_id = '$get_id' AND r.faculty_id = '$get_id'
ORDER BY s.code

然后，您可以在单个循环中获取所有信息：

while ($result = mysqli_fetch_assoc($query)) {
    if (!isset($per_section[$result['code']]) {
        $per_section[$result['code']] = round($result['total_student']/$total*30, 0, PHP_ROUND_HALF_UP);
        $code[] = $result['code'];
    }
    if ($result['subject_id'] !== null) {
        // do stuff with columns from result table here
    }
}

Answer 2

正如您问题的注释中所提到的，通过PDO和JOIN这两个表使用预准备语句是更好的做法：

$dbh = new PDO('mysql:host=localhost;dbname=mydb', 'username', 'password');

$stmt = $dbh->prepare('
    SELECT result.*
    FROM result
    JOIN student ON result.subject_id = student.code
    WHERE student.id = :id
');

$stmt->execute(['id' => $id]);
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);