php - 如何将变量循环到sql调用中

问题描述

我想teacher_id从数据库中获取所有内容，然后teacher_id在另一个 sql 中循环每个。

我得到teacher_id这样的：

$teachersql = "SELECT DISTINCT teacher_name, teacher_id FROM `dancers` WHERE school = '$ownerSchool'";
        $teacherres = mysqli_query($con,$teachersql);

        while($chartData2=mysqli_fetch_array($teacherres)){
             $teacher_Id = $chartData2['teacher_id'];


            }
        ?>

我得到一个数组ID。现在我想使用该数组，然后使用每个数组执行另一个 sql，ID这样我就可以计算每个老师有多少初学者。

我得到的是

Teacher     |   Level
Liv         |   Beginner
Liv         |   Beginner
Liv         |   Novice
Tee         |   Open champ
Mike        |   Major
Liv         |   Beginner

但我想要的是为每个老师打一个sql调用，然后为那个老师计算初学者的数量。

所以如果我做$teachersql2 = "SELECT * FROM舞者WHERE teacher_id = '$teacher_Id'";

我希望它调用$teacher_id array. 我正在寻找这个结果：

Teacher     |   Count
Liv         |   3 Beginner, 1 Novice
Tee         |   1 Open champ
Mike        |   1 Major

这样，我可以将计数 (3) 放入一个变量中，并将 (1) 放入一个变量中以在 else where 中使用。

完整代码：

// GET TEACHERS


        $teachersql = "SELECT DISTINCT teacher_name, teacher_id FROM `dancers` WHERE school = '$ownerSchool'";
        $teacherres = mysqli_query($con,$teachersql);

        while($chartData2=mysqli_fetch_array($teacherres)){
             $teacher_Id = $chartData2['teacher_id'];
             $teacherNames = $chartData2['teacher_name'];
             $teacherNames = str_replace(' ', '', $teacherNames);
             $teacherNames = str_replace('.', '', $teacherNames);
             $var2 = 'data';
             $newVar2 = $var2 . $teacher_Id . $teacherNames;

            $teachersql2 = "SELECT * FROM `dancers` WHERE teacher_id = '$teacher_Id'";
            $teacherres2 = mysqli_query($con,$teachersql2);
            while($chartData3=mysqli_fetch_array($teacherres2)){
                $preB_percent = $chartData3['current_lvl'];
        ?>
        //CHART BY TEACHERS
        var <?php echo $newVar2; ?> = google.visualization.arrayToDataTable([
            ['<? $total_students; ?> Pre-Beginners', '<?php $lvl_beginner; ?> Beginners', '<?php $lvl_advanced; ?> Advanced Beginners', '<?php echo $lvl_novice; ?> Novices', '<?php echo $lvl_prizeWinner; ?> Prize Winners', '<?php $lvl_prelim; ?> Prelim Champions', '<?php echo $lvl_open; ?> Open Champions', '<?php echo $lvl_major; ?> Majors'],
            ['<?php echo $preB_percent; ?>', '<?php echo $beginner_percent; ?>', '<?php echo $advanced_percent; ?>', '<?php echo $novice_percent; ?>', '<?php echo $prizeWinner_percent; ?>', '<?php echo $prelim_percent; ?>', '<?php echo $open_percent; ?>', '<?php echo $major_percent; ?>']
        ]);

        <?php }} ?>

也许有更好的方法来做到这一点。但我真的需要帮助，我已经被困了好几天了。我希望我的问题有意义

标签： php

您可以使用group by下面的 SQL 将两个查询合并为一个：

SELECT teacher_name, current_lvl, count(teacher_id) as count
FROM dancers
WHERE school = '$ownerSchool'  
GROUP BY current_lvl, teacher_id
ORDER BY teacher_name ASC

产生的结果将类似于此模式：

Teacher     |  Level        | Count
Liv         |  Beginner     |   3
Liv         |  Novice       |   1
Tee         |  Open champ   |   1
Mike        |  major        |   1

您可以使用 php 代码合并结果以生成数据图表

$results = mysqli_query($con, $teacher_sql);

$last_teacher_name = "";
$current_index;
$chart_data = [];

while($row = mysqli_fetch_array($results)) {

    $teacher_name = $row["teacher_name"];
    $current_lvl = $row["current_lvl"];
    $total_dancers = $row["count"];

    if ($teacher_name != $last_teacher_name ) {
        $chart_data += ["teacher_name" => $teacher_name];
        $last_teacher_name = $teacher_name;
        $current_index = count($chart_data) - 1;
    }

    $chart_data[$current_index]["current_lvl"] = $current_lvl;
    $chart_data[$current_index]["count"] = $total_dancers;
}

注意： 重要的是要注意@Dharman 标点的sql注入

php - 如何将变量循环到sql调用中

问题描述

解决方案

推荐阅读