CODEWITHSUNDEEP
mysqlpdo
user1762087
user1762087

Reputation: 460

pdo binding parameters in loop

I have following sql statement:

$sql = "UPDATE houses SET title=:title ";

which I dynamically edit according to object "location", which could have several paramaters (some of them could be null, thefore they are omitted)

//are there any location parameters which need to be added to query?
if (isset($house->location)){
    //go through them and add them to query
    foreach ($house->location as $key=>$locationParameter) {
         $sql.=','.$key.'=:'.$key;
        }
     //finish query
     $sql.=" WHERE id=:id";
     $stmt = $db->prepare($sql);  
     //bind all defined location parameters to query    
     foreach ($house->location as $key=>$locationParameter) {
         $stmt->bindParam($key, $locationParameter);
        }
     } else {
             //there are none location parameters, so prepare just the original query with title and id
             $sql.=" WHERE id=:id";
             $stmt = $db->prepare($sql);  
        }
//and finally bind the required parameters      
$stmt->bindParam("title", $house->title);
$stmt->bindParam("id", $id);
$stmt->execute();

When I echoed the query (echo $sql) it looked just as I want and also all binded parameters were right, BUT when I run the query all database columns for location parameters are updated just with the last value from location object, for example:

 $house->location->lat is 25.5
 $house->location->lon is 28.755
 $house->location->city is munich

After execution of query with this object, the columns in DB for lat, lon, and city are all filled with "munich". Could you tell me, what am I doing wrong?

+var_dump($sql) ->

string 'UPDATE houses SET title=:title,lat=:lat,lon=:lon,city=:city WHERE id=:id'

Upvotes: 0

Views: 113

Answers (1)

Your Common Sense
Your Common Sense

Reputation: 157839

without reading entire question though, just caught my eye

the columns in DB for lat, lon, and city are all filled with "munich".

quoting from PDO tag wiki:

If you don't know if you need bindValue() or bindParam(), go for the former. bindValue() is less ambiguous and has lesser side effects.

most likely a cause.

Upvotes: 1

Related Questions