CODEWITHSUNDEEP
phpsqlforeachpdo
samyb8
samyb8

Reputation: 2598

FOREACH not preparing PDO statement at each iteration

I am trying to run a FOREACH with PDO but so far my code seems to iterate only once for the FOREACH and returns only the 1st value.

Here is what I have:

$colors = $_GET['color'];
$colors = explode(' ', $colors);
foreach ($colors as $color) 
    {
    $items = $con -> prepare("SELECT * FROM item_descr WHERE color_base1 = :colorbase1");
    $items -> bindValue(":colorbase1", $color);
    }
$items ->execute();
while($info = $items->fetch(PDO::FETCH_ASSOC)) 
{
echo $info['color_base1'];
}

Note that $colors contains more than 1 different color obtained from checkboxes and a jquery.

Thanks!

Upvotes: 1

Views: 1647

Answers (3)

George
George

Reputation: 1

Would it be possible for you to provide a print_r of the $_GET['color'] ?

The reason for this - I think that if you have spaces in the q string it will not progress any further that the 1st space therefore the foreach will only run only once.

Upvotes: 0

Lawrence Cherone
Lawrence Cherone

Reputation: 46660

Here try this:

<?php 
//Check your vars before setting!
$colors = (!empty($_GET['color'])?$_GET['color']:null);
$colors = explode(' ', $colors);

//Prepare the query once
$items = $con->prepare("SELECT * FROM item_descr WHERE color_base1 = :colorbase");
//Use bindParam then you can set $color later on
$items->bindParam(":colorbase", $color);

//Execute the query, iterating over each color
$results=array();
foreach ($colors as $color){
    $items->execute();
    //Fetch the result and store in $results
    $results[] = $items->fetchAll(PDO::FETCH_ASSOC);
}

//Loop through results
if(!empty($results)){
    foreach($results as $result) {
        foreach($result as $row){
            echo $row['color_base1'].'<br />';
        }
    }
}else{
    //empty
}
?>

Upvotes: 1

IMSoP
IMSoP

Reputation: 98015

Your code is nested wrong: you want to prepare the query once, and execute it multiple times, but you're doing the opposite.

Rather than:

foreach ( ... )
{
    prepare( ... );
    bind( ... );
}
execute();

You need:

prepare( ... );
foreach ( ... )
{
    bind( ... );
    $results[] = execute();
}

Alternatively, you could use an SQL IN clause to build one query rather than having the overhead of going back and forth to the database multiple times.

Upvotes: 3

Related Questions