Starting with PDO select statement

[jiros1]

Hi, Thanks for taking the time.

 

I'm trying PDO for the first time and I'm trying to make a CRUD codebase.

I can insert but I have trouble with the select statement and iterate through the data, Apache gives me this error:

Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in index.php:38 

This is line 38:

    $getName = $result;
    while( $row = $getName->fetch_assoc() ){
        echo $row['name'];
    }

This is my code:

     $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare("SELECT name FROM users");
    $stmt->execute();

    // set the resulting array to associative
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    
    $getName = $result;

    //Iterate through the data
    // line 38:
    while( $row = $getName->fetch_assoc() ){
            echo $row['name'];
    }

[requinix]

You're trying to call fetch_assoc on whatever the setFetchMode function returned.

 

1. You need to call it on a statement object.

2. You might be confusing PDO with mysqli. PDO does not have a "fetch_assoc". Use one of these instead.

[Muddy_Funster]

Here's an example PDO Select statement for reference:

$con = new PDO('lib:host=host;dbname=dbname','usr','pwd')
$sql = "SELECT name FROM user WHERE UID = :uid";
$stmt = $con->prepare($sql);
$stmt->bindParam(':uid',$id, PDO::PARAM_INT);
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($result);

There are variants on how to bind parameters and how to fetch results, but this is the simplest way that is closest to what you already tried. I also altered the query to show one way of how you would bind a parameter to a prepared statement.

 

Hope it helps some.

Edited July 5, 2016 by Muddy_Funster

[Jacques1]

You can actually omit the fetch() calls altogether. PDO is rather “clever”: If you simply iterate over the PDOStatement object with a foreach loop, you automatically get the rows as associative arrays.

 

Also note that prepared statements are useless when there's no external input. Simply use a static query:

<?php

const DB_HOST = '...';
const DB_USER = '...';
const DB_PASSWORD = '...';
const DB_NAME = '...';
const DB_CHARSET = 'UTF8';



$dsn = 'mysql:host='.DB_HOST.';dbname='.DB_NAME.';charset='.DB_CHARSET;
$databaseConnection = new PDO($dsn, DB_USER, DB_PASSWORD, [
    PDO::ATTR_EMULATE_PREPARES => false,                    // important: Disable emulated prepared statements
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);



$userStmt =  $databaseConnection->query('
    SELECT name
    FROM users
');

foreach ($userStmt as $user)
{
    echo 'The name is '.htmlspecialchars($user['name'], ENT_QUOTES | ENT_SUBSTITUTE, 'UTF-8').'<br>';
}

[jiros1]

Thanks a lot, I've got it working now!

A question though, how come we can do a foreach iteration now? And why doesn't that work for MYSQLi?

[Jacques1]

A question though, how come we can do a foreach iteration now?

 

A PDOStatement implements the Traversable interface.

 

 

 

And why doesn't that work for MYSQLi?

 

It does, at least partially.

 

The mysqli_result class implements Traversable as well. You get a mysqli_result either through the mysqli::query() method or by calling mysqli_stmt::get_result() in the case of a prepared statement (this requires the native MySQL driver, though, so it's not available on all systems).

 

mysqli is a more low-level interface, so it tends to be harder to use.

[jiros1]

Awesome, thanks!

A PDOStatement implements the Traversable interface.

 

 

 

 

It does, at least partially.

 

The mysqli_result class implements Traversable as well. You get a mysqli_result either through the mysqli::query() method or by calling mysqli_stmt::get_result() in the case of a prepared statement (this requires the native MySQL driver, though, so it's not available on all systems).

 

mysqli is a more low-level interface, so it tends to be harder to use.