使用PDO进行MySQL全文搜索不起作用

Question

UPDATE:

Sorry everyone, it seems that I'm quite tired :) I just missed execute() part :)

---

I'm bumped into some problem. Please look at code bellow.

$searchTerm = 'chr*';

$sql = "SELECT user_id FROM user
    WHERE MATCH (user_name) AGAINST (? IN BOOLEAN MODE)";

$statement = self::$replicaDB->prepare($sql);
$statement->bindValue(1, $searchTerm);
$db_data = $statement->fetchAll(PDO::FETCH_ASSOC);

This query returns empty list (no errors, no exception)

But if I place search term in query directly then everything is working fine.

$sql = "SELECT user_id FROM user
    WHERE MATCH (user_name) AGAINST ('chr*' IN BOOLEAN MODE)";

$statement = self::$replicaDB->query($sql);
$db_data = $statement->fetchAll(PDO::FETCH_ASSOC);

What am I doing wrong?

Answer 1

You are losing the quotes when you are binding your parameter.

Do it like this :

$searchTerm = 'chr*';

$sql = "SELECT user_id FROM user
    WHERE MATCH (user_name) AGAINST ('?' IN BOOLEAN MODE)";

$statement = self::$replicaDB->prepare($sql);
$statement->bindValue(1, $searchTerm);
$db_data = $statement->fetchAll(PDO::FETCH_ASSOC);

or like this

$searchTerm = '\'chr*\'';

$sql = "SELECT user_id FROM user
    WHERE MATCH (user_name) AGAINST (? IN BOOLEAN MODE)";

$statement = self::$replicaDB->prepare($sql);
$statement->bindValue(1, $searchTerm);
$db_data = $statement->fetchAll(PDO::FETCH_ASSOC);