Using Named Parameters with Pdo for Like

PDO Parameterized Query - Reuse named placeholders?

PDO::prepare states that "you cannot use a named parameter marker of the same name twice in a prepared statement", so I guess that's a no then.

How do I create a PDO parameterized query with a LIKE statement?

Figured it out right after I posted:

$query = $database->prepare('SELECT * FROM table WHERE column LIKE ?');
$query->execute(array('value%'));

while ($results = $query->fetch())
{
echo $results['column'];
}

In PHP PDO, how can I bind named parameters of a prepared statement without knowing their names?

There's absolutely no reason to use bindParam.

If your SQL has named placeholders then your array must be associative. You need to call it like this:

queryDB($query, ['name' => $name, 'age' => $age]);

You could then loop with foreach($params as $key => $value) and use bindParam but as I said, there's absolutely no reason to use it.

Instead, pass the array to execute.

function queryDB(PDO $dbh, string $query, ?array $param = null)
{
$stmt = $dbh->prepare($query);
$stmt->execute($param);
return $stmt->fetchAll();
}

P.S. You can even remove the if statement and the call to query. This method does the same thing as prepare and execute. There's no reason to have a special case like this in your code.

PDO statements with named parameters VS question mark parameters

The difference between named an unamed parameters is that with unnamed parameters you'll have to take care about the order in which they will be bound to the query.

Especially in your example unnamed params will fit very good as it eases the function call.


Further note that you won't need to call return $this; in a constructor method.

implement LIKE query in PDO

You have to include the % signs in the $params, not in the query:

$query = "SELECT * FROM tbl WHERE address LIKE ? OR address LIKE ?";
$params = array("%$var1%", "%$var2%");
$stmt = $handle->prepare($query);
$stmt->execute($params);

If you'd look at the generated query in your previous code, you'd see something like SELECT * FROM tbl WHERE address LIKE '%"foo"%' OR address LIKE '%"bar"%', because the prepared statement is quoting your values inside of an already quoted string.

PDO bindParam() with multiple named parameters

See PDO::prepare

You cannot use a named parameter marker of the same name twice in a prepared statement


Related Topics



Leave a reply



Submit