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

Riki Tiilikainen
Riki Tiilikainen
4,042 Points
Posted by Riki Tiilikainen
Riki Tiilikainen
4,042 Points

Can’t get the single_item_array to work

For some reason, I can’t get the single_item_array to work, copied the code from the full_catalog_array that works fine, but when I var_dump the single_item_array I get this, Unable to retrieved resultsSQLSTATE[HY000]: General error: 1 ambiguous column name: media_id
Any suggestion on how to fix this?

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 retrieved results"; echo $e -> getMessage(); exit; } $catalog = $results ->fetch(); return $catalog; } var_dump(single_item_array(1));

Fred Young
Fred Young
15,523 Points

This is happening because the column media_id is used in both the Media table & the Books table. The SELECT statement doesn't know which one to use. Therefore, you must specify one of the media ids. I chose the one from the Media table and rewrote the query:

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

2 Answers

Geraldo Hernandez
Geraldo Hernandez
7,515 Points
Geraldo Hernandez
Geraldo Hernandez
7,515 Points

Fred Young Thank you for the solution. I was having the same issue before I took a look again at the database diagram from the first video and noticed that the Books table was using media_id as a foreign key to the Media table. Although Alena hasn't done this yet, it's good practice to select each column with a specified table when using joins in order to avoid ambiguous errors.

Victor Kandalov
Victor Kandalov
21,582 Points
Victor Kandalov
Victor Kandalov
21,582 Points

Had the same problem. Also, Alena mistyped var_dump(singl_item_array(1)); in the video, and they didn't show how she corrected it for var_dump(single_item_array(1)); so obviously it couldn't work for her as was in the video. Frustration for all students me included who repeat everything after video and it doesn't work as shown.