php - MySQL PDO Name-Value Prepared Statement Using Last Parameter Only
Get the solution ↓↓↓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()...
Answer
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!
Answer
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:
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: filter_sanitize_string deprecated
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.