CODEWITHSUNDEEP
phppdoforeach
user6100679
user6100679

Reputation:

Display all rows in column with foreach

I am trying to use foreach to echo every row in a column. But am getting:

Warning: Invalid argument supplied for foreach()

Here is my code:

<?php
$user = user;
$pass = pass;
$dbh = new PDO('mysql:host=localhost;dbname=onlyaskf_registered', $user, $pass);

$stmt = $dbh->prepare("SELECT COUNT(*), sum(price),part_number,location,price FROM products WHERE code = :usercode");
$stmt->bindParam(':usercode', $_SESSION['MM_Username']);

?>

<?php 
if ($stmt->execute()) {
    foreach($stmt->fetchColumn(2) as $row) {
        echo $row;
    }
}  
?>

With this code I can get the first row within the column:

<?php 
if ($stmt->execute()) {
    $row = $stmt->fetchColumn(2);
    echo $row;
}  
?>

Column 2 is my part_number column, if I format my php like above it gives me the first instance of that column's row. How do I get it to show each row in the column with foreach? Is that even possible or is there a better method to echo an entire column's rows?

UPDATE:

I changed my code to this but it is only giving me the first row's column value instead of every row:

<?php if ($stmt->execute()) {
while($val = $stmt->fetch()) { 
echo $val['part_number'];
}
}  ?>

Upvotes: 2

Views: 1918

Answers (2)

devpro
devpro

Reputation: 16117

After your update, code is not giving you first row, it's giving you one row just because of SUM() function in your query:

SELECT COUNT(*), sum(price),part_number,location,price FROM products

Note that, SUM() will returns the total sum of a numeric column.

So, in your case, you need to remove sum(price) function from your query than you will get the all rows.

You can use PHP for this purpose something like:

$sum = 0;
while($val = $stmt->fetch()) { 
    $sum += $val['price'];
}
echo $sum; // will return sum of all rows.

Upvotes: 1

Marc B
Marc B

Reputation: 360572

fetchColumn() doesn't return an array, so you can NOT foreach on it. You use while instead:

while($val = $stmt->fetchColumn(2)) { 
    echo $val;
}

But note that this FAILS if you're retrieving a boolean field. As per the note in the docs:

Note:

PDOStatement::fetchColumn() should not be used to retrieve boolean columns, as it is impossible to distinguish a value of FALSE from there being no more rows to retrieve. Use PDOStatement::fetch() instead.

Upvotes: 1

Related Questions