Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
641 views
in Technique[技术] by (71.8m points)

php - PDO and MySQL Fulltext searches

I'm converting all my sites code from using mysql_* functions to PDO. The PHP documentation on PDO is not clear for my needs. It gives you the functions to use, but does not go into detail to explain them in different scenarios.

Basically, I have a mysql fulltext search:

$sql = "SELECT ... FROM search_table WHERE MATCH(some_field) AGAINST ('{$searchFor}*' IN BOOLEAN MODE)";

The actual statements much longer, but this is what it basically does.

My question is, how would I incorporate this into PDO?

I know you're not meant to use quotes around the place-marker, so do you leave them out in the AGAINST() function? Do I include them? If I leave them out, what happens to the wildcard symbol etc?

$sql = $this->db->prepare("SELECT ... FROM search_table WHERE MATCH(some_field) AGAINST(:searchText IN BOOLEAN MODE");
$sql->bindValue(':searchText', $searchFor . '*');
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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:


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...