PHP/MYSQL - Boolean Full-Text Searches - exact string operator ("") gives different result sets in Prepared Statement vs SQL Query -

i making advanced search page on website using php prepared statements query mysql database myisam table using boolean full-text search.

it first section of advanced search provided google , covers following criteria:

• all of these words
• exact word or statement (nb: problem comes in)
• any of these words
• none of these words

i retrieve each input value, clean , process each part of collection of strings, append appropriate information form mysql query via prepared statement.

so in essence following search:

• all -
• exact -
• any - seagate toshiba
• none -

would output string:

seagate* toshiba* 

the query result in this:

select id, description `items`  match (description) against ('seagate* toshiba*' in boolean mode) 

which list rows words "seagate" followed , "toshiba" followed in description field.

this works fine, output of:

-(750gb*) -(320gb*) seagate* toshiba* 

which list rows above exclude rows "750gb" , "320gb" in description field.

by adding value "all of these words" string output of:

+(16mb*) +(7200rpm*) -(750gb*) -(320gb*) seagate* toshiba* 

which list rows above show downs included both "16mb" , "7200rpm" in description field.

now problematic part. if make use of "exact word of statement" string , add value "serial ata 600" output of:

+(16mb*) +(7200rpm*) -(750gb*) -(320gb*) +("serial ata 600") seagate* toshiba* 

running string , resulting query through sql query using phpmyadmin result set of 2 rows matching criteria search.

however, when running on website result of 6 rows indicate +("serial ata 600")" being ignored entirely.

if enter value string "exact word of statement" output of:

+("serial ata 600") 

the results indicate string list rows contained either "serial" or "ata" or "600".

by running same query directly in mysql result list rows contained words "serial ata 600".

in mysql definition of operator states:

a phrase enclosed within double quote (“"”) characters matches  rows contain phrase literally, typed. 

this case in mysql when running same query php prepared statement returns different result set.

here the prepared statement:

if ($result = $link->prepare("     select id, description     `items`      match (description)     against (? in boolean mode) ")) {     $result->bind_param("s", $pattern);     ... etc } 

here output of $pattern directly before this:

+("serial ata 600") 

could possibly suggest reason behavior not see reason there difference in way things work here between php , mysql.

i can provide additional code relating how string generated on request output in example.

any suggestions / advice / input / feedback or comment appreciated! thank you!!

this 1 place prepared statements fall flat on faces. internally, preparation engine doing equivalent of:

$quoted = mysql_real_escape_string('+("serial ata 600")'); 

which gives equivalent of

+(\"serial ata 600\") 

now you're not using 3-word quote phrase anymore, you're sending in following separate words:

+("serial  ata  600") 

this because " quotes sql metacharacters, , need them treated metacharacters. however, because metacharacters, prep engine quote them, reducing them normal plain-jane quotes, , they're no longer enclosing search phrase. they've become part of search phrase.

don't know if work, may have rewrite prepared statement more like

... match against (concat('("', ?, '")'))