>我在MySQL中有以下两个表结构，它们记录了电话会议的详细信息以及加入会议的参与者：
表：会议：
conference_sid, date_created, date_completed, RecordURL, PIN
*date_created 和 *date_completed 是时间戳
表：参会人员：
conference_sid, call_sid, call_from, name_recording
我想输出一个简单的表，该表将每个conference_sid的以下结果显示为单独的行：
<table>
<thead>
<th>Date</th>
<th>Duration</th>
<th>Participants</th>
<th>Recording</th>
</thead>
<tbody>
<tr id="conference_sid">
<td>date_created</td>
<td>duration: [date_completed - date_created in h/mm/ss]</td>
<td>
<li><a href="name_recording">call_from</a> [for all participants in that conference_sid]
<li>call_from...
</td>
<td>
<a href="RecordURL">Call recording</a>
</td>
</tr>
<tr id="conference_sid">
...
</tr>
</tbody>
</table>
我只希望此表显示与用户会话具有相同PIN的会议的相关结果：：get('PIN'(
您可以使用
GROUP_CONCAT合并参与者
SELECT
conf.conference_sid,
date_created,
TIMEDIFF(date_completed, date_created) AS duration,
conf.RecordURL,
conf.PIN,
GROUP_CONCAT(pid SEPARATOR ",") AS pid,
GROUP_CONCAT(call_sid SEPARATOR ",") AS call_sid,
GROUP_CONCAT(call_from SEPARATOR ",") AS call_from,
GROUP_CONCAT(name_recording SEPARATOR ",") AS name_recording
FROM
conference conf
LEFT OUTER JOIN
participants p ON p.conference_sid = conf.conference_sid
WHERE
conf.PIN = 123
GROUP BY conf.conference_sid
请参阅 SQLFIDDLE 和 MySQL 文档，了解 TIMEDIFF。
现在应用程序逻辑将是
<?php
$pin = 123;
$db = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$stmt = $db->prepare(
'SELECT
conf.conference_sid,
date_created,
timediff(date_completed, date_created) AS duration,
conf.RecordURL,
conf.PIN,
GROUP_CONCAT(pid SEPARATOR ",") AS pid,
GROUP_CONCAT(call_sid SEPARATOR ",") AS call_sid,
GROUP_CONCAT(call_from SEPARATOR ",") AS call_from,
GROUP_CONCAT(name_recording SEPARATOR ",") AS name_recording
FROM
conference conf
LEFT OUTER JOIN
participants p ON p.conference_sid = conf.conference_sid
WHERE
conf.PIN = :pin
GROUP BY conf.conference_sid');
$stmt->bindParam(':pin', $pin);
?>
<table border="1">
<thead>
<th>Date</th>
<th>Duration</th>
<th>Participants</th>
<th>Recording</th>
</thead>
<tbody>
<?php
$stmt->execute();
while ($row = $stmt->fetch()) {
?>
<tr>
<td><?php echo $row['date_created']; ?></td>
<td><?php echo $row['duration']; ?></td>
<td>
<table border="1">
<thead>
<th>call_sid</th>
<th>call_from</th>
<th>name_recording</th>
</thead>
<tbody>
<?php
$length = count(explode(',', $row['pid']));
$call_sid = explode(',', $row['call_sid']);
$call_from = explode(',', $row['call_from']);
$name_recording = explode(',', $row['name_recording']);
for ($i=0; $i < $length; $i++) {
?>
<tr>
<td> <?php echo $call_sid[$i]; ?> </td>
<td> <?php echo $call_from[$i]; ?></td>
<td> <?php echo $name_recording[$i]; ?> </td>
<tr>
<?php
}
?>
</tbody>
</table>
</td>
<td>
<a href="<?php echo $row['RecordURL']; ?>">
Call recording</a>
</td>
</tr>
<?php
}
?>
</tbody>
您将获得带有逗号(，( 分隔值的结果集，分别是 pid、call_sid、call_from 和 name_recording。您可以使用爆炸将此字符串转换为数组。
array explode ( string $delimiter , string $string [, int $limit ] )
返回一个字符串数组，每个字符串都是字符串的子字符串 通过在字符串分隔符形成的边界上拆分它来形成。
我不会做PHP部分，因为我对PHP不是那么了解，但这里是SQL：
SELECT *
FROM `conference`, `participants`
WHERE `conference`.PIN = $PIN AND
`participants`.conference_sid = `conference`.conference_sid
这将返回包含来自conference的信息的行，以及这些conferences的participants，连接成一行。
以下查询将为您提供需要显示的信息：
SELECT c.conference_sid
, c.date_created
, timediff(c.date_completed, c.date_created) AS duration
, p.call_from
, p.name_recording
, c.RecordURL
FROM conference c
JOIN participants p
ON c.conference_sid = p.conference_sid
WHERE c.PIN = :PIN
ORDER BY c.conference_sid
您需要使用嵌套循环处理结果。每次conference_sid更改时，外部循环都应前进。内部循环将显示该会议的参与者列表的每个元素。
这将是我对它的看法，它使用 2 个单独的查询来保持数据有点分离。为了简洁起见，我使用 fetchAll((，但这可能会有性能问题，幸运的是这可以适应。我没有进行任何错误检查，如果您想要它或您有问题，请询问
<?php
// assume $db is a PDO connection to the database
/* @var $db PDO */
$q = 'SELECT conference_sid, date_created, date_completed, RecordURL, PIN'
.' FROM conference';
// we need these
$conferences = $db->query($q)->fetchAll(PDO::FETCH_CLASS,'stdClass');
// let's group them as CSV, and concatenate the contents with ":"
$q = 'SELECT conference_sid,GROUP_CONCAT(CONCAT_WS(":",call_from,name_recording)) AS parts '
.' FROM participants GROUP BY conference_sid';
$conf_parts = array();
foreach ($db->query($q)->fetchAll(PDO::FETCH_CLASS,'stdClass') as $parts) {
// save the participants as an array, their data is still joined though
$conf_parts[$parts->conference_sid] = explode(',',$parts->parts);
// their contents will be exploded later
}
?>
<table>
<thead><th>Date</th><th>Duration</th><th>Participants</th><th>Recording</th></thead>
<tbody><?php foreach ($conferences as $conference) {
$csid = $conference->conference_sid;
// http://stackoverflow.com/questions/3108591/calculate-number-of-hours-between-2-dates-in-php
// Create two new DateTime-objects...
$date1 = new DateTime($conference->date_completed);
$date2 = new DateTime($conference->date_created);
// The diff-methods returns a new DateInterval-object...
$diff = $date2->diff($date1);
?><tr id="<?php echo $csid; ?>">
<td><?php echo $conference->date_created; ?></td>
<td><?php echo $diff->format('H/i/s'); ?></td>
<td>
<ul><?php foreach ($conf_parts[$csid] as $participant) {
// we have each participant for this conference call
list ($call_from, $name_recording) = explode($participant,':');
// and now we have the required data from each participant
?><li><a href="<?php echo $name_recording ?>"><?php echo $call_from; ?></a></li><?php
} ?></ul>
</td>
<td>
<a href="<?php echo $conference->RecordURL; ?>">Call recording</a>
</td>
</tr><?php
} ?></tbody>
</table>
在这个特定的 contex 中，我更喜欢使用两个单独的查询。以下是我会怎么做：
<?php
try {
$db = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
echo 'Could not connect to db';
exit;
}
$stmt_conferences = $db->prepare(
'SELECT
date_created,
timediff(date_completed, date_created) AS duration,
RecordURL,
conference_sid
FROM
conference
WHERE
PIN=:pin');
$stmt_conferences->bindParam(':pin', $pin);
$stmt_participants = $db->prepare(
'SELECT
name_recording,
call_from
FROM
participants
WHERE
conference_sid=:confsid');
$stmt_participants->bindParam(':confsid', $confsid);
?>
<table>
<thead>
<th>Date</th>
<th>Duration</th>
<th>Participants</th>
<th>Recording</th>
</thead>
<tbody>
<?php
$pin = 1; /* get your PIN here */
$stmt_conferences->execute();
while ($row = $stmt_conferences->fetch()) {
?>
<tr>
<td><?php echo htmlspecialchars($row['date_created'], ENT_QUOTES); ?></td>
<td><?php echo htmlspecialchars($row['duration'], ENT_QUOTES); ?></td>
<td>
<?php
$confsid = $row['conference_sid'];
$stmt_participants->execute();
while ($participant = $stmt_participants->fetch()) {
?>
<li><a href="<?php echo htmlspecialchars($participant['name_recording'], ENT_QUOTES); ?>">
<?php echo htmlspecialchars($participant['call_from'], ENT_QUOTES); ?>
</a>
<?php
}
?>
</td>
<td>
<a href="<?php echo htmlspecialchars($row['RecordURL'], ENT_QUOTES); ?>">
Call recording</a>
</td>
</tr>
<?php
}
?>
</tbody>
请注意，您必须添加一些代码来处理错误并正确转义您回显的所有数据(您真的可以信任您的数据库吗？此外，元素 ID 在整个文档中应该是唯一的，您可以在页面中只有一个id="conference_sid"。请改用类。
编辑
如果你真的可以信任你的数据库，那么你可以用这样的代码输出字段的内容：
<?php echo $row['call_from']; ?>
但是，如果记录URL包含例如以下字符串会发生什么？
<script>alert("I am injecting some code....");</script>
会发生一些不需要的代码会注入到您的页面中，因此每次需要回显某些输出时，最好使用像 htmlspecialchars(( 这样的安全函数：
<?php echo htmlspecialchars($row['call_from'], ENT_QUOTES); ?>
这样，任何不需要的代码都不会有害。
我还添加了一个基本的 TRY/CATCH 结构来处理错误。
希望这有帮助！
首先，我们需要得到我们的结果。
$vPIN = $_SESSION['PIN']; // or however you get your user's pin from session
$vQuery = "SELECT * FROM conference AS a LEFT JOIN participants as B USING (conference_sid) WHERE a.PIN='$vPIN'";
$oResult = $oDB->execute($vQuery);
$aRows = $oResult->fetchAll(PDO::FETCH_ASSOC);
请注意前缀：$v如果对于一个简单的变量，$o表示资源(我喜欢将其视为对象(，$a表示数组。这只是为了我的精神理智。
所以现在，我们有一个数组，可能非常大，包含会议桌中的每一行乘以参与者中的每一行。甜蜜，现在让我们构建一个包含一些含义的数组。
foreach($aRows as $aRow) // maybe a bit confusing but the 's' changes everything: all rows vs one row
{if (!isset($aConferences[$aRow['conference_sid']]['infos']))
{$aConferences[$aRow['conference_sid']]['infos']['date_created'] = $aRow['date_created'];
$aConferences[$aRow['conference_sid']]['infos']['date_completed'] = $aRow['date_completed'];
$aConferences[$aRow['conference_sid']]['infos']['record_url'] = $aRow['RecordURL'];
$aConferences[$aRow['conference_sid']]['infos']['pin'] = $aRow['PIN'];}
$aConferences[$aRow['conference_sid']]['participants'][] = $aRow['call_from'];}
所以这里发生的情况是，对于每一行，如果尚未设置相应conference_sid的信息，它们将被设置，然后我们为该会议的每个call_from创建一个从 0 到 x 的列表。 print_r具有虚拟值的数组：
[1627]['infos']['date_created'] = 2013-11-26
['date_completed'] = 2013-11-29
['record_url'] = 'http://whatever.com'
['PIN'] = 139856742
['participants'][0] = Bob
[1] = gertrude
[2] = Foo
[8542]['infos']['date_created'] = 2013-12-01
['date_completed'] = 2013-12-02
['record_url'] = 'http://whateverelse.com'
['PIN'] = 584217
['participants'][0] = Family Guy
[1] = aragorn
[2] = obama
[3] = Loki
所以这里有一个很好的数组，我们可以用它来构建一个HTML表！让我们这样做
$vHTML = '<table>
<thead>
<th>Date</th>
<th>Duration</th>
<th>Participants</th>
<th>Recording</th>
</thead>
<tbody>';
foreach ($aConferences as $conference_sid => $aConference) // notice the s and no s again
{$vHTML.= '<tr id="' . $conference_sid . '">';
$vDateCreated = $aConference['infos']['date_created'];
$vDateCompleted = $aConference['infos']['date_completed'];
$vHTML.= '<td>' . $vDateCreated . '</td>';
$vHTML.= '<td>' . date('Y-m-d',(strtotime($vDateCompleted) - strtotime($vDateCreated))) . '</td>'; // you might have to debug that date diff for yourself.
$vHTML.= '<td><ul>'; // here a foreach for the participants
foreach ($aConference['participants'] as $call_from)
{$vHTML.= '<li>' . $call_from . '</li>';}
$vHTML.= '</ul></td>';
$vHTML.= '<td>' . $aConference['infos']['record_url'] . '</td>';
$vHTML.= '</tr>';}
$vHTML.= '</tbody></table>';
所以在这里：为每个会议创建一个包含信息的表格行，然后为每个参与者在列表中添加一个列表项。 如果您希望任何精度，请注释。
哦，别忘了和$vHTML做点什么，比如回声$vHTML :)