Using PHP with PDO to access a MyISAM table...
This case fails:
SELECT
id,
title,
content,
(MATCH(title) AGAINST(:fulltext IN BOOLEAN MODE)*10)+(MATCH(content) AGAINST(:fulltext IN BOOLEAN MODE)*3) AS relevancy
FROM
mytable
WHERE
enabled = 1
AND `date` >= CURRENT_DATE
AND MATCH (title, content) AGAINST (:fulltext IN BOOLEAN MODE)
ORDER BY relevancy DESC, `date` ASC
LIMIT 50
$stmt->bindParam(":fulltext", $fulltext);
$stmt->execute();
Error:
exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number'
Now I reworked the code into this:
SELECT
id,
title,
content,
(MATCH(title) AGAINST(? IN BOOLEAN MODE)*10)+(MATCH(content) AGAINST(? IN BOOLEAN MODE)*3) AS relevancy
FROM
mytable
WHERE
enabled = 1
AND `date` >= CURRENT_DATE
AND MATCH (title, content) AGAINST (? IN BOOLEAN MODE)
ORDER BY relevancy DESC, `date` ASC
LIMIT 50
$stmt->execute(Array($fulltext, $fulltext, $fulltext));
And this works.
The question: Can anyone explain why this is? Why does binding :fulltext fail in the first case?
Using the same binding multiple times works in other, similar statements that access a Postgres DB instead of the MariaDB MyISAM table:
WHERE customer.first_name ILIKE :term
OR customer.city ILIKE :term
OR customer.street ILIKE :term
OR customer.zip ILIKE :term
OR customer.email ILIKE :term
OR customer.email_private ILIKE :term
OR customer.company_email ILIKE :term