5

Given a search string I need to select every record where (in the field the search is performed on) there is at least one word that begins with the given text.

For example:

'John Doe'

Have to be be selected with search strings like:

'joh'
'do'
'JOHN doe'

Have not to be selected with

'ohn'
'oe'

I need (possibly) to avoid full text search.

What I've found to work is

$query = 'SELECT * FROM MYTABLE WHERE SEARCHFIELD LIKE "' . $searchText . '%"'
                                . 'OR SEARCHFIELD LIKE "% ' . $searchText . '%"'

I'm asking if there is a better way to do that.

(for "better way" I mean better performance or same performance but more elegant)

Also, as the query will be built up with a prepared statement, how should I unescape LIKE metacharacters in the search string?

Paolo
  • 15,233
  • 27
  • 70
  • 91

2 Answers2

3

As already stated in the question the query

$query = 'SELECT * FROM MYTABLE WHERE SEARCHFIELD LIKE "' . $searchText . '%"'
                                . 'OR SEARCHFIELD LIKE "% ' . $searchText . '%"'

works for matching records where the SEARCHFIELD contains a word that begins with (or is equal to) $searchText

Regarding performance I've made a test on my development machine MBP 2,2 GHz i7 quad core:

Searching for a word on 4.000 records takes around 40 milliseconds.

Records are normally indexed (no fulltext).

I have few thousands records and the query doesn't run very often so for me is good.
The solution may not be suitable for other contexts.

To build a prepared statement with the above query I used the technique described here:

Escaping MySQL wild cards

The resulting code is as follows:

function like($s, $e)
{
    return str_replace(array($e, '_', '%'), array($e . $e, $e . '_', $e . '%'), $s);
}

/* ... */

/* create a prepared statement */
$stmt = $mysqli->prepare(
    'SELECT * FROM MYTABLE WHERE SEARCHFIELD LIKE ? ESCAPE "=" OR SEARCHFIELD LIKE ? ESCAPE "="'
); 

if( $stmt )
{
    /* escape the text */
    $escSearchText = like( $searchText, "=" );

    /* 'like' parameters */
    $like1 = $escSearchText . "%";
    $like2 = "%" . $escSearchText . "%";

    /* bind parameters for markers */
    $stmt->bind_param( "ss", $like1, $like2 );

/* ... */
Community
  • 1
  • 1
Paolo
  • 15,233
  • 27
  • 70
  • 91
2

Use this:

$query = "SELECT * FROM MyTable WHERE searchfield LIKE CONCAT('%', ?, '%')";

You don't need the OR condition -- if a field matches search%, it will also match %search%.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • No, that will match with 'oe' or 'ohn' too. I'll edit my question to be clear on that – Paolo Oct 31 '13 at 22:41
  • `LIKE` can't do word matching, it just matches strings. – Barmar Oct 31 '13 at 22:43
  • You need to use a `RLIKE` instead of `LIKE` and create a regular expression. The same basic approach still works, you just have to use the regular expression that matches the beginning of a word instead of the first `%`. – Barmar Oct 31 '13 at 22:44