CODEWITHSUNDEEP
phpmysqlarrayspdo
Jason
Jason

Reputation: 159

PDO equivalent of mysql_fetch_array

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

Upvotes: 4

Views: 36511

Answers (4)

Steve
Steve

Reputation: 20469

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

Upvotes: 2

Joel Lubrano
Joel Lubrano

Reputation: 606

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.

Upvotes: 12

Mike Willis
Mike Willis

Reputation: 1512

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

Upvotes: 6

Niet the Dark Absol
Niet the Dark Absol

Reputation: 324790

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

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

Upvotes: 3

Related Questions