mysql - 循环中的pdo绑定(bind)参数

Question

我有以下sql语句:

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

我根据对象“位置”动态编辑，它可能有多个参数(其中一些可能为空，因此被省略)

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

当我回显查询(echo $sql)时，它看起来就像我想要的那样，并且所有绑定(bind)参数都是正确的，但是当我运行查询时，位置参数的所有数据库列都仅使用位置对象的最后一个值进行更新，例如:

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

使用该对象执行查询后，数据库中纬度、经度和城市的列均填充为“慕尼黑”。你能告诉我，我做错了什么吗？

+var_dump($sql) ->

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

Answer 1

虽然没有阅读整个问题，但引起了我的注意

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

引用自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.

很可能是一个原因。