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?

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.

In addition to the issues identified in the answer from Jay Blanchard

PDO bindParam passes by reference, not by value.

So one possible fix for the problem is to to tweak this one line of code:

foreach ($paramsArray as $key => $value) {

to add a & character before $value, to make it a reference &$value

foreach ($paramsArray as $key => &$value) {
                                 ^

That should be sufficient to fix the code.

Or, you could just use the bindValue function rather than the bindParam function. That's what's happening in the answer from Jay Blanchard. (Passing the values as an array on the execute is equivalent to bindValue.)

Reference: http://php.net/manual/en/pdostatement.bindparam.php