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

Emil Wallgren
Emil Wallgren
11,737 Points

Problem with fetchAll() in PDO

Hi! I have this code:

<?php

    public function showBlogForCategory() {

        if (isset($_GET['category'])) {
        $category = $_GET['category'];

        $sql = 'SELECT * FROM blog WHERE category=? ORDER BY id DESC';
        $prepared = $this->db->prepare($sql);
        $executed = $prepared->execute(array($category));
        $blogPosts = $executed->fetchAll(PDO::FETCH_ASSOC);

        $newLine = '';

        foreach ($blogPosts as $blogPost) {
            $newLine .=  '<h2><a href="nyhet.php?p='.$blogPost['id'].'">'.$blogPost['title'].'</a></h2>
                                      <h4><i>Av: '.$blogPost['author'].'</i></h4>
                                      <p>'.substr($blogPost['description'], 0, 200).'...</p>';
        }

        return $newLine;
    }
    }

But it's outputting this error message:

Fatal error: Call to a member function fetchAll() on a non-object in SECRET;-)

I cant find the problem...Do someone here understand what is wrong?

Regards!

/Emil

3 Answers

You should always put your database work in try catch blocks. You also have connected to your database. Here is code I am using in a development context on my localhost. User name and passwords will be changed in live deployment.

<?php
try {
    $db = new PDO("mysql:host=localhost;dbname=cubScout;charset=utf8mb4","root","");
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);

} catch (Exception $e) {
    echo $e->getMessage() . " dbCall Line 13";
    exit;
}

Replace localhost with your database host's name. dbname is your database name. root is the user name and there is no password, hence the "".

The catch is nice. The $e->getMessage() returns the MySQL error and I add the file and line (approximate) so I can troubleshoot easily. I will probably make that more obtuse on deployment. Then you exit if there is an error.

This is where I query the database:

<?php
function full_scout_list() {
    require('dbCall.php');
//  require(ROOT_PATH . "inc/database.php");

    try {
        $results = $db->query("
                SELECT FirstName, LastName, Den, Bobcat, Tiger, Wolf, Bear, Webelo, Arrow, Health, id
                FROM scouts
                ORDER BY Den DESC, LastName");
        $scouts = array(array());
        $scouts = $results->fetchAll(PDO::FETCH_ASSOC);
    //  $recent = array_reverse($recent);
        return $scouts;
    } catch (Exception $e) {
        echo $e->getMessage() . "db.php full_scout_list Line 52";
        exit;
    }
}

This is code to fetch information from the database and do something similar to what you want to do:

<?php
require(ROOT_PATH . 'database_learn/db.php');
require(ROOT_PATH . 'database_learn/dbCall.php');
//step through array and display in table
//          $scouts = array(array());
$scouts = full_scout_list();
//var_dump($scouts);
    $t = '<th class="heading">Name</th><th class="heading">Den</th><th class="heading">Bobcat</th><th class="heading">Tiger</th><th>Wolf</th><th class="heading">Bear</th><th class="heading">Webelo</th><th class="heading">Arrow of Light</th>';

foreach ($scouts as $scout) {
    $t = $t . '<tr>';
    $t = $t . '<th class="row">' . $scout["FirstName"] . ' ' . $scout["LastName"] . '</th>';
    $t = $t . '<td> ' . $scout["Den"] . '</td>';
    $t = $t . '<td>' . $scout['Bobcat'] . '</td>';
    $t = $t . '<td>' . $scout['Wolf'] . '</td>';
    $t = $t . '<td>' . $scout['Bear'] . '</td>';
    $t = $t . '<td>' . $scout['Webelo'] . '</td>';
    $t = $t . '<td>' . $scout['Arrow'] . '</td>';
    $t = $t . '</tr>';
}

The initial $t is not elegant and I want to figure out a way to do that better. I am trying to pull the column names and just have not had time to look it up. I think I can also clean up the code in the foreach loop with a count and while statement.

I did not get variables to work in my database queries. You can see some attempts in my commented out lines. I got frustrated and wanted to make it work before I went back to the variables. I ultimately want constants so I can put the code on GitHub without revealing the key information to let someone in.

Emil Wallgren
Emil Wallgren
11,737 Points

Check my answer below. Thanks for your support! :-)

/Emil

No need to be secretive about error dumps.

Are you sure you are connected to the database? You can wrap your execute statement around an if statement to see if it has executed properly:

<?php

$sql = 'SELECT * FROM blog WHERE category=? ORDER BY id DESC';
$prepared = $this->db->prepare($sql);

if ($executed = $prepared->execute(array($category)) ) {
   echo "Statement executed successfully.";
} else {
  $arr = $prepared->errorInfo();
  print_r($arr);
}
Emil Wallgren
Emil Wallgren
11,737 Points

Check my answer below. Thanks for your support! :-)

/Emil

Emil Wallgren
Emil Wallgren
11,737 Points

Got it working guys!!!

This is the proper code! Check what I changed in the variable $prepared!

<?php
    public function showBlogForCategory() {

        if (isset($_GET['category'])) {
        $category = $_GET['category'];

        $sql = 'SELECT * FROM blog WHERE category=? ORDER BY id DESC';
        $prepared = $this->db->prepare($sql);
        $prepared->execute(array($category));
        $blogPosts = $prepared->fetchAll(PDO::FETCH_ASSOC);

        $newLine = '';

        foreach ($blogPosts as $blogPost) {
            $newLine .=  '<h2><a href="nyhet.php?p='.$blogPost['id'].'">'.$blogPost['title'].'</a></h2>
                                      <h4><i>Av: '.$blogPost['author'].'</i></h4>
                                      <p>'.substr($blogPost['description'], 0, 200).'...</p>';
        }

        return $newLine;
    }
    }