将SQL搜索分组，而不是在每个循环中循环--有更好的方法吗？
将SQL搜索分组，而不是在每个循环中循环--有更好的方法吗？
提问于 2018-02-23 12:22:58
我有一个学生数据库，我想根据每个学生的班级对他们进行分组。目前，我只能通过在每个组中循环来实现这一点。有没有更好的方法。我想要的结果是：
- 第一年
- 表1类
- 学生Y，瞳孔，瞳孔K
- 表1类
- Form Class 2
- Pupil G, Pupil A, Pupil Z
- 第二年
- 表3类
- 学生A，学生U，学生Q
- 表3类
- Form Class 4
- Pupil V, Pupil R, Pupil M
- 第三年
- 表5类
- 学生G，学生S，瞳孔D
- 表5类
- Form Class 6
- Pupil W, Pupil P, Pupil C
数据库如下所示：
+----+------+----------+-------+
|Year|Class |First Name|Surname|
+----+------+----------+-------+
|1 |Class3|Jenny |James |
|1 |Class1|John |Bruces |
|1 |Class1|Dirk |Diggler|
|1 |Class2|Michael |Johnson|
+----+------+----------+-------+到目前为止，我尝试过的编码是：
<?php
$pupil_details = $_SESSION['pupdetails'];
$selectclass = $connect->query("SELECT DISTINCT Year FROM `$pupil_details` ORDER BY class DESC");
while ($rows = $selectclass->fetch_array()) {
echo $rows["Year"];
$yrlvl = $rows['Year'];
?><br/><?php
$selectpup = $connect->query("SELECT DISTINCT class from `$pupil_details` where Year ='$yrlvl'");
while ($rowsd = $selectpup->fetch_array()) {
$yrlvlclass = $rowsd['class'];
echo $yrlvlclass;
?><br/><?php
$wholeSchool = mysqli_query($connect, "SELECT * from $pupil_details where class ='$yrlvlclass' ORDER BY surname asc");
while ($row = mysqli_fetch_array($wholeSchool)) {
echo $row["firstname"];
echo $row["surname"];
}
}
}
?>有人能提出更好的办法吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-02-23 13:53:20
我测试了这个，它起了作用：
$selectPupil = $connect->query("select * from `pupils` order by Year, Class, Surname");
$neededForFirstValue = 0;
while ($row = $selectPupil->fetch_array()) {
//set initial values for year
if($neededForFirstValue == 0) {
$previousYear = $row['Year']; //takes the value to compare
echo "Year " . $previousYear . "<br />"; //display first year
$neededForFirstValue = 1; //to never go into that if again
}
$currentYear = $row['Year']; //takes current year value
if($currentYear == $previousYear) { //check if year has changed, if not, keep going
//set initial values for class
if($neededForFirstValue == 1) {
$previousClass = $row['Class']; //takes the value to compare
echo "Class: " . $previousClass . "<br />"; //display first year
$neededForFirstValue = 2; //to never go into any if again
}
$currentClass = $row['Class'];
if($currentClass == $previousClass) {
echo $row['First Name'] . " " . $row['Surname'] . "<br />"; //display a pupil
} else {
echo "Class: " . $currentClass . "<br />"; //display current year
echo $row['First Name'] . " " . $row['Surname'] . "<br />"; //display first user in each class
}
} else {
echo "Year " . $currentYear . "<br />"; //display current year
}
$previousClass = $row['Class']; //remember the year to compare
$previousYear = $row['Year']; //remember the year to compare
}Stack Overflow用户
发布于 2018-02-23 12:29:57
您可以只按年份、类和姓氏进行单次查询就可以完成所有操作。
select *
from your_table
order by Year, class, surname 但是不要在php代码中使用var。您面临sql注入的风险。在您的示例中，在SQL中不允许在var中使用对象(表名)。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48947835
复制相关文章
相似问题
腾讯云开发者
