PDO equivalent of mysql_fetch_array

Question

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:-

//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();

and dump the variable, I get 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.

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.

I've looked at How to use PDO to fetch results array in PHP? and how to properly use while loop in PDO fetchAll and 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.

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

Many thanks, Jason

Answer 1

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.php for more details.

Answer 2

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

More on parameters - PHP PDO fetch()

Answer 3

fetchAll returns an array with all the results. So try:

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

Answer 4

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

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

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