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, Mar 19 '12 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, Mar 19 '12 at 18:29

score 3 · Answer 1 · answered Mar 19 '12 at 15:58

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.

Mark Biek · Accepted Answer · 2012-03-19T16:20:06.000

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.

score -1 · Answer 3 · answered Mar 19 '12 at 15:56

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)

*"I was hoping to use FULLTEXT search, but I'm using InnoDB tables"* — Ry-, Mar 19 '12 at 15:58
FULLTEXT indexes are supported in InnoDB, since MySQL 5.6 and MariaDB 10.0 — lyubeto, Oct 28 '15 at 15:19

score -1 · Answer 4 · answered Mar 19 '12 at 16:00

-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 '12 at 16:00

redDevil

1,879
17
25

score -1 · Answer 5 · answered Mar 19 '12 at 16:03

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

PHP/MySQL: Simple search engine

5 Answers5