I noticed that Kirby’s database class can’t reference the same bind parameter multiple times in a single query.
$query
->where('match (name) against (:name_param) > 0')
->order('match (name) against (:name_param) desc');
Doing this returns an empty collection. No errors, just an empty collection - even though the query returned by ->debug() executes correctly when copy/pasted into a SQL client.
I don’t know much about PDO - is this expected behavior?
If :param is only referenced once in the query, results are as expected with a name column, a param column, and a result for every record in the table. (Every resulting object has the same value in the param field of course - whatever the binding was.)
As soon as the param is mentioned a second time anywhere in the query (a second time in the select statement like this example, again the the where or order-by clauses, etc.) an empty collection is returned with no errors.
I’ve done some research and it is indeed a limitation of PDO or actually MySQL:
MySQL doesn’t support named parameters (:name_param), so PDO replaces the named parameters with ? characters and passes the bound parameter values as a simple ordered list. This is why the same parameter name cannot be used twice as the value would need to be transmitted to the database twice:
You must include a unique parameter marker for each value you wish to pass in to the statement when you call PDOStatement::execute(). You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.
– https://www.php.net/manual/en/pdo.prepare.php
There are two workarounds for this:
Supply the parameter value twice with different and unique names.
Use $database->connection()->setAttribute(PDO::ATTR_EMULATE_PREPARES, true); to make PDO emulate the named parameters. PDO will then escape the parameter values locally and bake them right into the query instead of passing them as a raw list to the database. As far as I read, PDO’s emulation should be pretty good by now, but of course it’s going to be less reliable than native prepared statements.