Welcome to the Treehouse Community

The Treehouse Community is a meeting place for developers, designers, and programmers of all backgrounds and skill levels to get support. Collaborate here on code errors or bugs that you need feedback on, or asking for an extra set of eyes on your latest project. Join thousands of Treehouse students and alumni in the community today. (Note: Only Treehouse students can comment or ask questions, but non-students are welcome to browse our conversations.)

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and a supportive community. Start your free trial today.

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

Sean Flanagan
Sean Flanagan
33,232 Points

Unable to retrieve results

Hi.

This is the message I got when I ran my code.

Here's my functions.php:

<?php

function full_catalog_array() {
  include("connection.php");
  try {
    $results = $db->query("SELECT media_id, title, category, img FROM Media");
  } catch (Exception $e) {
    echo "Unable to retrieve results";
    exit;
  }

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

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;
}

var_dump(single_item_array(1));

function get_item_html($id,$item) {
    $output = "<li><a href='details.php?id="
        . $id . "'><img src='" 
        . $item["img"] . "' alt='" 
        . $item["title"] . "' />" 
        . "<p>View Details</p>"
        . "</a></li>";
    return $output;
}

function array_category($catalog,$category) {
    $output = array();

    foreach ($catalog as $id => $item) {
        if ($category == null OR strtolower($category) == strtolower($item["category"])) {
            $sort = $item["title"];
            $sort = ltrim($sort,"The ");
            $sort = ltrim($sort,"A ");
            $sort = ltrim($sort,"An ");
            $output[$id] = $sort;            
        }
    }

    asort($output);
    return array_keys($output);
}

Here's a snapshot:

https://w.trhou.se/cd6hxy2qoj

I'd appreciate any help.

4 Answers

Hi,

I think this works.

The problem seems to be with the select string. You need to prefix so you identify the final locations of the DB items.

function single_item_array($id) {
    include ("connection.php");
    try {
        $results = $db->query(
            "SELECT Media.media_id, Media.title, Media.img, Media.format, Media.year, Media.category, 
            Genres.genre, Books.publisher, books.isbn
            FROM Media
            JOIN Genres ON Media.media_id = Genres.genre_id
            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;
}
var_dump(single_item_array(1));
Alena Holligan
Alena Holligan
Treehouse Teacher

media_id is ambiguous because more than one table includes that field name. You will need to specify a table you would like to use, as Matilka Krow suggested, or just for that one field, example:

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

You can get more details on your error by adding the following line to the exception catch block in the functions.php file

echo $e->getMessage();

You can download the SQLite Browser to interact with your database directly and try running any queries https://sqlitebrowser.org/

This worked for me. To be clear, when I follow the steps in the video in the Treehouse Workspaces, it results in an error. It appears that the video is inaccurate.

Tim Knight
Tim Knight
28,863 Points

Hi Sean,

I've been taking a look at at this and I'm getting the same result on my local server. I think there might be an issue with the database file. Are you able to open the database file with a SQLite browser? Something like https://sqlitebrowser.org/ or similar?

Sean Flanagan
Sean Flanagan
33,232 Points

Hi Tim.

I've never used SQLite before. Should I install it now?

Tim Knight
Tim Knight
28,863 Points

I find it's nice to have a tool that you can use to browse the database so it's probably a good idea. Did this file download come with one of the lessons?

Martin Wiulsrød
seal-mask
.a{fill-rule:evenodd;}techdegree seal-36
Martin Wiulsrød
Front End Web Development Techdegree Graduate 16,314 Points

I got the same error and I think it has to do with what columns are inside the database we downloaded. Some of the colums we are targeting in our select statement doesn't exist or has a different name.

SELECT media_id, title, category, img, format, year, genre, publisher, isbn FROM Media

When I go into the media table, there is no "genre", "publisher" or "ISBN". If you remove those from the try block the code runs (for me anyway). Here is an example:

try {
        $results = $db->query(
        "SELECT media_id, title, category, img, format, year
        FROM Media"
        );
}

Not really sure what to do from this point out though.

Sean Flanagan
Sean Flanagan
33,232 Points

No, the file download didn't accompany any of the lessons, not to my knowledge anyway.

Tim Knight
Tim Knight
28,863 Points

Oh okay. I wasn't sure if maybe that database file came from a lesson. I would certainly check out a browser for the database file, but from what it looks like now, that database file doesn't seem to be readable by the SQLite library.

Siraj Khan
Siraj Khan
3,451 Points

I have got the same problem Tim Knight , I tried everything but still get "Unable to retrieve results". YES, the database file came from a lesson.