PHP/MySQL: Simple search engine

Question

I'm putting together a simple search engine for a small site. Users will be able to search for their friends by their email address or name. I was hoping to use FULLTEXT search, but I'm using InnoDB tables, so that takes me back to LIKE %keyword%.

My problem is that users have their first name and last name stored in two separate columns. How can I go about searching for "David Rule" is a situation like that?

Here's what I have at the moment:

    if(filter_var($query,FILTER_VALIDATE_EMAIL)) {
        $STH = $this->_db->prepare("SELECT UserID
            FROM UserCredTable
            WHERE EmailAddress = :query;");
    } else {
        $STH = $this->_db->prepare("SELECT UserID
            FROM UserInfoTable
            WHERE FirstName LIKE :query
            OR LastName LIKE :query;");
    }

It sort of works, but I'm guessing relevance is pretty much non-existent :(

How can I sort by the most relevant results?

Update:

Does this seem like a decent solution?

    if(filter_var($query,FILTER_VALIDATE_EMAIL)) {
        $params["query"] = $query;
        $STH = $this->_db->prepare("SELECT UserID
            FROM UserCredentials
            WHERE EmailAddress = :query;");
    } else {
        $params["query"] = "%".$query."%";
        $STH = $this->_db->prepare("SELECT UserID
            FROM UserDetails
            WHERE CONCAT(FirstName,' ',LastName) 
            LIKE :query;");
    }

It doesn't from the performance point of view. This query would be unable to use indexes, thus, with large table and/or heavy usage it will make the server slow. For the small table it's okay though. — Your Common Sense
– Your Common Sense, Commented Mar 19, 2012 at 18:07
@YourCommonSense There won't be more than a six thousand rows ever searched. And only very rarely. Is that acceptable? — Chuck Le Butt
– Chuck Le Butt, Commented Mar 19, 2012 at 18:29
I know this is old, but worth mentioning for future readers, that since MySQL 5.6, InnoDB supports FULLTEXT indexes as well. — matronator
– matronator, Commented Nov 6, 2021 at 1:34

fire · Accepted Answer · 2012-03-19 15:58:24Z

3

Take a look at Sphinx it indexes data directly from MySQL and has a PHP API for search.

Other than that you could split up the spaces in the search text and try a few OR's but like you said relevance is non-existant in InnoDB.

MySQL isn't really designed for search so using another product that is will be more flexible and scalable in the long-term.

answered Mar 19, 2012 at 15:58

fire

21.5k18 gold badges82 silver badges116 bronze badges

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

Comments

Mark Biek · Accepted Answer · 2012-03-19 16:20:06Z

0

Search is obviously a big topic and there are lots of different ways to handle things.

That said, for the most basic possible solution, you should be able to search on the concatenation of those two fields:

SELECT UserID
        FROM UserInfoTable
        WHERE CONCAT(FirstName, ' ', LastName) LIKE '%Dave Smith%';

The CONCAT() MySQL function takes as many strings as you're concatenating as arguments. In the above case, we're just added a space between FirstName and LastName.

edited Mar 19, 2012 at 16:20

answered Mar 19, 2012 at 15:57

Mark Biek

152k54 gold badges160 silver badges201 bronze badges

Comments

Paul Bain · Accepted Answer · 2012-03-19 15:56:46Z

0

You should consider using the full-text search support build into MySQL and many other database engines.

It adds ranking to your search for you, it's fairly awesome.

The following example is from the MySQL manual,http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM articles
    -> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
|  1 | MySQL Tutorial    | DBMS stands for DataBase ...             |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)

answered Mar 19, 2012 at 15:56

Paul Bain

4,4171 gold badge19 silver badges31 bronze badges

2 Comments

Ry- Over a year ago

"I was hoping to use FULLTEXT search, but I'm using InnoDB tables"

lyubeto Over a year ago

FULLTEXT indexes are supported in InnoDB, since MySQL 5.6 and MariaDB 10.0

redDevil · Accepted Answer · 2012-03-19 16:00:21Z

-1

You can use the CONCAT() function in MySQL

} else {
     $STH = $this->_db->prepare("SELECT UserID
         FROM UserInfoTable
         WHERE CONCAT(FirstName,LastName) LIKE '%:query%';");
 }

answered Mar 19, 2012 at 16:00

redDevil

1,91917 silver badges25 bronze badges

Comments

Nick · Accepted Answer · 2012-03-19 16:03:03Z

-1

If fulltext search isn't an option then you'd probably have to look at spliting the search term and querying for something like this:

SELECT UserID
FROM UserInfoTable
WHERE (FirstName LIKE '%David%'
OR LastName LIKE '%David%') OR
(FirstName LIKE '%:Rule%'
OR LastName LIKE '%:Rule%')

Not ideal, and I'm not certain if this is the best solution, but it would probably return the best resultset for you. Especially if people start having triple barreled names (e.g. "John Taylor Smith") which seems to be becoming more common

answered Mar 19, 2012 at 16:03

Nick

6,3462 gold badges32 silver badges48 bronze badges

Collectives™ on Stack Overflow

PHP/MySQL: Simple search engine

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related