StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>There are two ways to do this. The first is the rather obvious approach. Let's say you have all the words that need to appear in an array called $necessaryWords:</p> <pre><code>$sql = 'SELECT ... FROM ...'; // and so on $sql .= ' WHERE 1'; foreach ($necessaryWords as $word) $sql .= ' AND concat(subject,body) LIKE "%' . $word . '%"'; //Quotes around string </code></pre> <p>However, using <code>%foo%</code> is rather slow, as no indexes can be used, so this query might cause performance issues with huge tables and/or a high number of necessary words.</p> <p>The other approach would be a <code>FULLTEXT</code> index on <code>subject</code> and <code>body</code>. You could the use the fulltext <code>MATCH IN BOOLEAN MODE</code> like this:</p> <pre><code>$sql = 'SELECT ... FROM ...'; // and so on $sql .= ' WHERE MATCH(subject,body) AGAINST("'; foreach ($necessaryWords as $word) $sql .= ' +' . $word; $sql .= '")'; </code></pre> <p><s>Note that your table must use <code>MyISAM</code> in order to use <code>FULLTEXT</code> indexes.</s> UPDATE: <a href="https://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html" rel="nofollow noreferrer">As of MySQL 5.6</a>, <code>InnoDB</code> supports <code>FULLTEXT</code> indexes as well. I guess this could be the better choice performance wise. Further documentation on the fulltext in boolean mode can be found in the <a href="http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html" rel="nofollow noreferrer">manual</a>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload