php 相当于 mysql_fetch_array 的 PDO

PDO equivalent of mysql_fetch_array

提问by Jason

I am struggling with the PDO equivalent of the following query which calculated how many new items there are in the queue and works out how many weeks to complete them, thereby giving me a workstack timescale:-

我正在努力使用以下查询的 PDO 等效项，该查询计算队列中有多少新项目并计算出完成它们的周数，从而为我提供了一个工作堆栈时间表：-

//count new to be made
$new = "SELECT FLOOR(SUM(TotalNew) / 7) AS Weeks FROM 
(
SELECT YEAR( date_ready ) , MONTHNAME( date_ready ) , 
STATUS , COUNT( 
STATUS ) AS TotalNew
FROM new
WHERE 
(STATUS =  'new'
OR STATUS =  'progress') 
GROUP BY YEAR( date_ready ) , MONTHNAME( date_ready ) , 
STATUS ORDER BY YEAR( date_ready ) , MONTH( date_ready ) 
) Total";
$count = mysql_query($new) or die(mysql_error());

while($row = mysql_fetch_array($count)) {
$weeks = $row['Weeks'];
}

Where I'm up to is this....

我要去的地方是这个......

//count new to be made 
$new = "SELECT FLOOR(SUM(TotalNew) / 7) AS Weeks FROM 
(
SELECT YEAR( date_ready ) , MONTHNAME( date_ready ) , 
STATUS , COUNT( 
STATUS ) AS TotalNew
FROM new
WHERE 
(STATUS =  'new'
OR STATUS =  'progress') 
GROUP BY YEAR( date_ready ) , MONTHNAME( date_ready ) , 
STATUS ORDER BY YEAR( date_ready ) , MONTH( date_ready ) 
) Total";


//get count data fromdb
$stmt = $dbLink->prepare($new);
$stmt->execute();

If I add $count = $stmt->fetchAll();

如果我添加 $count = $stmt->fetchAll();

and dump the variable, I get array(1) { [0]=> array(2) { ["Weeks"]=> string(2) "16" [0]=> string(2) "16" } }

并转储变量，我得到 array(1) { [0]=> array(2) { ["Weeks"]=> string(2) "16" [0]=> string(2) "16" } }

If I replace

如果我更换

$count = $stmt->fetchAll();

with

和

while ($row = $stmt->fetchAll())  {
echo "about to print";
echo "<br>";
print_r($row);
echo "<br>";
echo $row['Weeks'];
echo "<br>";
echo "printed";
}

I get a different variation of the array - Array ( [0] => Array ( [Weeks] => 16 [0] => 16 ) ), but not the output I want which is the number relating to Weeks from the query. I've tried error checking with the various echo / print_r to try and see where the outputs match expected output.

我得到了数组的不同变体 - Array ( [0] => Array ( [Weeks] => 16 [0] => 16 ) )，但不是我想要的输出，这是与查询中的周数相关的数字。我尝试使用各种 echo / print_r 进行错误检查，以尝试查看输出与预期输出匹配的位置。

The query works fine, and the original mysql_query version also works, so I'm obviously misunderstanding how PDO handles arrays and how to pull an item out from within the array.

查询工作正常，原始的 mysql_query 版本也工作，所以我显然误解了 PDO 如何处理数组以及如何从数组中拉出一个项目。

I've looked at How to use PDO to fetch results array in PHP?and how to properly use while loop in PDO fetchAlland tried various combinations to no avail. To be honest, even if something randomly worked, I'd prefer to know why and am beginning to question my understanding of arrays.

我看过 如何使用 PDO 在 PHP 中获取结果数组？以及 如何在 PDO fetchAll 中正确使用 while 循环并尝试了各种组合都无济于事。老实说，即使某些东西随机起作用，我也更愿意知道原因并开始质疑我对数组的理解。

As always, I'd be grateful for a pointer or two please?

与往常一样，我会很感激一两个指针吗？

Many thanks, Jason

非常感谢，杰森

回答by Joel Lubrano

I think you are looking for:

我认为您正在寻找：

while($row = $stmt->fetch(/* PDO::FETCH_ASSOC */)) {
    // do loop stuff
}

PDO::fetchAll()returns an associative array of all of the query results (a 2-D array). This is not recommended for large result sets according to the PHP docs. PDO::fetch()returns just one row from a result set and mimics mysql_fetch_array(). See http://php.net/manual/en/function.mysql-fetch-array.phpfor more details.

PDO::fetchAll()返回所有查询结果的关联数组（二维数组）。根据 PHP 文档，对于大型结果集不建议这样做。 PDO::fetch()从结果集中只返回一行并模仿mysql_fetch_array(). 有关更多详细信息，请参阅http://php.net/manual/en/function.mysql-fetch-array.php。

回答by Mike Willis

You probably will want to use:

您可能想要使用：

while ($row = $stmt->fetch(PDO::FETCH_ASSOC))  {
    echo "about to print";
    echo "<br>";
    print_r($row);
    echo "<br>";
    echo $row['Weeks'];
    echo "<br>";
    echo "printed";
}

the PDO::FETCH_ASSOC part makes the system return an associative array

PDO::FETCH_ASSOC 部分使系统返回一个关联数组

More on parameters - PHP PDO fetch()

更多关于参数 - PHP PDO fetch()

回答by Niet the Dark Absol

fetchAllreturns an array with all the results. So try:

fetchAll返回一个包含所有结果的数组。所以尝试：

foreach($stmt->fetchAll() as $row) {
    // ...
}

回答by Steve

You are after a single result, so just use fetchColumn:

您只需要一个结果，因此只需使用fetchColumn：

$stmt = $dbLink->prepare($new);
$stmt->execute();
$weeks = $stmt->fetchColumn();

http://php.net/manual/en/pdostatement.fetchcolumn.php

http://php.net/manual/en/pdostatement.fetchcolumn.php