I am trying to implement MySQL full text search in my app.
But I get an empty array as a result only if I use a prepared statement. If I simply put php variable in SQL query it works just fine but it is not what I want.
This returns few records as it should:
try {
$sql = /** @lang mysql */
"SELECT *, MATCH (first_name, last_name, email, username)
AGAINST (('$string*') IN BOOLEAN MODE) AS score
FROM customer p
WHERE MATCH (first_name, last_name, email, username)
AGAINST (('$string*') IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 100;
";
$stmt = $conn->prepare($sql);
$stmt->execute();
}catch (\Exception $e){
Following code returns [ ]:
try {
$sql = /** @lang mysql */
"SELECT *, MATCH (first_name, last_name, email, username)
AGAINST ((':string*') IN BOOLEAN MODE) AS score
FROM customer p
WHERE MATCH (first_name, last_name, email, username)
AGAINST ((':string*') IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 100;
";
$stmt = $conn->prepare($sql);
$stmt->execute(['string' => $string]);
Query like following:
SELECT *, MATCH (first_name, last_name, email, username)
AGAINST ((:string*) IN BOOLEAN MODE) AS score
FROM customer p
WHERE MATCH (first_name, last_name, email, username)
AGAINST ((:string*) IN BOOLEAN MODE)
ORDER BY score DESC
LIMIT 100;
Results in following error:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') IN BOOLEAN MODE) AS score FROM customer p WHE' at line 2
I expect the second query to work the same as the first one.
As much as I understand There is some "trick" of rule of writing this which I do not know and/or cannot find. Please tell me:
How to use Full-text search with the prepared statement. What is the syntax?
