duanqing3026 2016-02-10 20:15
浏览 93
已采纳

MySQL INSERT与PHP中的外键问题

I am using PDO to insert values into my table like this:

$query = "INSERT INTO Maps(meetingId, street, city, code, centerLat, centerLon, zoom, markerVisible, markerLat, markerLon) VALUES (:meetingId, :street, :city, :code, :centerLat, :centerLon, :zoom, :markerVisible, :markerLat, :markerLon)";

$paramArr = array(
    ":meetingId" => intval($mapInfo['meetingId']),
    ":street" => $mapInfo['street'],
    ":city" => $mapInfo['city'],
    ":code" => $mapInfo['code'],
    ":zoom" => $mapInfo['zoom'],
    ":centerLat" => $mapInfo['center']['lat'],
    ":centerLon" => $mapInfo['center']['lon'],
    ":markerVisible" => $mapInfo['marker']['visible'],
    ":markerLat" => $mapInfo['marker']['lat'],
    ":markerLon" => $mapInfo['marker']['lon']
);

$db = $this->databaseManager ->getDB();
$query = $db->prepare($query);

foreach ($paramsArray as $key => $value) {
    $query->bindParam($key, $value, PDO::PARAM_INT);
}

When I execute this query I get:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint

meetingId is foreign key but I add a key that I am 100% sure exists in te relevant table. This key is of type int.

On the other hand if I remove the first variable and type in proper id in its place (that again I am sure exists) I get

SQLSTATE[HY000]: General error

Am I missing something here?

  • 写回答

2条回答 默认 最新

  • dousao9569 2016-02-10 20:21
    关注

    In your code here:

    foreach ($params as $key => $value) {
        $query->bindParam($key, $value, PDO::PARAM_INT);
    }
    

    You are declaring that every value you're passing is an integer (PDO::PARAM_INT) when that is just not the case, hence the integrity constraint error (the integer didn't match the data in the other table) and then the general error when you "fixed" meetingId.

    To fix this you do not need the loop to bind, just execute the query with the array:

    $queryResults = $db->prepare($query);
    $queryResults->execute($paramArr);
    

    From Demystifying PHP PDO:

    YOU MUST pass all values to bind in an array to PDOStatement->execute() or you have to bind every value with PDOStatement->bindValue(), then call PDOStatement->execute() with no parameters.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 滑块验证码拖动问题悬赏
  • ¥15 Wanted but not invoked:Actually, there were zero interactions with this moc
  • ¥20 怎么驱动tb6612
  • ¥15 Arcgis 3D效果点要素
  • ¥15 在执行yolo训练脚本的时候,没有报错,在扫描val这一步后就推出执行了
  • ¥15 delphi开发的WEBSERVER改用HTTPS协议
  • ¥15 pic16f877A单片机的计数器proteus仿真失效
  • ¥100 调查 Vitis AI 中验证 .xmodel 量化后的正确性
  • ¥30 codelite全屏时file、setting那一行消失了
  • ¥15 gazebo-rviz教程