MySQL INSERT与PHP中的外键问题

Question

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?

Answer 1

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.