PHP, MySQL: Making a better search?

Question

I've made a search box feature that allows me to type in a word and then it finds any matches of the word in my database. However, it only finds EXACT matches. I'm looking for suggestions on how to make the search better.

The code below is what i currently use for searching the databases for users that might be matches for the user searching.

    $search_keys = array('fname', 'lname', 'email' );

    foreach ( $search_keys as $key )
    {
        $result = mysql_query( "SELECT id FROM users WHERE " . $key . " LIKE " . "\"{$str}\"" ) or die(mysql_error());

        while ( $row = mysql_fetch_array( $result ) )
        {

            // Get the User
            $tmp_user = new User();
            $tmp_user->getUserById( $row['id'] );

            // Add User to list of potential candidates
            array_push($users, $tmp_user);
        }
    }

keep in mind that using the LIKE method ignores the index, and makes searches crawl depending on your data subset — Jakub
– Jakub, Commented May 4, 2011 at 15:11
Not totally true. It will only ignore the index if the search term is fully surrounded, however if it only ends with a % - for example Jam%, it will use the index. Source: dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html — josh.trow
– josh.trow, Commented May 4, 2011 at 15:14

hakre · Accepted Answer · 2011-05-04 18:43:11Z

4

I see two points where you can improve your code fragment. But first of all take care that $str is properly formatted to be safely used in your SQL query. Otherwise you will run into a problem called SQL Injection. I assume that now for your code.

Use wildcards with the LIKE SQL function.
Search across the three fields with one SQL query.

Please see the example code which contains both suggestions. First the SQL query is build. There is only need to run one query for all (three) fields instead of one query per field. That's useful if you extend your search later.

/* build SQL query */
$conditions = array();
$search_keys = array('fname', 'lname', 'email' );
foreach ( $search_keys as $key )
{
    $conditions[] = "{$key} LIKE \"%{$str}%\""; # Wildcard (%); [] works like array_push()
}
$query = sprintf('SELECT id FROM users WHERE (%s)', implode(' OR ', $conditions));

/* run SQL query */
$result = mysql_query($query) or die(mysql_error());
while ( $row = mysql_fetch_array( $result ) )
{
    // Get the User
    $tmp_user = new User();
    $tmp_user->getUserById( $row['id'] );

    // Add User to list of potential candidates
    array_push($users, $tmp_user);
}

edited May 4, 2011 at 18:43

answered May 4, 2011 at 15:18

hakre

200k55 gold badges454 silver badges865 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

daveomcd Over a year ago

I think your line on 11 should be $result = mysql_query( $query );

Andy Over a year ago

This is an excellent start, I was able to modify for a much broader search. Thanks for the framework, this is a gem.

josh.trow · Accepted Answer · 2011-05-04 15:08:13Z

2

Well there is always the simplest method of adding the wildcard operator, so if they enter 'Jam' it would be

SELECT id FROM users WHERE fname LIKE '%Jam%'
etc...

EDIT: The point being it would return a match on JAMIE or JAMES or LogJam or (you get the idea), which means they don't need to remember the whole name, just some part of it.

answered May 4, 2011 at 15:08

josh.trow

4,90123 silver badges31 bronze badges

4 Comments

Jakub Over a year ago

however also the slowest possible approach, and with thousands or records you will see this crawl.

daveomcd Over a year ago

What's a better approach then, Jakub? This does work well but if there is a better method I would like to explore it.

josh.trow Over a year ago

@Jakub No argument there, but doing this kind of search is never fast anyway.

Jakub Over a year ago

It would depend on the type of search the user is making, STARTS WITH 'TERM%' is faster than CONTAINS '%TERM%', etc; It really depends on his database schema, the content he searches for, you can always optimize, just look at the way some php search engines work (they index the words and most do exact = 'term' searches which refer back to original search term index / reference.

iwiznia · Accepted Answer · 2011-05-04 15:09:18Z

2

I would suggest to use a fulltext database as solr or sphinx for this kind of behaviour. If you can't or don't want to install it, you should add % to your code. $result = mysql_query( "SELECT id FROM users WHERE " . $key . " LIKE " . "\"%{$str}%\"" ) or die(mysql_error());

answered May 4, 2011 at 15:09

iwiznia

1,67914 silver badges21 bronze badges

Comments

jimy · Accepted Answer · 2011-05-04 15:09:49Z

1

you should try

$result = mysql_query( "SELECT id FROM users WHERE " . $key . " LIKE " . "%" . $str . "%" ) or die(mysql_error());

answered May 4, 2011 at 15:09

jimy

4,9183 gold badges37 silver badges52 bronze badges

Collectives™ on Stack Overflow

PHP, MySQL: Making a better search?

4 Answers 4

2 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related