CODEWITHSUNDEEP
phpmysqlpdo
Carl Barrett
Carl Barrett

Reputation: 229

PHP PDO SQL only returning one row of data instead of all rows

I am using PHP PDO extension to create a list of all items from a DB category table. The expected results are not correct, only one row of data returned instead of all rows which is expected. By running the same SQL statement within the phpMyAdmin console I get the expected results for all rows of data. I need to know what I am overlooking with pdo.

Current code:

    $catId = 0;   

    $sql = "SELECT *
            FROM category
            WHERE cat_parent_id = :catId
            ORDER BY cat_name";

    $_stmt = $this->_dbConn->prepare($sql);
    $_stmt->bindParam(":catId", $catId, PDO::PARAM_INT);  
    $_stmt->execute();

    $rows = $_stmt->fetch(PDO::FETCH_ASSOC);    

    // display PDO result - only getting one row instead of all rows.       
    print_r($rows);

    Array ( [cat_id] => 3 
            [cat_parent_id] => 0 
            [cat_name] => Shop 
            [cat_description] => Seminars 
            [cat_image] => c72e.gif 
            )

    // NOTE: By running the same SQL within the phpMyAdmin 
    // console I get the expected results with all rows.
    Array ( [cat_id] => 3 
            [cat_parent_id] => 0 
            [cat_name] => Shop 
            [cat_description] => Seminars 
            [cat_image] => c72e.gif 
           ),
          ( [cat_id] => 1 
            [cat_parent_id] => 0 
            [cat_name] => Site Map 
            [cat_description] => List content links 
            [cat_image] => c83b.gif 
           )

Upvotes: 0

Views: 1880

Answers (2)

Decent Dabbler
Decent Dabbler

Reputation: 22783

PDOStatement::fetch() will only return 1 row at a time.

You could use PDOStatement::fetchAll():

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

or create a loop where you keep calling PDOStatement::fetch() until it returns false:

$rows = array();
while( $row = $_stmt->fetch(PDO::FETCH_ASSOC) ) {
  $rows[] = $row;
}

But the latter example is a bit redundant, if you want to get all rows at once anyway, unless there are some memory concerns you want to address.

Upvotes: 4

slon
slon

Reputation: 1040

You have to use fetchAll like below:

$rows = $_stmt->fetchAll();
print_r($rows);

Upvotes: 0

Related Questions