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 Understanding SQL Injections

Jonathan Prada
Jonathan Prada
7,039 Points
Posted by Jonathan Prada
Jonathan Prada
7,039 Points

I followed the videos, line by line but still get "Unable to retrieve results". HELP :) ME :)

This is my full catalog array:

<?php
function full_catalog_array(){
    //include to connect to db
    include("connection.php");
    //Run a query that will retrieve items
    //its a good idea to use try/catch for all requests
    try {
        //The query method takes in SQL as a string
        $results = $db->query("SELECT title, category, img FROM Media");
    } catch (Exception $e) {
        echo "Unable to retrieve results";
        exit;
    }
    $catalog = $results->fetchAll(PDO::FETCH_ASSOC);
    return $catalog;
}

This is my single item array:

<?php
//grabs a single item
//will get the info based on the item id passed in
function single_item_array($id){
    //include to connect to db
    include("connection.php");
    //Run a query that will retrieve items
    //its a good idea to use try/catch for all requests
    try {
        //The query method takes in SQL as a string
        $results = $db->query(
          "SELECT media_id, title, category, img, format, year, 
          publisher, isbn, genre
          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(PDO::FETCH_ASSOC);
    return $catalog;
}

This is the get item html function:

<?php
//gets passed parameters are iterated in a for loop at index.php
function get_item_html($item) {
    $output = "<li><a href='details.php?id="
        . $item["media_id"] . "'><img src='"
        . $item["img"] . "' alt='"
        . $item["title"] . "' />"
        . "<p>View Details</p>"
        . "</a></li>";
    return $output;
}
Kristofer Doman
Kristofer Doman
18,307 Points

I'm assuming this error is coming from your single_item_array function. In your SELECT statement, you are referencing an ambiguous column (media_id). When a column with an identical name belongs in numerous tables in a join, you must specify which column you're referencing, in which table. For instance, like you do in the WHERE clause, media.media_id.

Try changing media_id to media.media_id in your select statement inside single_item_array().

If this doesn't fix things. There's a better way to debug this than they show us in the course. To get a better idea about what's going on and why it's not working, in your catch statements, remove what's there now and replace it with: print_r($e); exit;

This will display everything inside the Exception object. It should have messages which are more descriptive than "Unable to retrieve results".

Alex Bauer
Alex Bauer
9,426 Points
Alex Bauer
Alex Bauer
9,426 Points

It seems that the $item["media_id"] is what's wrong here along with some other things. I followed her line by line to make sure I didn't mess anything up either and in the next video she has different code...In the next video go to the downloads tab and download the zip folder to find all of the correct code. There are things that were done off camera to correct and edit code without any explanation.

Christopher Hall
Christopher Hall
11,158 Points
Christopher Hall
Christopher Hall
11,158 Points

Hello, try this code, hopefully it works for you.

try { //The query method takes in SQL as a string $results = $db->query( "SELECT Media.media_id, title, category, img, format, year, publisher, isbn, genre 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; }

10 Answers

Adriana Dodge
Adriana Dodge
4,680 Points
Adriana Dodge
Adriana Dodge
4,680 Points

The mistake is when she adds media_id to the select of single_item_array . This will throw an error that says "#1052 - Column 'media_id' in field list is ambiguous" so the fix is to add Media.media_id to the select. single_item_array query will be

 $results= $db->query("SELECT Media.media_id, title, category, img, format, year, publisher, isbn, genre
          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");
Kevin Korte
Kevin Korte
28,148 Points
Kevin Korte
Kevin Korte
28,148 Points

It's almost impossible to troubleshoot for me since I don't have your database, and I'm not really set up for it..but, looks like you're not able to get results back from the db, so you'll need to investigate why that is.

None of the code here is likely the problem. Likely one of two problems.

  1. Your db query doesn't match your db schema

OR

  1. In your connection.php file, it's not actually able to connect to the db so that you can query for records

Let me know if that helps you.

Ronaldo Fialho
Ronaldo Fialho
5,105 Points
Ronaldo Fialho
Ronaldo Fialho
5,105 Points

I'm having the same problem here!! i can retrieve data from full_catalog_array() function which i copied! and then i made modifications exactly the same as the video and i'm unable to retrieve single data. Did you figure it out???????

Jonathan Prada
Jonathan Prada
7,039 Points
Jonathan Prada
Jonathan Prada
7,039 Points

No Ronaldo! unfortunately i just moved on on the PHP track, this section appears to be riddled with issues! i will come back to it once everything else is finished. If you find an answer do share!

molo
molo
8,927 Points
molo
molo
8,927 Points

Usually if you are using JOINS you need to specify the table name BEFORE the column.

For example:

"SELECT Media.media_id, Media.title , Media.category, Media.img, Media.format, Media.year, Genres.genre, Books.publisher, Books.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"

Kristofer Doman
Kristofer Doman
18,307 Points

Well, that's false. You only need to specify table names before columns if a column is ambiguous (same column exists in numerous tables in the join).

kennyligthart
kennyligthart
3,859 Points
kennyligthart
kennyligthart
3,859 Points

You have to update the connection.php file

<?php

try {
  $db = new PDO("sqlite:".__DIR__."/database.db");
  $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
  echo "Unable to connect";
  //echo $e->getMessage();
  exit;
}
Steven Till
Steven Till
12,832 Points
Steven Till
Steven Till
12,832 Points

Adriana is correct in the answer above. This is what fixes it.

NTAMBARA Fred
NTAMBARA Fred
3,648 Points
NTAMBARA Fred
NTAMBARA Fred
3,648 Points

Encountered the same issue, "Adriana Dodge" response worked for me.

Brian Patterson
Brian Patterson
19,588 Points
Brian Patterson
Brian Patterson
19,588 Points

I have had the same issue. I was not able to retrieve the details of that book. Here is my details php file.

<?php
include("inc/functions.php");


if (isset($_GET["id"])) {
    $id =
    filter_input(INPUT_GET,"id",FILTERS_SANITIZE_NUMBER_INT);
    $item = single_item_array($id);
    var_dump($item);
}

if (!isset($item)) {
    header("location:catalog.php");
    exit;
}

$pageTitle = $item["title"];
$section = null;

include("inc/header.php"); ?>

<div class="section page">

    <div class="wrapper">

        <div class="breadcrumbs">
            <a href="catalog.php">Full Catalog</a>
            &gt; <a href="catalog.php?cat=<?php echo strtolower($item["category"]); ?>">
            <?php echo $item["category"]; ?></a>
            &gt; <?php echo $item["title"]; ?>
        </div>

        <div class="media-picture">

        <span>
            <img src="<?php echo $item["img"]; ?>" alt="<?php echo $item["title"]; ?>" />
        </span>

        </div>

        <div class="media-details">

            <h1><?php echo $item["title"]; ?></h1>
            <table>

                <tr>
                    <th>Category</th>
                    <td><?php echo $item["category"]; ?></td>
                </tr>
                <tr>
                    <th>Genre</th>
                    <td><?php echo $item["genre"]; ?></td>
                </tr>
                <tr>
                    <th>Format</th>
                    <td><?php echo $item["format"]; ?></td>
                </tr>
                <tr>
                    <th>Year</th>
                    <td><?php echo $item["year"]; ?></td>
                </tr>
                <?php if (strtolower($item["category"]) == "books") { ?>
                <tr>
                    <th>Authors</th>
                    <td><?php echo implode(", ",$item["authors"]); ?></td>
                </tr>
                <tr>
                    <th>Publisher</th>
                    <td><?php echo $item["publisher"]; ?></td>
                </tr>
                <tr>
                    <th>ISBN</th>
                    <td><?php echo $item["isbn"]; ?></td>
                </tr>
                <?php } else if (strtolower($item["category"]) == "movies") { ?>
                <tr>
                    <th>Director</th>
                    <td><?php echo $item["director"]; ?></td>
                </tr>
                <tr>
                    <th>Writers</th>
                    <td><?php echo implode(", ",$item["writers"]); ?></td>
                </tr>
                <tr>
                    <th>Stars</th>
                    <td><?php echo implode(", ",$item["stars"]); ?></td>
                </tr>
                <?php } else if (strtolower($item["category"]) == "music") { ?>
                <tr>
                    <th>Artist</th>
                    <td><?php echo $item["artist"]; ?></td>
                </tr>
                <?php } ?>
            </table>

        </div>

    </div>

</div>