1

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
MiK
  • 918
  • 4
  • 16
  • the reason for *why* is that the underlying DBMS does not allow for multiple keywords. the why of *that* why is probably buried in the source code and far too obscure for me to unravel. – Félix Adriyel Gagnon-Grenier Dec 04 '19 at 18:10

0 Answers0