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

Question

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 
           )

fetch() or fetchAll()? the former returns one row, the latter all rows. Use fetch in a loop — Professor Abronsius
– Professor Abronsius, Commented Nov 27, 2017 at 0:26

Decent Dabbler · Accepted Answer · 2017-11-27 00:31:32Z

4

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.

edited Nov 27, 2017 at 0:31

answered Nov 27, 2017 at 0:26

Decent Dabbler

22.8k11 gold badges77 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carl Barrett Over a year ago

The fetch() was the bummer! Everything is now working as expected. Thanks for the quick response.

slon · Accepted Answer · 2017-11-27 00:26:52Z

0

You have to use fetchAll like below:

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

answered Nov 27, 2017 at 0:26

slon

1,0501 gold badge8 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related