Using PHP to integrate mysql query into website

Question

I'm running two queries to get data from a tracks table and topics table and saving them into $tracks and $topics. I'm generating the "cards" with get_track_html() . I'm getting one card for each track but I can't figure out how to list the topics inside each card.
How do I

query only the topics for each track?
list out the topics with a ", " in between them? like implode(", ", $topics);

function advanced_track_array() {
  include ("inc/connection.php");
  try { 
    $results = $dbh->query("
      SELECT trackID, track_title 
      FROM tracks 
      WHERE category = 'advanced'
      ");
    } catch (Exception $e) {
      echo "Unable to retrieve results";
      exit;
    }

    $tracks = $results->fetchALL(PDO::FETCH_ASSOC);
    return $tracks;
}

function topics_array($trackID) {
  include ("inc/connection.php");
  try { 
    $results = $dbh->query("
      SELECT topic
      FROM topics
      WHERE topics.trackID = $trackID
      ");
    } catch (Exception $e) {
      echo "Unable to retrieve results";
      exit;
    }

    $topics = $results->fetchALL(PDO::FETCH_ASSOC);
    return $topics;
}

and here is the call to the function

<?php 

$tracks = advanced_track_array();

foreach($tracks as $id => $item) {
  $trackColor = "success";
  $trackID = $item["trackID"];
  $topics = topics_array($trackID);
  echo get_track_html($id,$item,$trackColor,$topics);
}
?>

function get_track_html($id,$item,$trackColor,$topics) {
  $output = 
  "<div class='col-lg-6 m-t-2'><!-- div track -->"  
.   "<div class='card'>" 
.       "<div class='card-header bg-" . $trackColor . "'>" 
.       $item["track_title"]     
.       "</div>"
.       "<div class='card-block span'>"
    .       "<h4 class='card-title'>"
    .       "Topics:"
    .       "</h4>"
    .       "<p class='card-text'>"
    .       "</p>"
    .       "<a href='details.php?id=" . $id . "' class='btn btn-" . $trackColor . "'>Start</a>"
.       "</div>"
.   "</div>"
. "</div><!-- div track -->";
  return $output;
}

Answer 1 · 2016-09-19T13:20:26Z

September 19, 2016 1:20pm

Hi Ryan O'Connor,

You can achieve this with the one query by simply using a LEFT JOIN command which allows you to make two requests in the same query, the below should work fine which is simply joining the request for the tracks to the topics by the trackID which then appends each topic to an array called track_topics.

As you're using PDO::FETCH_ASSOC as your fetch style, you will need to iterate over the track_topics array and retrieve each topic title which should have a key of topic.

SELECT tracks.trackID, tracks.track_title, topics.topic AS track_topics
  FROM tracks
  LEFT JOIN topics ON tracks.trackID = topics.trackID
  WHERE tracks.category = 'advanced'

Hope that helps.

Welcome to the Treehouse Community

Looking to learn something new?

Ryan O'Connor

Ryan O'Connor

Using PHP to integrate mysql query into website

1 Answer

Chris Shaw

Chris Shaw