Search Queries like Ebay/Google

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Search Queries like Ebay/Google

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
abushahin
Forum Commoner
Posts: 42
Joined: Wed Nov 25, 2009 12:35 pm
Location: london

Re: Search Queries like Ebay/Google

Post 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
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Search Queries like Ebay/Google

Post 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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
abushahin
Forum Commoner
Posts: 42
Joined: Wed Nov 25, 2009 12:35 pm
Location: london

Re: Search Queries like Ebay/Google

Post 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());
Post Reply