Pdo and MySQL Fulltext Searches

PDO and MySQL Fulltext searches

This is unfortunately a weird exception to the use of query parameters (edit: but apparently not in the most recent point-release of each MySQL branch, see below).

The pattern in AGAINST() must be a constant string, not a query parameter. Unlike other constant strings in SQL queries, you cannot use a query parameter here, simply because of a limitation in MySQL.

To interpolate search patterns into queries safely, use the PDO::quote() function. Note that PDO's quote() function already adds the quote delimiters (unlike mysql_real_escape_string()).

$quoted_search_text = $this->db->quote('+word +word');

$sql = $this->db->prepare("SELECT ... FROM search_table 
    WHERE MATCH(some_field) AGAINST($quoted_search_text IN BOOLEAN MODE");

---

Re comment from @YourCommonSense:

You're right, I just tested this on MySQL 5.5.31, 5.1.68, and 5.0.96 (MySQL Sandbox is a wonderful tool), and it seems that these versions do accept query parameters in the AGAINST() clause of a dynamic SQL query.

I still have a recollection of a conflict existing in the past. Maybe it has been corrected in the most recent point-release of each branch. For example, I find these related bugs:

• Using a stored procedure parameter in the AGAINST() clause always returns the same result: http://bugs.mysql.com/bug.php?id=3734
• Crash or strange results with prepared statement, MATCH and FULLTEXT: http://bugs.mysql.com/bug.php?id=14496

MySQL MATCH AGAINST FULL TEXT SEARCH not working dynamically with php PDO bindParam

It's because you have :query inside single quotes and so MySQL is treating it as a string, not a parameter.

You can workaround it by either replacing '*:query*' with :query and changing the bind to:

$stmt->bindParam(':query', '*' . $data['query'] . '*');

Or you can replace '*:query*' with CONCAT('*', :query, '*')

You might also be running into the restriction (see the manual) on using the same parameter name twice in one query (which only happens if you're not using emulated prepared statements). In that case you will need to change your code as follows:

$stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product 
                            WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query1, '*') IN BOOLEAN MODE) 
                            ORDER BY MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query2, '*')) DESC 
                            LIMIT 10");
$stmt->bindParam(':query1', $data['query']);
$stmt->bindParam(':query2', $data['query']);
$stmt->execute();

PHP Fulltext Search with PDO

How many records to you have in your table that match your search?

By default mysql full text search will not return any results if the matches appear in more than 50% of records.

This means if you have only one record in your table, or only 2 records, then no matter what you search for, you will always get no results.

To make it short, you need at least 3 records in your table and only one of those records must match your search criteria to get any results back.

This is a common situation when you just developing your project and have only 1 or 2 rows in a table, then trying a full test search does not return anything.

PDO fulltext search - get results even if just part of a keyword from a string is in a db column

Instead of MATCHing it AGAINST you can simply use LIKE(if you want to search in more than one column at once you'll have to bind that $_POST['search'] to more than one parameters but it'll do the job.

Here is your new statement:

$search = $pdo->prepare("
    SELECT * FROM books WHERE title LIKE :title OR author LIKE :author;
");

$search->execute([
    'title' => '%' . $_POST['search'] . '%',
    'author' => '%' . $_POST['search'] . '%'
]);

SQL full text search with PHP and PDO

When you prepare the $sql_data array, you need to prefix the parameter name with a colon:

array('queryString' => $value);

should be:

array(':queryString' => $value);

In your first SELECT, you have AGINST instead of AGAINST.

Your second SELECT appears to be missing a table name after FROM, and a WHERE clause. The LIKE parameters are also not correctly formatted. It should be something like:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE '%:queryString%' OR aricle_text_".$lang." LIKE '%:queryString%'";

Update 1 >>

For both SELECT statements, you need unique identifiers for each parameter, and the LIKE wildcards should be placed in the value, not the statement. So your second statement should look like this:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE :queryString OR aricle_text_".$lang." LIKE :queryString2";

Note queryString1 and queryString2, without quotes or % wildcards. You then need to update your array too:

$sql_data = array(':queryString1' => "%$value%", ':queryString2' => "%$value%");

See the Parameters section of PDOStatement->execute for details on using multiple parameters with the same value. Because of this, I tend to use question marks as placeholders, instead of named parameters. I find it simpler and neater, but it's a matter of choice. For example:

sql = "SELECT * FROM ".$db_prefix."_base WHERE article_title_".$lang." LIKE ? OR aricle_text_".$lang." LIKE ?";

$sql_data = array("%$value%", "%$value%");

<< End of Update 1

I'm not sure what the second SELECT is for, as I would have thought that if the first SELECT didn't find the query value, the second wouldn't find it either. But I've not done much with MySQL full text searches, so I might be missing something.

Anyway, you really need to check the SQL, and any errors, carefully. You can get error information by printing the results of PDOStatement->errorCode:

$sth->execute($sql_data);
$arr = $sth->errorInfo();
print_r($arr);

Update 2 >>

Another point worth mentioning: make sure that when you interpolate variables into your SQL statement, that you only use trusted data. That is, don't allow user supplied data to be used for table or column names. It's great that you are using prepared statements, but these only protect parameters, not SQL keywords, table names and column names. So:

"SELECT * FROM ".$db_prefix."_base"

...is using a variable as part of the table name. Make very sure that this variable contains trusted data. If it comes from user input, check it against a whitelist first.

<< End of Update 1

You should read the MySQL Full-Text Search Functions, and the String Comparison Functions. You need to learn how to construct basic SQL statements, or else writing even a simple search engine will prove extremely difficult.

There are plenty of PDO examples on the PHP site too. You could start with the documentation for PDOStatement->execute, which contains some examples of how to use the function.

If you have access to the MySQL CLI, or even PHPMyAdmin, you can try out your SQL without all the PHP confusing things. If you are going to be doing any database development work as part of your PHP application, you will find being able to test SQL independently of PHP a great help.

how to do a full text search with php pdo?

You can change the search string

$searchQ = $_GET['search'];
$searchArr = str_split($searchQ );
$searchQ_new = "";
foreach($searchArr as $alpha)
{
        $searchQ_new .= $alpha . "%";
}

Now you can use your code to search with $searchQ_new

---

Related Topics