Partial keyword searching

Can anyone give me an idea of how I can do partial keyword searching with php/mysql search engine?

For example, if a person search for "just can't get enough" i want it to return search result containing keywords "just can't get enough by black eyed peas" or from keywords " black eyed peas just can't get enough".

Another Example: If I enter开发者_如何学Goed "orange juice" i want it to return result with keywords "orange juice taste good"

Its pretty much like google and youtube search.

The code I'm using is: http://tinypaste.com/eac6cf

The search method you've used is the standard method of searching from within small amounts of records. For example, if you had just around a thousand records, it would be ok.

But if you've got to search from millions of records, this method is not to be used as it will be terribly slow.

Rather you have two options.

1. Explode your search field and build your own index containing single words and a reference to the record position. Then only search your index and seek the corresponding record from the main table.

2. Use MySQL's Full text search feature. This is easier to implement but has its own restrictions. This way you don't have to build the index yourself.

MySQL full-text search would help here, but would only work with myISAM tables and the performance tends to go through the drain when your data-sets becomes quite large.

At the company I work for, we push our search queries to Sphinx. Sites like Craigslist, The Pirate Bay, Slashdot all use this so it's pretty much proven for production use.

In MySQL, you can use a MyISAM type table and simply define a text field (CHAR, VARCHAR, or TEXT) and then create a FULLTEXT index. Just keep in mind the size of the text field, the more allowed characters, the larger the size of the index and the slower it's going to be to update.

Other large data-set options would include something like Solr but unless you already know your data is going to have a ton of data, you could certainly start with MySql and see how it goes.

Most MySQL editors, including phpmyadmin provide a gui for adding indexes, if you're doing it by hand the code would look something like:

CREATE TABLE IF NOT EXISTS `test2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;