The best SQL command for search engine site.

Hi.

I have a simple search engine portal which consists huge libraries of reviews of applications and games. My question is what is the best SQL command for my portal?

This is the SQL command I'm using..

SELECT * FROM table WHERE keywords like '% .$keywords . %';

Code (markup):

Let say the person is searching for Need For Speed Black Edition. If he typed Need For Speed for his keywords, that would be fine. What if he typed sth like Speed For Need? I don't think any results will be displayed to him.

Can anyone guide me on this? Thanks.

 

I suppose you should add on search page a radio box where user can choose option for search how: "any", "all words" or "exact match"

 

What if I wanna bypass this step. Is there any special algorithm to overcome this issue?

So far.. I'm doing it this way..

Let say the person search for Microsoft Office..

First I make my script run this way.. I separate the terms into 2 keywords.. and run the following commands..

SELECT * FROM table WHERE keywords like '%Microsoft%';
SELECT * FROM table WHERE keywords like '%Office%';

Meaning.. the result from the first word will display first, and the last word displays last.. I don't think this is the best way to do it..

 

You need to use FULLTEXT search capability to work this. In MySQL use MATCH() and AGAINST() to work this out. Which database you are using?

Let me know if you need more help.

 

I'm using the db from phpmyadmin. Thanks for the response mwasif..

How do I use fulltext search btw?

 

How do I write the command? Is it like this?

SELECT * FROM table WHERE keywords like '%mcafee%'
WHERE MATCH (myattributename)
AGAINST ('mydatabasename');

What if my table only have the following attributes.. id.. title.. and url.. ?

 

First you need to create FULLTEXT index on the fields which you want to make searchable. Then the query will be (I am supposing keywords field have FULLTEXT index)

SELECT * FROM table 
WHERE MATCH (keywords)
AGAINST ('office microsoft');

Code (markup):

Did you read the link I have provided in the previous post? Here is it again http://dev.mysql.com/doc/refman/5.0/en/fulltext-natural-language.html.

 

Yes, I am still reading.. I don't get it that's why I asked again. Thanks again.

Let me convert my current table into FULLTEXT table.

Give me a minute.

 

I tried this..

and I get the error message..

#1214 - The used table type doesn't support FULLTEXT indexes

 

Try this

CREATE TABLE links(
id INT(10) NOT NULL PRIMARY KEY,
cat CHAR(10),
name VARCHAR(80),
link varchar(300),
date date,
FULLTEXT (name,link)
) ENGINE = MyISAM;

Code (markup):

You were creating an InnoDB table. InnoDB does not support FULLTEXT. Make sure you have enough records before searching through them.

 

New problem occurs.. I have created the table successfully.. thanks for that.. I have successfully imported the data into my new table called 'LINKS'.

Now I'm going to do the fulltext search. I type in the following command..

Well.. I got the following error..

#1191 - Can't find FULLTEXT index matching the column list

 

By the way.. I just fixed it..

I should use FULLTEXT (name) instead of FULLTEXT (name,link)

because my link contains only id type link. e.g. viewtopic?p=322

 

You created FULLTEXT index on name and links columns and can only use these columns for FULLTEXT searching. BTW, you don't have keywords column in your table. In the previous post I gave you the example. MATCH() contains the column names you are trying to search. They query will be

SELECT * 
FROM links
WHERE MATCH (name,links)
AGAINST ('office')

Code (markup):

 

Okay. I have understand everything about this. Now I have a new error. Just in case you don't know, my links table consist of several categories.. What I wanna do is to search the word office towards the following table but with the condition that category = 'app'. How can I make that possible?

SELECT *
FROM links
WHERE MATCH (name,links) AND category = 'app'
AGAINST ('office')

 

You got it fixed. Good.

 

You can not have anything between MATCH and AGAINST

 

Thanks for the compliment my friend.

Let say I want condition to be applied on my FULLTEXT search. Can you rewrite the command for me? Thanks!

 

SELECT * 
FROM links
WHERE MATCH (name,links) AGAINST ('office')
AND category = 'app'

Code (markup):

 

I don't think you have column category in table instead you have cat.

 

Okay buddy. I have followed every of your instructions and my search engine is working great now in term of search. Million thanks for that!

I have sent a pm to you anyway!