如何使用PDO输出来自两个MySQL表的组合结果 [英] How do output combined results from two MySQL tables using PDO

问题描述

我在MySQL中具有以下两个表结构，它们记录了电话会议和加入会议的参与者的详细信息:

I have the following two table structures in MySQL, which record details of a conference call and those participants that joined it:

表格:会议:

conference_sid, date_created, date_completed, RecordURL, PIN

* date_created和* date_completed是时间戳

*date_created and *date_completed are timestamps

表:参与者:

conference_sid, call_sid, call_from, name_recording

我想输出一个简单的表，该表将每个Conference_sid的以下结果显示为单独的行:

I want to output a simple table, that displays the following results for each conference_sid as a separate row:

<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>

我只希望该表显示与用户Session :: get('PIN')PIN相同的会议的相关结果

I only want this table to show relevant results for conferences that have the same PIN as the user's Session::get('PIN')

推荐答案

您可以使用

请参考 SQLFIDDLE 和有关现在，应用程序逻辑将为

Now the application logic will be

<?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中用逗号分隔的结果集.您可以使用分解将此字符串转换为数组.

You will get the result set with comma(,) separated values in pid, call_sid, call_from, and name_recording. You can convert this string to array using explode.

array explode ( string $delimiter , string $string [, int $limit ] )

返回一个字符串数组，每个字符串都是字符串的子字符串 通过在由字符串定界符形成的边界上分割它而形成.

Returns an array of strings, each of which is a substring of string formed by splitting it on boundaries formed by the string delimiter.

这篇关于如何使用PDO输出来自两个MySQL表的组合结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！