Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP Integrating PHP with Databases Using Relational Tables Querying Multiple Tables with JOIN

Igor Skoldin
Igor Skoldin
6,779 Points
Posted by Igor Skoldin
Igor Skoldin
6,779 Points

Code in the video won't work

Leave alone the typo in the video var_dump(singl_item_array(1)) while the function is called single_item_array().

Here's the code of the single_item_array function from the video:

function single_item_array($id) {
  include("connection.php");

  try {
    $results = $db->query(
      "SELECT media_id, title, category, img, format, year, genre, publisher, isbn 
      FROM Media
      JOIN Genres ON Media.genre_id = Genres.genre_id
      LEFT OUTER JOIN Books ON Media.media_id = Books.media_id
      WHERE Media.media_id = $id"
    );
  } catch (Exception $e) {
    echo "Unable to retrieve results";
    exit;
  }

  $catalog = $results->fetch();
  return $catalog;
}

If we run this, we will get Unable to retrieve results, not the result. If we do var_dump($e); in the catch block, we will see:

object(PDOException)#3 (8) { ["message":protected]=> string(65) "SQLSTATE[HY000]: General error: 1 ambiguous column name: media_id" ["string":"Exception":private]=> string(0) "" ["code":protected]=> string(5) "HY000" ["file":protected]=> string(43) "/home/treehouse/workspace/inc/functions.php" ["line":protected]=> int(11) ["trace":"Exception":private]=> array(3) { [0]=> array(6) { ["file"]=> string(43) "/home/treehouse/workspace/inc/functions.php" ["line"]=> int(11) ["function"]=> string(5) "query" ["class"]=> string(3) "PDO" ["type"]=> string(2) "->" ["args"]=> array(1) { [0]=> string(241) "SELECT media_id, title, category, img, format, year, genre, publisher, isbn FROM Media JOIN Genres ON Media.genre_id = Genres.genre_id LEFT OUTER JOIN Books ON Media.media_id = Books.media_id WHERE Media.media_id = 1" } } [1]=> array(4) { ["file"]=> string(43) "/home/treehouse/workspace/inc/functions.php" ["line"]=> int(23) ["function"]=> string(17) "single_item_array" ["args"]=> array(1) { [0]=> int(1) } } [2]=> array(4) { ["file"]=> string(35) "/home/treehouse/workspace/index.php" ["line"]=> int(2) ["args"]=> array(1) { [0]=> string(43) "/home/treehouse/workspace/inc/functions.php" } ["function"]=> string(7) "include" } } ["previous":"Exception":private]=> NULL ["errorInfo"]=> array(3) { [0]=> string(5) "HY000" [1]=> int(1) [2]=> string(31) "ambiguous column name: media_id" } } Unable to retrieve results

So we need to clarify the media_id column — do SELECT Media.media_id instead of SELECT media_id to make it work.

What is the reason all these things work smoothly in the video?

Waldo Alvarado
Waldo Alvarado
16,322 Points
Waldo Alvarado
Waldo Alvarado
16,322 Points

Thanks for sharing this! It helped me out tremendously!

7 Answers

Matthew Cronan
Matthew Cronan
4,656 Points
Matthew Cronan
Matthew Cronan
4,656 Points

Thank you! Was struggling with this.

Zach Slagowski
Zach Slagowski
15,182 Points
Zach Slagowski
Zach Slagowski
15,182 Points

Thank you for this. For the longest time I thought I was doing something wrong.

Adan Camacho
Adan Camacho
17,567 Points
Adan Camacho
Adan Camacho
17,567 Points

Thank you! Also, I'm working on this locally and I didn't have media_id in my code, I'm not sure if I missed when it was added or if that was skipped, but anyhow, I added Media.media_id and it worked.

Ray Knag
Ray Knag
8,110 Points
Ray Knag
Ray Knag
8,110 Points

Thank you! This was very helpful

Rebecca Bompiani
Rebecca Bompiani
16,948 Points
Rebecca Bompiani
Rebecca Bompiani
16,948 Points

This was driving me crazy! Thanks for helping us out!

Micah Dunson
Micah Dunson
34,368 Points
Micah Dunson
Micah Dunson
34,368 Points

Thanks for the help on this!!

i6ze5srftzgi8o5iu46z5etr
i6ze5srftzgi8o5iu46z5etr
6,931 Points
i6ze5srftzgi8o5iu46z5etr
i6ze5srftzgi8o5iu46z5etr
6,931 Points

"SELECT Media.media_id, title, category, img, format, year, genre, publisher, isbn FROM Media JOIN Genres ON Media.genre_id = Genres.genre_id LEFT OUTER JOIN Books ON Media.media_id = Books.media_id WHERE Media.media_id = $id"