Geek Answers Handbook

Mysql is like a complete word or the beginning of a word in a string

For the search bar, I need to select each entry, where (in the field the search is performed) there is at least one word starting with the given text.

For example:

'John Doe'

Search strings should be selected, for example:

'joh'
'do'
'JOHN doe'

Unable to choose not with

'ohn'
'oe'

I need (possibly) to avoid full-text search.

What I found to work,

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

I ask if there is a better way to do this.

(for the “better way” I mean better performance or the same performance, but more elegant)

Also, as the query is created with a prepared statement, how do I add unescape LIKE metacharacters to the search bar?

+4
2

,

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

, SEARCHFIELD , ( ) $searchText

, MBP 2,2 GHz i7 quad core:

4.000 40 .

( ).

, , . .

, :

MySQL

:

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 );

/* ... */
+1

:

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

OR - search%, %search%.

+1

More articles:


All Articles