Page 1 of 1

Search Queries like Ebay/Google

Posted: Tue Feb 16, 2010 12:44 pm
by simonmlewis
Hi

We all know how to do a search for something like "fluffy rabbit" from an animal DB table:

"rabbit" or "fluffy" or "fluffy rabbit" will find the result.

But how do you also get the result come up, if you search for "rabbit fluffy"?
The order is different but the words ARE in the table.

Hope someone can help - I am sure it will be one of those SIMPLE solutions, but can I find it??? No.

Re: Search Queries like Ebay/Google

Posted: Tue Feb 23, 2010 6:04 pm
by abushahin
hey you can use fulltext search, you may have to change your data types to text or if its varchar you can use boolen mode. But if its text try this:

Code: Select all

SELECT animal name MATCH (animal_db)
AGAINST ('fluffy rabbit') AS score FROM animal_db WHERE MATCH (animal_db) AGAINST ('fluffy rabbit')
This isnt tested but should work or similar look up fulltext search. Above example gives you a relevance score too.
Fluffy rabbit can be in any order

Re: Search Queries like Ebay/Google

Posted: Fri Feb 26, 2010 7:15 am
by simonmlewis
Not sure about the animal_db.

This is what I interpretted your code to be:

Code: Select all

$result = mysql_query ("SELECT * MATCH (products) AGAINST ('$search') AS score FROM products WHERE MATCH (products) AGAINST ('$search') AND pause = 'off'  LIMIT $offset, $rowsPerPage") or die (mysql_error());
This is the error I get:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MATCH (products) AGAINST ('truck exquisite') AS score FROM products WHERE MATCH ' at line 1

Re: Search Queries like Ebay/Google

Posted: Sun Apr 11, 2010 1:30 pm
by abushahin
Hey, dont no if you have already found an answer or not, you need to add the comma (,) after *, and have you changed your field types in your db to text? if not add the line in boolean mode after your query inside the bracket so.

Code: Select all

$result = mysql_query ("SELECT *, MATCH (products) AGAINST ('$search' IN BOOLEAN MODE) AS score FROM products WHERE MATCH (products) AGAINST ('$search' IN BOOLEAN MODE) AND pause = 'off'  LIMIT $offset, $rowsPerPage") or die (mysql_error());