multiple-foreach循环与mysql连接

我不确定我的数据库设计和编码结构是有效的还是不能得到预期的结果。
例如，我得去接学生 smith 详细资料，他的一般资料和他在每一个科目每一个类别的每一个学期取得的成绩。
下面是我的数据库结构
学生

id  |   Roll    |   name    |   class
=====================================
1   |   1       |   smith   |   7

主题

id  |   title
=============
1   |   Science
2   |   Math

考试

id  |   title
=================
1   |   First Term 
2   |   Second Term

标志

id  |   std_id  |   sub_id  |   th  |   pr  |   exm_id
======================================================
1   |   1       |   1       |   60  |   20  |   1
2   |   1       |   2       |   55  |   18  |   1
3   |   1       |   1       |   70  |   23  |   2
4   |   1       |   2       |   61  |   19  |   2

现在，我正在努力获取结果

Name : Smith
class:  7
Progress Report
    First Term
        Science
            Th:60 | PR:20
        Math
            Th:55 | PR:18

    Second Term
        Science
            Th:70 | PR:23
        Math
            Th:61 | PR:19

但是，下面是我得到的输出，我不能以考试方式获取结果，所以所有的考试都共享所有的分数

Name : Smith
class:  7
Progress Report
    First Term
        Science
            Th:60 | PR:20
        Math
            Th:55 | PR:18
        Science
            Th:70 | PR:23
        Math
            Th:61 | PR:19

    Second Term
        Science
            Th:60 | PR:20
        Math
            Th:55 | PR:18
        Science
            Th:70 | PR:23
        Math
            Th:61 | PR:19

我试过了 group.by 至 exam.id ，虽然它取得了预期的考试成绩，但它重复了同一个学生

Name : Smith
class:  7
Progress Report
    First Term
        Science
            Th:60 | PR:20
        Math
            Th:55 | PR:18

Name : Smith
class:  7
Progress Report
    Second Term
        Science
            Th:70 | PR:23
        Math
            Th:61 | PR:19

下面是mysql和php代码

$result=$con->prepare(
    "SELECT
        student.id, student.rid, student.name AS name, student.class,

        class.title AS class, 

        GROUP_CONCAT(DISTINCT exam.title) AS exam,
        GROUP_CONCAT(subject.title, '<br/> Th - ', mark.th, ' | PR - ', mark.pr SEPARATOR ',') AS mark

        FROM student
        JOIN class ON class.id = student.class 
        JOIN mark ON mark.std_id = student.id
        JOIN exam ON exam.id = mark.exm_id
        JOIN subject ON subject.id = mark.sub_id

        WHERE student.id=:id
        GROUP BY student.id;" //If exam.id is added here, it works but repeats student
) or die($con->error);

$result->bindParam(':id',$_POST['std']);
$result->execute();
while($row=$result->fetch(PDO::FETCH_ASSOC)){
    $name=$row['name'];
    $class=$row['class'];
    $exm_array = explode(',',$row['exam']);
    $mrk_array = explode(',',$row['mark']); 
    echo "
    Name: $name<br/>
    Class: $class<br/>
    Progress Report<br/>";

    // I think there are other fine alternative way instead of foreach

    foreach(array_values($exm_array) as $i => $exam){
        echo "<span class='col100'>".$exam."<br/>Mark:</span>";
        foreach(array_values($mrk_array) as $i => $mark){
            echo "<span class='col100'> ".$mark."</span>";
        }
    }
}

原始输出

这将是我最好的猜测如何实现它。
将标记加载到php数组中
用修改过的查询遍历学生（只有一个） GROUP_CONCAT() )
显示学生姓名
从预加载的数据中为student it获取正确的术语数组
循环数组以显示每个术语
首先查询，加载所有标记：

SELECT
    student.id,
    exam.title AS exam,
    GROUP_CONCAT(subject.title, '<br/> Th - ', mark.th, ' | PR - ', mark.pr SEPARATOR ',') AS mark
FROM student
    JOIN mark ON mark.std_id = student.id
    JOIN exam ON exam.id = mark.exm_id
    JOIN subject ON subject.id = mark.sub_id
GROUP BY
    student.id,
    exam.title

并将其存储在php数组中 $marks_array 然后通过学生循环：

SELECT
    student.id,
    student.rid,
    student.name AS 'name',
    student.class,
    class.title AS 'class_title'
FROM student
    JOIN class ON class.id = student.class

在循环中再次调用正确的记录： $marks_array[<student_id>] -这将是一个有两个值的数组（每个检查一个值）
试一试，让我知道是否有意义。。

multiple-foreach循环与mysql连接

1条答案

相关问题

热门标签

最新问答