在php中迭代mysql结果集时需要帮助

Question

我有两张桌子：

Institute:
-----------
id       name         city
-----    -------      -------
1        Name 1       London    
2        Name 2       Leeds
3        Name 3       Edinburgh

Course:
--------
id       i_id     name                      stream
-----    -----    --------------------      -------
1        1        Computer Engineering      Engineering 
2        1        Chemical Engineering      Engineering
3        2        Electronics Engineering   Engineering 
4        3        Mechanical Engineering    Engineering

我需要在我的结果集中提供以下信息：

1. 提供工程课程的学院(名称1，名称2，名称3)。
2. 学院提供的所有课程。

现在，这是我要实现这个结果集的查询：

SELECT institute.id, institute.name AS i_name, institute.city, course.name AS c_name
FROM institute, course
WHERE institute.id = course.i_id
AND course.stream = 'Engineering'
ORDER BY institute.id

此查询返回4条记录：

id      i_name      city           c_name
---     -------     ---------      ----------------
1       Name 1      London         Computer Engineering
1       Name 1      London         Chemical Engineering
2       Name 2      Leeds          Electronics Engineering
3       Name 3      Edinburgh      Mechanical Engineering

如何在此结果集上迭代，以便在网页上获得以下数据列表：

 Found 3 Institutes
 ----------------------
 1) Name 1, London
    Courses Offered:-
    => Computer Engineering
    => Chemical Engineering

 2) Name 2, Leeds
    Courses Offered:-
    => Electronics Engineering

 3) Name 3, Edinburgh
    Courses Offered:-
    => Mechanical Engineering

已添加

我有分页设置在我的网页上。现在假设我想显示每页2条记录，我将使用LIMIT 0,2运行上面的查询。显然，我查询中的前2条记录给了我相同的研究所，所以在这种情况下，我也需要第三研究所。

我能做这样一个动态查询吗？

Answer 1

像这样的事情应该有效：

$currentInstitute = null;
$i = 1;
while($row = mysql_fetch_object($query)) {
  if($row->i_name != $currentInstitute)
    echo $i++.") $row->i_name, $row->city\n   Courses offered:-\n";
  $currentInstitute = $row->i_name;
  echo "    => $row->c_name\n"; 
}