PDO query returns blank Array

Question

I am trying my hands on PDO for the first time. Issue is whenever I am running a PDO query I get a blank array in my browser

The code,

<?php

$config['db'] = array(

    'host'      => 'localhost',
    'username'  => 'root',
    'password'  => '',
    'dbname'    => 'website'

);

$db = new PDO('mysql:host=' .$config['db']['host']. ';dbname=' .$config['db']['dbname'], $config['db']['username'], $config['db']['password']);

//$query returns PDO statment object
$query = $db->query('SELECT `articles`.`title` FROM `articles`');

print_r($query);

//we will use different methods on PDO to work with database

//a generic method to display all results
while($rows = $query->fetch(PDO::FETCH_ASSOC)){
    echo '<br>'.$rows['title'];
}

$rows1 = $query->fetch(PDO::FETCH_ASSOC);
print_r($rows1);

$rows2 = $query->fetchAll(PDO::FETCH_ASSOC);
echo '<pre>', print_r($rows2, true), '</pre>';

$rows3 = $query->fetchAll(PDO::FETCH_NUM);
echo '<pre>',print_r($rows3, true),'</pre>';

$articles = $query->fetchAll(PDO::FETCH_ASSOC);
echo $articles[4]['title'];
?>

The problem occurs while printing or echoing values for the variables $rows1, $rows2 & $rows3.

I should be getting pre formatted array but all I get is blank array, as shown

Let me know your inputs friends, thanks...

these will not work because you already used that result-set object — Anant Kumar Singh, Jun 25 '16 at 11:34
You fetched data in while loop, that's why fetchAll is empty. You need different approach. First use fetchAll to save data into variable, and then use this variable in loops — instead, Jun 25 '16 at 11:37

score 3 · Accepted Answer · answered Jun 25 '16 at 11:41

3

fetch method use something like a cursor to return the results. Since you are exploring from beginning to the end of the result set (moving cursor from the beginning to the end) inside the

while($rows = $query->fetch(PDO::FETCH_ASSOC)){
    echo '<br>'.$rows['title'];
}

while loop above, when you come to code below that, the result set's cursor is already at the end. therefore, you get an empty array as result.

You have to fetch all the results first.

$rows = $query->fetchAll(PDO::FETCH_ASSOC);

Then loop the results as you want.

foreach($row in $rows){
    // do something
}

//again access results below
echo '<pre>', print_r($rows, true), '</pre>';

So the idea is to not to use the query object since its nature of using a cursor. Just retrieve the results, then use them.

answered Jun 25 '16 at 11:41

Imesha Sudasingha

3,162
1
22
32

description is better. +10 – Anant Kumar Singh Jun 25 '16 at 11:42
thanks for ur inputs buddy, is there a way through which fetch could somehow get reset? – Gaurav Jun 25 '16 at 12:40
http://stackoverflow.com/questions/15111681/reset-cursor-position-in-pdo http://stackoverflow.com/questions/9437214/resetting-array-pointer-in-pdo-results I'm afraid no. The links are some questions of resetting cursor – Imesha Sudasingha Jun 25 '16 at 13:13

PDO query returns blank Array

1 Answers1