Jump to content

Starting with PDO select statement


jiros1
Go to solution Solved by Muddy_Funster,

Recommended Posts

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'];
    }
Link to comment
Share on other sites

  • Solution

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 by Muddy_Funster
  • Like 1
Link to comment
Share on other sites

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>';
}
  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.