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

Posted by Joshua Esmero
Joshua Esmero
10,571 Points

Getting "Integrity constraint violation: 1052" error from query

Hi,

I've followed the query shown in 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"; echo $e->getMessage(); exit; }

$catalog = $results->fetch();

return $catalog;

}

But I keep getting this error:

Unable to retrieve resultsSQLSTATE[23000]: Integrity constraint violation: 1052 Column 'media_id' in field list is ambiguous

I removed the "media_id" from the field list after SELECT, and it works. Why is it that the video example included media_id but did not get this error? I'm developing locally using WAMP.

Tomazzino Grabarino
Tomazzino Grabarino
101 Points

This: "SELECT media_id AS med_id, ...

What this means is that you have included other column in your query which also has media_id. Therefore you ended up with 2 media id columns being pulled in.

To fix this, you need to select one media_id AS some_other_name just to keep those unique.

If still not clear. Here's simple example:

Query to pull data from items table and basket table Both tables have item_id column. Therefore when i pull data from both of them I end up getting 2 item_id columns which is amiguous. Fix to this:

SELECT items.id AS item_id, basket.item_id AS basket_item_id FROM .....

Hope that it makes sense Have fun