PDO only fetching single row

Question

I am trying to fetch all rows with the Id that is taken from getting the cat_id:

<?php require_once '../db_con.php'; 

if(!empty($_GET['cat_id'])){
    $doc = intval($_GET['cat_id']);
try{
    $results = $dbh->prepare('SELECT * FROM cat_list WHERE cat_id = ?');
    $results->bindParam(1, $doc);
    $results->execute();

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

    $doc = $results->fetch(PDO::FETCH_ASSOC);    
    if($doc == FALSE){

        echo '<div class="container">';
        echo "<img src='../img/404.jpg' style='margin: 40px auto; display: block;' />";
        echo "<h1 style='margin: 40px auto; display: block; text-align: center;' />Oh Crumbs! You upset the bubba!</h1>";
        echo '<a href="userList.php"  style="margin: 40px auto; display: block; text-align: center;">Get me outta here!</a>';
        echo'</div>';
        die();
    }
}

?>

If I just use:

$doc = $results->fetch(PDO::FETCH_ASSOC);

It fetches a single row as expcted

However if I use:

$doc = $results->fetchAll(PDO::FETCH_ASSOC);

I get the following error?

Notice: Undefined index: doc_id in /Applications/MAMP/htdocs/dashboardr v3.1.7/catView.php on line 126

Notice: Undefined index: doc_title in /Applications/MAMP/htdocs/dashboardr v3.1.7/catView.php on line 126

But if I var_dump($doc);

It does return the values:

array(2) { [0]=> array(3) { ["doc_title"]=> string(5) "dsfsd" ["cat_no"]=> string(1) "4" ["doc_id"]=> string(2) "72" } [1]=> array(3) { ["doc_title"]=> string(14) "Adams Test Doc" ["cat_no"]=> string(1) "4" ["doc_id"]=> string(3) "120" } }

I am really confused?

UPDATE

<?php include 'header.php'; ?>

<?php require_once '../db_con.php'; 

if(!empty($_GET['cat_id'])){
    $cat = intval($_GET['cat_id']);
try{
    $results = $dbh->prepare("SELECT doc_list.doc_title, doc_list.cat_no, doc_id FROM doc_list WHERE cat_no = ?");
    $results->bindParam(1, $cat);
    $results->execute();

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

    $doc = $results->fetch(PDO::FETCH_ASSOC);    
    if($doc == FALSE){

        echo '<div class="container">';
        echo "<img src='../img/404.jpg' style='margin: 40px auto; display: block;' />";
        echo "<h1 style='margin: 40px auto; display: block; text-align: center;' />Oh Crumbs! You upset the bubba!</h1>";
        echo '<a href="userList.php"  style="margin: 40px auto; display: block; text-align: center;">Get me outta here!</a>';
        echo'</div>';
        die();
    }
}

?>

<h3 class="subTitle"><i class="fa fa-file-text"></i> </span>Document List</h3>  

   <p><?php 

   var_dump($doc);

   echo '<a href="docView.php?doc_id='.$doc["doc_id"].'">'.$doc['doc_title'].'</a>' ?></p>


   I WANT ALL MY RESULTS LISTED HERE

</div>
</div>

If you do `$doc = $results->fetchAll(PDO::FETCH_ASSOC);`, then you need to loop over your `$doc` - `foreach($doc as $values){ ....`. (notice the `array(2)`, that tells you that `$doc` is an array, so `$doc['doc_id']` would actually be `$doc[0]['doc_id']` — Sean, May 12 '15 at 21:42
you have no GET array for `doc_id` as per `="docView.php?doc_id` - this `$cat = intval($_GET['cat_id']);` should probably be `$cat = intval($_GET['doc_id']);` same for `if(!empty($_GET['cat_id'])){` — Funk Forty Niner, May 12 '15 at 21:54
@Fred-ii- That url works and show the doc when I click on it — PhpDude, May 12 '15 at 21:55

Rizier123 · Accepted Answer · 2015-05-12T21:48:23.247

4

Because with fetch() you get always one row, so you have to put it into a loop like below to iterate through all rows:

while($doc = $results->fetch(PDO::FETCH_ASSOC)) {
    //your code
}

And with fetchAll() you get all rows at once into one single array, so you have to iterate through the array e.g.

$doc = $results->fetchAll(PDO::FETCH_ASSOC);  
foreach($doc as $v) {
    //your code
}

edited May 12 '15 at 21:48

answered May 12 '15 at 21:43

Rizier123

58,877
16
101
156

I think OP was confused about the fact that `fetchAll()` does contain all the columns there, but didn't notice that it's a zero-indexed multidimensional array. – Mike May 12 '15 at 21:44
@Mike yeah, I hope my answer helps him to understand what's going on – Rizier123 May 12 '15 at 21:46
When I wrap the code in the foreach loop I get a blank screen – PhpDude May 12 '15 at 21:49
@phpcoder What do you do in your foreach loop? Just do like `$doc = $results->fetchAll(PDO::FETCH_ASSOC); foreach($doc as $v) { echo $v["doc_title"]; //What you need/want }` – Rizier123 May 12 '15 at 21:50
@Rizier123 I will show you my entire page in my question – PhpDude May 12 '15 at 21:51
@phpcoder See: http://stackoverflow.com/questions/12769982/reference-what-does-this-error-mean-in-php/12772851#12772851 – Mike May 12 '15 at 21:52
@phpcoder Now wrap your echo statement: `echo ' – Rizier123 May 12 '15 at 21:55
Ok so I am getting further along, when I do this
I now get: Warning: Illegal string offset 'doc_title' in /Applications/MAMP/htdocs/dashboardr v3.1.7/catView.php on line 127 E Warning: Illegal string offset 'doc_title' in /Applications/MAMP/htdocs/dashboardr v3.1.7/catView.php on line 127 3 Warning: Illegal string offset 'doc_title' in /Applications/MAMP/htdocs/dashboardr v3.1.7/catView.php on line 127 5 – PhpDude May 12 '15 at 22:02
@phpcoder You forgot to change your `fetch()` to `fetchAll()` in this line: `$doc = $results->fetch(PDO::FETCH_ASSOC);` – Rizier123 May 12 '15 at 22:04
Fab that works perfectly, I understand what is happening here now – PhpDude May 12 '15 at 22:06

PDO only fetching single row

1 Answers1