PDO查询返回空白数组

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...

Answer 1

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.