PHP PDO SQL 只返回一行数据而不是所有行

2021-12-26 00:00:00 php mysql pdo

我正在使用 PHP PDO 扩展来创建数据库类别表中所有项目的列表.预期结果不正确，只返回一行数据而不是全部预期的行.通过在 phpMyAdmin 控制台中运行相同的 SQL 语句，我得到了预期的结果所有数据行的结果.我需要知道我在使用 pdo 时忽略了什么.

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.

当前代码:

    $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 
           )

推荐答案

PDOStatement::fetch() 一次只返回 1 行.

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

您可以使用 PDOStatement::fetchAll():

You could use PDOStatement::fetchAll():

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

或创建一个循环，在该循环中不断调用 PDOStatement::fetch() 直到它返回 false:

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.

相关文章