CODEWITHSUNDEEP
phppdo
hantoren
hantoren

Reputation: 1225

Executing PDO statement with more parameters than defined

I have an array that contains more variables than necessary. Is there any way to avoid the error: Invalid parameter number

<?php
$pdo = new PDO('mysql:host=localhost;dbname=databasename', 'username', 'password');

$user = [
  "firstname" => "John",
  "lastname" => "Doe",
  "street" => "abcdestreet 123"
];

$statement = $pdo->prepare("SELECT * FROM users WHERE firstname = :firstname AND lastname= :lastname");
$statement->execute($user);   
while($row = $statement->fetch()) {
   echo $row['firstname']." ".$row['lastname']."<br />";
   echo "Email: ".$row['email']."<br /><br />";
}
?>

Upvotes: 0

Views: 63

Answers (1)

Don&#39;t Panic
Don&#39;t Panic

Reputation: 41810

One thing you could try is to filter the input array to only include keys that match parameter names in the prepared statement.

$sql = "SELECT * FROM users WHERE firstname = :firstname AND lastname= :lastname";

$values = array_filter($user, fn($k) => preg_match("/:$k\b/", $sql), ARRAY_FILTER_USE_KEY);

$statement = $pdo->prepare($sql);
$statement->execute($values);

This will work for your example, and I think should work in general for what you're trying to do. The regex should be adjusted to not match things in quotes that look like parameters, which I don't know how to do off the top of my head, but I'm sure there are some examples of things like that around here. The odds of accidentally having something like that that happens to match one of your keys don't seem too high, though. If there are some other instances of things it shouldn't match that I haven't thought of, I think the worst thing that could happen is that you'll get the same error you're already getting.

Upvotes: 2

Related Questions