CODEWITHSUNDEEP
phpmysqlsqlpdoinsert
RobertPorter
RobertPorter

Reputation: 552

MySQL INSERT issue with foreign keys in 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?

Upvotes: 1

Views: 240

Answers (2)

spencer7593
spencer7593

Reputation: 108450

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

Upvotes: 1

Jay Blanchard
Jay Blanchard

Reputation: 34416

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.

Upvotes: 2

Related Questions