Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

mysql - 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 
           )
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...