php - 在 PHP 中循环遍历 MySQL 行

Question

我有一个包含 3 个表的 MySQL 数据库 - events、venues、instance。 events 包含有关事件的信息，例如事件 ID、名称和描述。 venues 包含 field ID 和 field 名称。 instance 包含事件 ID、地点 ID 和日期。

我执行连接并将结果加载到一个数组中，这样我就可以使用 PHP 在页面上显示事件信息:

$eid = $_GET['event_id'];

$q = "SELECT e.event_name, e.event_description, i.venue_id, i.instance_avail, DATE_FORMAT( i.instance_date,  '%M %D, %Y' ) AS DATE
FROM events AS e
INNER JOIN instance AS i ON e.event_id = i.event_id
WHERE e.event_id = $eid";

$r = @mysqli_query ($dbc,$q);
$row = mysqli_fetch_array($r, MYSQLI_ASSOC);

名称和描述只在页面上出现一次，因此很容易显示:

echo $row['event_name']

但是，我还需要做的是显示按地点排序的日期列表，例如:

**New York**

18th December, 2013
19th January, 2014
21st February, 2013

**Washington, DC**

18th December, 2013
22nd December, 2013
12th March, 2014

我可以使用现有查询来执行此操作，还是编写新查询会更简单？如果是这样，如何？另外，我如何循环显示 field 、日期、下一个 field 等？

Answer 1

field 变化时把值(value)放出来。像这样:-

<?php

$eid = $_GET['event_id'];

$q = "SELECT e.event_name, e.event_description, i.venue_id, i.venue_name, i.instance_avail, DATE_FORMAT( i.instance_date,  '%M %D, %Y' ) AS DATE
FROM events AS e
INNER JOIN instance AS i ON e.event_id = i.event_id
WHERE e.event_id = $eid
ORDER BY i.venue_id, i.instance_date";
$cur_venue = 0;
$r = @mysqli_query ($dbc,$q);
if ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
{
    $cur_venue = $row['venue_id'];
    echo $row['event_name']."<br />";
    echo $row['venue_name']."<br />";
    echo $row['DATE']."<br />";
    while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
    {
        if ($cur_venue != $row['venue_id'])
        {
            $cur_venue = $row['venue_id'];
            echo $row['venue_name']."<br />";
        }
        echo $row['DATE']."<br />";
    }
}

?>

可能使用 do...while 循环清理它:-

<?php

$eid = $_GET['event_id'];

$q = "SELECT e.event_name, e.event_description, i.venue_id, i.venue_name, i.instance_avail, DATE_FORMAT( i.instance_date,  '%M %D, %Y' ) AS DATE
FROM events AS e
INNER JOIN instance AS i ON e.event_id = i.event_id
WHERE e.event_id = $eid
ORDER BY i.venue_id, i.instance_date";
$cur_venue = 0;
$r = @mysqli_query ($dbc,$q);
if ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
{
    echo $row['event_name']."<br />";

    do
    {
        if ($cur_venue != $row['venue_id'])
        {
            $cur_venue = $row['venue_id'];
            echo $row['venue_name']."<br />";
        }
        echo $row['DATE']."<br />";
    } while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC));
}

?>

编辑 - 对测试数据进行了测试，以下内容似乎有效。

<?php

#------ database connections -------
define('MYSQLHOST','localhost');
define('MYSQLUSER','');
define('MYSQLPASS','');
define('MYSQLDATABASE','test');
define('MYSQLDATABASE_INTERNAL', '');// sf orders database

$dbc = mysqli_connect(MYSQLHOST, MYSQLUSER, MYSQLPASS, MYSQLDATABASE);


$eid = $_GET['course_id'];

$q = "SELECT e.course_name, e.course_description, i.venue_id, v.venue_name, i.instance_avail, DATE_FORMAT( i.instance_date,  '%M %D, %Y' ) AS DATE
FROM courses AS e
INNER JOIN instance AS i ON e.course_id = i.event_id
INNER JOIN venue AS v ON i.venue_id = v.venue_id
WHERE e.course_id = $eid
ORDER BY i.venue_id, i.instance_date";
$cur_venue = 0;
$r = @mysqli_query ($dbc,$q) or die(mysqli_error($dbc));
if ($row = mysqli_fetch_array($r, MYSQLI_ASSOC))
{
    echo $row['course_name']."<br />";

    do
    {
        if ($cur_venue != $row['venue_id'])
        {
            $cur_venue = $row['venue_id'];
            echo $row['venue_name']."<br />";
        }
        echo $row['DATE']."<br />";
    } while ($row = mysqli_fetch_array($r, MYSQLI_ASSOC));
}

?>