title: PHP/MYSQL - Boolean Full-Text Searches - exact string operator ("") gives different result sets in Prepared Statement vs SQL Query
body: <p>I am making an advanced search page on my website using PHP prepared statements to query a MySQL database MyISAM table using a Boolean Full-Text search.</p>
  <p>It is very much like the first section of the Advanced Search provided by Google and covers the following criteria:</p>
  <ul>
  <li>All of these words</li>
  <li>Exact word or statement (NB: This is where the problem comes in)</li>
  <li>Any of these words</li>
  <li>None of these words</li>
  </ul>
  <p>I retrieve each input value, clean and process each part of the collection of strings, then append the appropriate information to form the mySQL query via the prepared statement.</p>
  <p>So in essence for the following search:</p>
  <ul>
  <li>All -</li>
  <li>Exact -</li>
  <li>Any - seagate toshiba</li>
  <li>None -</li>
  </ul>
  <p>Would output as this string:</p>
  <pre><code>seagate* toshiba*
  </code></pre>
  <p>The query would result in something like this:</p>
  <pre><code>SELECT id, description
  FROM `items` 
  WHERE MATCH (description)
  AGAINST ('seagate* toshiba*' IN BOOLEAN MODE)
  </code></pre>
  <p>Which would list all rows with the words &quot;seagate&quot; followed by anything and &quot;toshiba&quot; followed by anything in the description field.</p>
  <p>This works fine, as does an output of:</p>
  <pre><code>-(750gb*) -(320gb*) seagate* toshiba*
  </code></pre>
  <p>Which would list all rows as above but exclude any rows with &quot;750gb&quot; and &quot;320gb&quot; in the description field.</p>
  <p>By adding a value to the &quot;All of these words&quot; string we would get an output of:</p>
  <pre><code>+(16mb*) +(7200rpm*) -(750gb*) -(320gb*) seagate* toshiba*
  </code></pre>
  <p>Which would list all rows as above but show only downs that included both &quot;16mb&quot; and &quot;7200rpm&quot; in the description field.</p>
  <p><strong>Now for the problematic part</strong>. If i were to make use of the &quot;Exact word of statement&quot; string and add the value &quot;serial ata 600&quot; we would get an output of:</p>
  <pre><code>+(16mb*) +(7200rpm*) -(750gb*) -(320gb*) +(&quot;serial ata 600&quot;) seagate* toshiba*
  </code></pre>
  <p>Running this string and resulting query through as a sql query using phpmyadmin I get a result set of 2 rows matching the criteria for the search.</p>
  <p>However, when running this on my website i get a result of 6 rows which would indicate that +(&quot;serial ata 600&quot;)&quot; is being ignored entirely.</p>
  <p>If i enter only a value for the string &quot;Exact word of statement&quot; so that we would get an output of:</p>
  <pre><code>+(&quot;serial ata 600&quot;)
  </code></pre>
  <p>The results would indicate that this string would list all rows that contained either &quot;serial&quot; or &quot;ata&quot; or &quot;600&quot;.</p>
  <p>By running the same query directly in mysql this result would list all rows that contained exactly the words &quot;serial ata 600&quot;.</p>
  <p>In the <a href="https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html" rel="nofollow noreferrer">MySQL definition of this operator</a> it states:</p>
  <pre><code>A phrase that is enclosed within double quote (“&quot;”) characters matches 
  only rows that contain the phrase literally, as it was typed.
  </code></pre>
  <p>This is the case in MySQL but when running the same query with PHP as a Prepared Statement returns a different result set.</p>
  <p>Here is the the prepared statement:</p>
  <pre><code>if ($result = $link-&gt;prepare(&quot;
      SELECT id, description
      FROM `items` 
      WHERE MATCH (description)
      AGAINST (? IN BOOLEAN MODE)
  &quot;))
  {
      $result-&gt;bind_param(&quot;s&quot;, $pattern);
      ... ETC
  }
  </code></pre>
  <p>Here is the output of <code>$pattern</code> directly before this:</p>
  <pre><code>+(&quot;serial ata 600&quot;)
  </code></pre>
  <p>Could anyone possibly suggest the reason for this behavior as i do not see any reason for there to be any difference in the way things work here between PHP and MySQL.</p>
  <p>I can provide any additional code relating to how the string is generated on request but the output is as it is in my example.</p>
  <p>Any suggestions/advice/input/feedback or comment would be greatly appreciated.</p>