CODEWITHSUNDEEP
phppdoassociative-array
spice
spice

Reputation: 1510

Get ASSOC array with PDO without using prepared statement

I'm using an internal switch to determine the sort order of my results and have just discovered that you can't use PDO to bind certain params (like selecting a table or specifying a sort order) in this way.

So I'm now trying to return my results without binding using ->query like this (ignoring the sort part for now) :

$results = $db->query("SELECT * from tracks WHERE online = 1", PDO::FETCH_ASSOC);

But when I print_r($results) I'm just getting the PDO object statement back :

PDOStatement Object
(
    [queryString] => SELECT * from tracks WHERE online = 1
)

What am I doing wrong here?

Here is my PDO connection :

protected static function getDB()
    {

        static $db = null;

        if ($db === null) {
            $dbhost = getenv('DB_HOST');
            $dbuser = getenv('DB_USER');
            $dbpass = getenv('DB_PASS');
            $dbname = getenv('DB_NAME');

            try {
                $db = new PDO("mysql:host=$dbhost;dbname=$dbname;charset=utf8mb4",
                               $dbuser, $dbpass);

                $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
                $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

            } catch (PDOException $e) {
                echo $e->getMessage();
            }
        }

        return $db;
    }

Upvotes: 0

Views: 520

Answers (2)

Martin Zeitler
Martin Zeitler

Reputation: 76807

the statement needs to be executed ...

$stmt = $db->query("SELECT * from tracks WHERE online = 1");
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

passing it as second argument should also work:

$stmt = $db->query("SELECT * from tracks WHERE online = 1", PDO::FETCH_ASSOC);
$data = $stmt->fetchAll();

or as one-liner:

$data = $db->query("SELECT * from tracks WHERE online = 1")->fetchAll(PDO::FETCH_ASSOC);

there's also:

$stmt->setFetchMode(PDO::FETCH_ASSOC);

Upvotes: 1

u_mulder
u_mulder

Reputation: 54796

PDO::query manual shows you how to work with results, returned by query method:

$results = $db->query("SELECT * from tracks WHERE online = 1", PDO::FETCH_ASSOC);
foreach ($results as $row) {
    print $row;
}

Upvotes: 0

Related Questions