PHP，在 SQL 中循环查询结果 [英] PHP, looping through query results in SQL

问题描述

我目前在尝试为我想要的结果创建循环时遇到问题.

I'm currently having trouble trying to create a loop for my desired outcome.

我目前正在创建一个学生记录卡，其中存储了不同学生(假学生)的大量数据.

I'm currently creating a student record card which stores numerous data of different students (fake students).

我创建了一个查询，它返回我需要的相关数据(参见图一，phpmyadmin)

I have created a query which returns the relevant data I need (see picture one, phpmyadmin)

SELECT mods.mid, mtitle, credits, enrl.ayr 
FROM stud, smod, mods, enrl 
WHERE stud.sid = '154279' AND stud.sid = smod.sid 
AND smod.mid = mods.mid AND stud.sid = enrl.sid 
ORDER BY `enrl`.`ayr`  DESC

从结果可以看出，有以下属性:中
标题
学分
艾尔

As you can see by the results, there are attributes: mid
mtitle
credits
ayr

我已按 ayr 降序订购.我正在尝试创建一个循环，该循环将运行此查询的返回值并打印出每一行，直到当前年份结束.几乎将所有行与同一年分组，例如'2001/02' 进入一个子表，然后我可以命名和打印.

I have ordered by ayr in decending order. I am trying to make a loop that will run through the return on this query and print out each row until the end of whatever the current year is. Almost grouping all rows with the same year e.g. '2001/02' into a sub table which I can then name and print.

正如您在我的学生记录页面的第二张图片中看到的那样，我需要能够打印这一年的所有记录，然后为下一个现有年份创建一个新标题并打印所有包含该年份的行.

As you can see by my second picture of the student records page, I need to be able to print all records for the one year, then create a new header for the next existing year and print all containing rows for that.

{编辑}PHP代码:

$query = "SELECT mods.mid, mtitle, credits, enrl.ayr 
            FROM stud, smod, mods, enrl 
            WHERE stud.sid = '154279' AND stud.sid = smod.sid AND smod.mid = mods.mid AND stud.sid = enrl.sid 
            ORDER BY enrl.ayr  DESC
        ";


$scap = ''; 
$curYear = $row['ayr'];  
if($result = $link->query($query)) {
        while ($row = $result->fetch_assoc() && $row['ayr'] == $curYear) { 
            $scap .= "<table id=\"test\" style=\"width:100%\"> 
                        <tr> 
                            <td> " . $row['mid'] . " </td> <td> " . $row['mtitle'] . "</td> <td> " . $row['credits'] . " <td> " . $row['ayr'] . "</td>
                        </tr> 
                </table>"; 
        }$result->free(); 
    } 

提前致谢.

推荐答案

假设您为整个页面最多提交一个查询.就像我在评论中说的

Let's say you commit to one query max for the whole page. Like I said in comments

我会有一个变量，称之为 $curYear.从一些垃圾开始细绳.在你的循环中，如果 cur year 的东西不同于$curYear，在您的输出中创建一个新段，但不考虑更新$curYear 变量

I would have a variable, call it $curYear. Start it out as some junk string. In your loop, if the cur year thing is different than $curYear, create a new segment in your output but regardless update $curYear variable

这并不意味着干扰您现有的源代码(那么多).它只是一个哨兵，提醒您一年的变化(无论是年/学期).

That was not meant to interfere with your existing source code (that much). It is just a sentinel to alert you to a year change (year/term whatever).

所以它开始是一些垃圾值，比如797fsdf*"

So it starts as some junk value, like "797fsdf*"

现在在您的时间里，请记住，您拥有所有年份的所有年份.

Now inside your while, remember, you have ALL the years coming in from that result set for all years.

做我在上面那个粉红色块中所说的比较变量 $curYear 和

Do what I said in that pink block above comparing that variable $curYear to

$row['ayr']

当这两个值不同时，是时候做任何你想做的 HTML 处理(创建一个新的 html 表，一个新的 div，谁在乎).让我们称之为分离事情.

When those two values are different, time to do whatever HTML treatment you want (creating a new html table, a new div, who cares). Let's call this the separation thing.

无论如何，在输出行后，请确保已将 $curYear 设置为 $row['ayr'].为什么这很重要?因为下一个循环你想知道你是否需要做分离的事情.

Regardless, after you output the row, make sure you have set $curYear to $row['ayr']. Why is that important? Because the next loop you want to know if you need to do the separation thing.

棘手的部分是如果你在做 html 表格，如果你不是第一年，你必须关闭上一个表格(前一年)

The tricky part is if you are doing html tables, you have to close out the previous table (prior year) if you are not on your first year

这篇关于PHP，在 SQL 中循环查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！