RE: [PHP-DB] how to do a search in mysql database for multiple keywords?? From: Mike Patton (mpatton <email protected>)
Date: 10/15/01

Another solution would also be to use the FULLTEXT functions availble in
MySQL... it is by far faster than this query. To do so, your fields must be
of type VARCHAR and TEXT. Then create a FULLTEXT index on these fields by
issuing the following command:

ALTER TABLE <tablename> ADD FULLTEXT(<column1>,<column2>,<ccolumn3>)

Then your query would look like:

SELECT *, MATCH(<field1>,<field2>,<field3> ) AGAINST ('$qiery' ) AS score
FROM Messages WHERE MATCH( <field1>,<field2>,<field3> ) AGAINST ( '$query'
) ORDER BY score DESC;

This would return the results in order of relevance... works pretty good :) 

-- 
------------------------------------------------------------------------
James "Mike" Patton      \     Professional web applications developer
mpatton <email protected>   \    Specializing in PERL, MySQL, PHP and JAVA
------------------------------------------------------------------------

On 2001.10.15 04:08 Dave Watkinson wrote:
> 	SELECT 
> 	<columnlist> 
> 	FROM 
> 	<tablename> 
> 	WHERE 
> 	<column to be searched> 
> 	LIKE 
> 	'%<word1>%' 
> 	AND 
> 	<column to be searched> 
> 	LIKE 
> 	'%<word2>%' 
> 	AND 
> 	<column to be searched> 
> 	LIKE 
> 	'%<word3>%'
> 
> for example...
> 
> SELECT uid, name, address, email FROM people WHERE address LIKE
> '%london%' AND address LIKE '%baker%' AND address LIKE '%221b%'
> 
> In MySQL the % sign is the wildcard, so '%baker%' will look for all
> instances containing baker. Similarly baker% will match all strings
> starting with baker and baker% will match all strings ending in baker.
> 
> 
> Hope this helps
> 
> 
> Dave
> 
> 
> 
> -----Original Message-----
> From: Nicklas Bondesson [mailto:nicklas.bondesson <email protected>]
> Sent: 15 October 2001 09:06
> To: php-db <email protected>
> Subject: [PHP-DB] how to do a search in mysql database for multiple
> keywords??
> 
> 
> need some help on this.
> 
> any ideas??
> 
> /nicke
> 
> 
> 
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: php-db-unsubscribe <email protected>
> For additional commands, e-mail: php-db-help <email protected>
> To contact the list administrators, e-mail: php-list-admin <email protected>
> 
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, e-mail: php-db-unsubscribe <email protected>
> For additional commands, e-mail: php-db-help <email protected>
> To contact the list administrators, e-mail: php-list-admin <email protected>
> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, e-mail: php-db-unsubscribe <email protected>
For additional commands, e-mail: php-db-help <email protected>
To contact the list administrators, e-mail: php-list-admin <email protected>