Can't fetch any data with PDO's fetch or fetchAll function

Question

I've been at this for some time. Using PDO's documentation and based on older stackoverflow posts I feel I am doing this correct, however I can not figure out where my data is in the array returned by fetchAll. Any help would be appreciated.

In my DatabaseHandler class:

public function getStateList(){
    $root = "root";
    $rootpasswd = "password";
    $host = "localhost";
    try{
        $dbh = new PDO("mysql:host=$host;dbname=TESTDATA;", $root, $rootpasswd);
        $stmt = $dbh->prepare("SELECT id FROM state");
        if(!$stmt->execute()){
            //error
        }
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        return $result;
    }catch(PDOExeption $e){
        //catch exception
    }
}

Now when calling this method it returns an array of 52 id's which is what it's supposed to do however I can not get to this data. Why? Here is the code that calls the method.

<?php
    require("./Database/DatabaseHandler.php");

    $dbh = new DatabaseHandler();
    $stateCodes = $dbh->getStateList();
    $max = sizeof($stateCodes);

    error_log("Arraysize=".$max."\n", 3, "/testlog/data.dat");
    error_log("Position 0: ".$stateCodes[0]."\n", 3, "/testlog/data.dat");
    error_log("Position[0][0]: ".$stateCodes[0][0]."\n", 3, "/testlog/data.dat");
    error_log("Position[0][0][0]: ".$stateCodes[0][0][0]."\n", 3, "/testlog/data.dat");
?>

The first errorlog records 52. The second returns the word Array which makes me think it's a 2 dim array. The Third nothing. The Fourth Nothing.

I know I am using fetchAll wrong somehow but I can't figure out where or how. Thank you in advance.

Have you tried `print_r($stateCodes)`? – Jay Blanchard Sep 24 '14 at 16:12 — Jay Blanchard, Sep 24 '14 at 16:12

score 0 · Answer 1 · answered Sep 24 '14 at 16:13

0

You have used PDO::FETCH_ASSOC that will be an associative array.

But since you are fetching all, the array is 2 dimensional.

$stateCodes[0]['id'];

Alternatively you can loop this way:

foreach($stateCodes as $code){
    error_log("Code Id =" . $code['id']);
}

answered Sep 24 '14 at 16:13

meda

45,103
14
92
122

So how can I return just the id number? Will I always have to explicitly ask for id via ['id'] – seanr Sep 24 '14 at 16:50
this is what your query does, return the id of all states – meda Sep 24 '14 at 16:52
if you mean you want to access using numeric index then use `PDO::FETCH_BOTH` so you can do `$stateCodes[0][0]` but usually `FETCH_ASSOC` is more readable – meda Sep 24 '14 at 16:54

Can't fetch any data with PDO's fetch or fetchAll function

1 Answers1