CODEWITHSUNDEEP
phpmysqlsqlpdo
alias51
alias51

Reputation: 8608

How do output combined results from two MySQL tables using PDO

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

Table: conference:

conference_sid, date_created, date_completed, RecordURL, PIN

*date_created and *date_completed are timestamps

Table: participants:

conference_sid, call_sid, call_from, name_recording

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>

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

Upvotes: 2

Views: 679

Answers (6)

F&#233;lix Adriyel Gagnon-Grenier
F&#233;lix Adriyel Gagnon-Grenier

Reputation: 8885

First, we need to get our result.

$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);

note the prefixes: $v if for a simple variable, $o represents a ressource (which I like to think of as an object), $a represents an array. It's just for my mental sanity.

so now, we have an array, probably very big, containing every single row in the conference table times every corresponding row in the participants. Sweet, now let's build an array with some meaning in it.

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'];}

so what happens here is that for each row, if the infos for corresponding conference_sid haven't been set, they will be, and then we create a list from 0 to x of each call_from for that conference. print_r of that array with dummy values:

[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

so here is a nice array with which we can build a html table! let's do that

$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>';

so here: for each conference create a table row with the infos, then for each participant, add a list item within the list. comment if you wish for any precision.

oh, and don't forget to do something with $vHTML. like echo $vHTML :)

Upvotes: 0

fthiella
fthiella

Reputation: 49049

In this particular contex I prefer to use two separated queries. Here's how I would do it:

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

Please notice that you have to add some code to handle errors and to correctly escape all data you echo (can you really trust your database?). Also element IDs should be unique within the entire document, you can have just one id="conference_sid" in your page. Use classes instead.

Edit

If you can really trust your database, then you can just output the contents of a field with code like this:

<?php echo $row['call_from']; ?>

but what happens if RecordURL contains for example the following string?

<script>alert("I am injecting some code....");</script>

It will happen that some unwanted code will be injected in your page, so it is always better yo use a safe function like htmlspecialchars() every time you need to echo some output:

<?php echo htmlspecialchars($row['call_from'], ENT_QUOTES); ?>

this way, any unwanted code won't be harmful.

I also added a basic TRY/CATCH construct to handle errors.

Hope this helps!

Upvotes: 0

hanzo2001
hanzo2001

Reputation: 1398

This will be my take on it, it uses 2 separate queries to keep the data kinda separated. I use fetchAll() for brevity but this could have performance issues, luckily this can be accomodated. I didn't put any error checking, if you want it or you have questions, please ask

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

Upvotes: 0

Damodaran
Damodaran

Reputation: 11047

You can combine the participants using 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

Refer SQLFIDDLE and MySQL documentation about TIMEDIFF.

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>

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.

Upvotes: 4

rsanchez
rsanchez

Reputation: 14657

The following query will give you the information you need to display:

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

You will need to process the results with a nested loop. The outer loop should advance each time the conference_sid changes. The inner loop will display each element of the participants list for that conference.

Upvotes: 0

Noam Rathaus
Noam Rathaus

Reputation: 5598

I won't do the PHP part, as I am not that knowledgeable in PHP, but here is the SQL:

SELECT *
FROM `conference`, `participants`
WHERE `conference`.PIN = $PIN AND
      `participants`.conference_sid = `conference`.conference_sid

This will return rows with the information from conference and the participants of those conferences, joined into one row.

Upvotes: 2

Related Questions