Small search problem on my website

[McMaster]

Hey,

 

I am trying to create a search on my website but I am having a problem. When I type in "Delete Account" no results are shown even though there is results. If I narrow it down to just "Delete" results are shown. Is there any way I can make it search even if I type in Delete Account? This is my query:

 

mysql_query("SELECT * FROM help_topics WHERE topic LIKE '%$query%' OR text LIKE '%$query%'");

 

Thanks guys

[premiso]

http://www.phpfreaks.com/tutorial/simple-sql-search

 

That might help you understand and work with the problem.

[McMaster]

I see from that page that they are just using wildcards which is what I am doing but it's not working right :S any ideas?

[premiso]

You just skimmed over it.  Read it a bit more in detail, such as how I exploded the items at the space and the made it search for the different terms combing them with an OR so it pulls items that have either DELETE or Account in them.

[Pikachu2000]

I see FULLTEXT searches in your future. LIKE searches are slow, inefficient, and inflexible.

[McMaster]

Yeah I see what you are saying premiso. I will have a further look in to it. This fulltext search though that was mentioned, I tried it with this query:

 

$sql = mysql_query("select * match(topic,text) against('$query') as relevance from help_topics");

if (mysql_num_rows($sql)) {

//code

}

 

And I am resulted in this error:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/juiceoff/public_html/search_help.php  on line 361

 

Any help there? I am not sure whether I can use mysql_num_rows with fulltext so hopefully someone can point me in the right direction here.

[Pikachu2000]

Your query string syntax is off.

 

SELECT * FROM (table) WHERE MATCH(columns) AGAINST(terms)

[McMaster]

Tried this:

 

$sql = mysql_query("SELECT * FROM (help_topics) WHERE MATCH(topic,text) AGAINST('$query')");

 

It still doesn't show "delete account" results when it should. Hmm.

[premiso]

You may be interested in http://www.sphinxsearch.com/

 

But the full-text search only returns rows if the search terms return less than 50% of the rows in the table (if I am not mistaken). So for not very large tables the full text isn't the best tool to use.

[Pikachu2000]

That's correct, and search terms less than 4 characters are ignored by default, but that can be changed in the MySQL config. The fields also need to be indexed as FULLTEXT.

[McMaster]

Set fields as fulltext in phpmyadmin, searched "delete account" and it still doesn't work. Arr! What on earth am I doing wrong.

[Pikachu2000]

What percentage of the records in the database contain those terms? If it's over 50%, it will return no results.