PDO query not returning results in PHP foreach loop

Question

I have a function (below) which isn't returning a result set from a PDO query inside a foreach loop. Not sure what could be the problem. To explain a bit, I'm running PHP v5.6.3 and the $csv string passed to the function is something like:

$csv = "red, brown, blue";

After the str_getcsv is ran, print_r($colors) works as expected. I've tried preparing the query and binding values inside or outside the loop with no success, and if I pull the query outside of the loop (and run it once) it works.

function generateColors($csv) {
    global $db; // Connect DB
    $colors = str_getcsv($csv, ","); //Converts the CSV to an array
    $query = $db->prepare("SELECT * FROM colors WHERE slug = :slug");
    foreach ($colors as $slug) {
        $query->bindValue(':slug', $slug, PDO::PARAM_STR);
        $query->execute();
        $result = $query->fetch(PDO::FETCH_ASSOC);
        echo "COLOR: " .$slug;
        echo "<br>";
        var_dump($result);
    }
}

The output (below) shows that the loop is working, just the query seems to stop returning values after 1 iteration. Any help on why I'm getting a bool(false) instead of an array set like [column 1]=>value 1, [column 2]=>value 2, etc., would be greatly appreciated.

COLOR: red
array -> [column]=>value, [column]=value, etc. // returned as expected.
COLOR: brown
bool(false)
COLOR: blue
bool(false)

THE PROBLEM IS you are thinking that $slug of foreach will automaticlly equalizie $slug of bindvalue which is not going to happen. you need to put your bindValue code in foreach and then need to check — Anant Kumar Singh
Edited function above to put bindValue back inside loop. Unfortunately, still resulting in a bool(false). — PanicRev
Actually... putting it back inside the loop, I've noticed it returns the first result set properly (for the first loop run), however, all subsequent values in the loop are returning the bool(false). — PanicRev
that means you have record for first slug but not for others? I think i am clear? — Anant Kumar Singh
Correct, my output is like this now: COLOR: red array(11) { ["slug"]=> string(15) "red" ["name"]=> string(15) "Red", etc... COLOR: brown bool(false) etc.. — PanicRev

Unknown Unknown · Accepted Answer · 2015-05-12T18:31:48

You should bind the params inside the foreach. Before the foreach your variable $slug is not defined.

function generateColors($csv) {
    global $db; // Connect DB
    $colors = str_getcsv($csv, ","); //Converts the CSV to an array
    $query = $db->prepare("SELECT * FROM colors WHERE slug = :slug");
    foreach ($colors as $slug) {
        $query->bindValue(':slug', $slug, PDO::PARAM_STR);
        $query->execute();
        $result = $query->fetch(PDO::FETCH_ASSOC);
        echo "COLOR: " .$slug;
        echo "<br>";
        var_dump($result);
    }
}

PDO query not returning results in PHP foreach loop

1 Answers