PHP/MySQL Searching

Question

I have a single table with 4 text fields that I am currently searching using

WHERE `$field` LIKE '%$value%'

to match each field directly and ordering results by number of field matches. This was the most efficient method for me to code it (because of my extremely limited experience, however I realize it's not the most efficient method for user interface.

I'm just wondering what direction to take to create a more user-friendly search statement. I have been reading about different types of table searching, but I'm confused about which is the best method for my table.

Search form uses all 4 fields, only one needs to be filled out, and I'd like to order by relevance, however that is achieved. I don't want to get exact matches because of spelling errors and whatnot.

Here's the table I'm working with:

MySQL>EXPLAIN `so_customer_isam`;

-

Type: MyISAM
Collation: UTF8_bin
Primary Key: `id`

-

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| last_name  | varchar(35) | NO   |     | NULL    |                |
| first_name | varchar(35) | NO   |     | NULL    |                |
| company    | varchar(35) | YES  |     | NULL    |                |
| phone      | varchar(35) | NO   |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

Will it searching on more than fields at a give time, i guess yes. And as Fabien pointed out relevancy, if you could elaborate on the relevancy. To start with i would say implement indexing in a way that helps the search. Probably go for mysql soundex search in case of names. But it would be depend on the use case you are referring to and amount of data your app will handle. — jaipster
– jaipster, Commented Jan 14, 2014 at 18:44
I guess what I'm asking is "Should I even attempt to use full-text search since I'm only searching single terms at a time?" I'm trying to determine the best method for searching in single word field format. By "relevance" I mean [Search: "Chris Brown"] = Chris Brown > Christopher Brown > Chris Browne > Chriss Brown > Brown > Chris. I currently have 400 customers in this table and will be adding around avg. 1 per day or so. So it's not a large database and all searching is done on the local network. — Dexter
– Dexter, Commented Jan 14, 2014 at 19:33

Hüseyin BABAL · Accepted Answer · 2014-01-14 18:43:11Z

1

If you are using full text search in mysql, try http://sphinxsearch.com/. This indexes your preferred data and then you can make full text search on that index.

Or you can use elasticsearch, a powerful search/indexing engine as a service. This is the popular search service nowadays.

I don't suggest you to make full text on mysql. By trying to use above technologies, you can also develop your skills.

answered Jan 14, 2014 at 18:43

Hüseyin BABAL

15.6k5 gold badges53 silver badges74 bronze badges

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

1 Comment

Dexter Over a year ago

I ended up going with Sphinx. There was no real easy way to search for misspellings of names on Full-Text searches, so as you mentioned, I ended up downloading and installing Sphinx. It works awesome. Thanks so much!

Collectives™ on Stack Overflow

PHP/MySQL Searching

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related