php - MySQL PDO Name-Value Prepared Statement Using Last Parameter Only ← (PHP, MySQL)

As titled, I'm using MySQL PDO with a prepared statement, and when executing, I'm getting a single value (the last supplied value) pushed into all fields.

Table looks like so:

• id (int - auto-increment)
• a_id (int)
• b_id (int)

INSERT looks like this:

INSERT INTO my_table(a_id, b_id) 
VALUES (:a_id, :b_id)

Code to insert looks like this...

$stmt = $conn->prepare($sql);

foreach($params as $k => $v) {
    $stmt->bindParam(
        $k,
        $v
    );
}

$stmt->execute();

The statement successfully inserts a value (which confirms $conn is present, active and open and the statement is set up and parameters bound).

I have confirmed the values by debugging inside the foreach, where I get two parameters (in $params) of ":a_id" => 1, ":b_id" => 9999...

My record goes in as ({new_id}, 9999, 9999) though.*

This means it sees and uses ":b_id" => 9999, but appears to drop or overwrite ":a_id" => 1...

I thought maybe the underscore was a problem (but apparently several people have reviewed source in the past to indicate underscore is fine... though hyphen is not).

I'm not reusing parameters (my understanding is one use allowed per prepared statement)...

Is there some named parameter issue I'm missing here? Since it's basically ->bindParam() to ->execute(), there's not a lot of room for me troubleshoot.

I'm trying to address speed issues with ORM-based data access while using the Fat Free Framework, but I can't think that there's interference there.

For reference, this is running under PHP 5.5.8 / Windows / IIS.

EDIT:*

I can confirm that moving to positional parameters is doing the same thing.

INSERT INTO my_table(a_id, b_id) 
VALUES (?, ?)

Code changed to...

$stmt = $conn->prepare($sql);

$i = 1;
foreach($params as $value) {
    $stmt->bindParam(
        $i,
        $value
    );
    $i++;
}

$stmt->execute();

To clarify how the $params array is being set... things are being passed through to this code (which is the heart of an abstract db handler), and the array is manually constructed...

i.e.

$results = \XX\DB::do_cmd(
    $db, 
    self::SQL_INSERT, 
    array(
        ':a_id' => intval($this->a_id),
        ':b_id' => intval($this->b_id),
    )
);

Still got ({new_id}, 9999, 9999)...

NOTE: To remove confusion, in addition to going to a positional based pass, I also hardcoded values to see what I'd get...

$results = \XX\DB::do_cmd(
    $db, 
    self::SQL_INSERT, 
    array(
        1,
        1234,
    )
);

My record came out ({new_id}, 1234, 1234). Same problem, differing values.

I have the feeling there's a "gotcha" here... but I have no idea what it is.

The interesting thing, is that I double check the table for an existing record before the INSERT based on those two values (to prevent duplication) and the check correctly identifies that the record is there... despite it being wrong in the database... which means SELECT and INSERT are doing the same thing with the parameters (though not all that surprising, since parameter handling is the same).

EDIT2:

Update to note as solved. Using...

$stmt->execute($params); // scrap the foreach nonsense...

bindValue() rather than bindParam() is also appropriate.

NOTE: I was working from the following PHP documentation (http://php.net/manual/en/pdo.prepared-statements.php) which doesn't differentiate bindValue() vs bindParam()...

Solution:

Thanks for all the help everybody!

I went with Michael's solution, but tested Ryan's too.

i.e.

Update to note as solved. Using...

$stmt->execute($params); // scrap the foreach nonsense...

bindValue() rather than bindParam() is also appropriate.

To wrap things up, as per Ryan's comment, I'm pushing an answer out.

Thanks again!

Solution:

The variable has to be call-by-reference:

$stmt = $conn->prepare($sql);

foreach($params as $k => &$v) {
    $stmt->bindParam(
        $k,
        $v
    );
}
$stmt->execute();

See the first user-contributed comment here:

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