LIKE clause in search query

[garyjohnson]

Hi, I am using the Like %$search% clause in my query to find results in my database, and I was wondering if this is the most effective and efficent way to do this?

[deceptikon]

The answer is a definite maybe. As far as being effective, if it's not effective then it's not a viable solution because it doesn't work. So there's not really any degree here, it either works for your needs or it doesn't.

Whether it's the most efficient way really depends on what exactly you want to happen. The data might support an exact or even indexed match, and in that case a LIKE clause would be relatively inefficient.

[garyjohnson]

It seems to be working fine and having no issues for what I need it to do, but is it possible for me to order the results by the most matched word? For instance the word searched is hat, the words hat and bat show up, is it possible to make sure hat is displayed first?

[LastMitch]

For instance the word searched is hat, the words hat and bat show up, is it possible to make sure hat is displayed first?

You need to used a preg_match() function.

For example

word is column in the database.

When you find a word in $word then used this:

if(preg_match("/[A-Z|a-z]+/", $_POST['word'])){$word=$_POST['word'];} 

Then you SELECT:

$sql="SELECT * FROM mydata WHERE word LIKE '%".$word."%'";

You need to play around with the preg_match() to find that pattern to make hat appear first.

I don't know the pattern.

[diafol]

Have a look at MATCH with FULLTEXT for MySQL.

Here's just one bit on it: http://datatables.net/forums/discussion/5688/how-to-mysql-fulltext-searching/p1

It should return results based on a rank. (Ignore the JS stuff though).

Note you can only use this with MyISAM tables - if you're using InnoDB, you'll need to convert it. In addition, you'll need to create a fulltext INDEX on the field(s) that you wish to search.