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

Daniel Goldberg
Daniel Goldberg
12,443 Points
Posted by Daniel Goldberg
Daniel Goldberg
12,443 Points

single_item_array function not running properly. Problem with LEFT OUTER JOIN

For some reason my single_item_array function is not running correctly. Whenever I attempt to load any details page, it is unable to retrieve the data it runs the exception instead. After playing around a bit I discovered that found that the problem lies in the LEFT OUTER JOIN line. When I delete this line (as well as publisher and isbn from the SELECT line) the code runs perfectly. As you can see below, my code is identical to the code shown in this video so I have no idea why it won't work. Please help!

My code:

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; 
}
$output = $results->fetch();
return $output;
}

4 Answers

Paul Yabsley
Paul Yabsley
46,713 Points
Paul Yabsley
Paul Yabsley
46,713 Points

Going back to the video and looking at this example it seems the purpose is to optionally retrieve publisher and isbn from the book table only if the item in the media table exists in the book table.

As to why it isn't working I'm not quite sure without the same db set up.

You could however try putting this in your exception in place of the user friendly message: echo $e->getMessage();

That will output the actual sql error which should give you some clues. Try that and report back.

<?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";
    echo $e->getMessage(); // Use this instead of the above line and it gives you the actual sql error
    exit; 
}
Paul Yabsley
Paul Yabsley
46,713 Points
Paul Yabsley
Paul Yabsley
46,713 Points

Hi Daniel

What error message does it give you?

When you say you take out publisher and isbn from your column list and it works, perhaps that means you have to prefix those columns with the table name. It's a bit difficult to tell without the table name. Perhaps you could link to the video this is from?

Daniel Goldberg
Daniel Goldberg
12,443 Points
Daniel Goldberg
Daniel Goldberg
12,443 Points

Hi Paul

I meant for this question to be linked to the "Querying Multiple Tables with JOIN" video in the "Integrating PHP with Databases" course. Here is a link to the video: https://teamtreehouse.com/library/integrating-php-with-databases/using-relational-tables/querying-multiple-tables-with-join

I have been having trouble fully wrapping my head around SQL's "JOIN" clause so I am not completely sure exactly what this LEFT OUTER JOIN statement is doing, but I know that it is supposed to make information from the Books table accessible (such as publisher and isbn) when selecting information from the Media table. However, when I add publisher and isbn to the SELECT statement, along with this LEFT OUTER JOIN statement, the code is unable to run, and instead catches the exception, reading "Unable to retrieve results." When I remove these additions, keeping the code exactly the same otherwise, everything runs perfectly and the exception is not caught. Thank you so much for the help and I hope this gives you a better understanding of my issue.

Daniel Goldberg
Daniel Goldberg
12,443 Points
Daniel Goldberg
Daniel Goldberg
12,443 Points

Thanks for the suggestion! I just did this and now the message reads "SQLSTATE[HY000]: General error: 1 ambiguous column name: media_id"

Any idea what this means?

Paul Yabsley
Paul Yabsley
46,713 Points
Paul Yabsley
Paul Yabsley
46,713 Points

Yep this is the sort of error you need when debugging sql queries. When joining tables that contain columns with the same name sql doesn't know which one you want unless you prefix it with the table name. In this case both the Media table and the Books table have a column called media_id. The syntax for prefixing the table name is a period. i.e Table.column

To fix this particular error change the start of your statement to: SELECT Media.media_id,