basic concatenation/FULLTEXT search question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

basic concatenation/FULLTEXT search question

Post by zoe »

I have a business directory that uses this search query(roughly):

Code: Select all

$query = "SELECT * FROM business_directory WHERE 
Name LIKE '% $businesstype %' OR Name LIKE '$businesstype %' OR Name LIKE '% $businesstype'";
My question now is: Is it possible, and what would the correct syntax be to also match $businesstype(s), thereby finding designer(s) when a search is carried out for 'designer'? I should know this by now! :oops:
Last edited by zoe on Thu Dec 01, 2005 8:48 am, edited 1 time in total.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

I"m not sure what you mean? You mean you want to match the designer to the business variable as well? Or the designers are listed in a different table, or you want to match designers to a different variable?
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

When $businesstype = 'designer', I want it also to match 'designers'. It's just that most people enter their details as, for example Anonymous Web Designers Ltd, and most site users will search for designer, rather than designers. I want to open up the search to include plurals, but not for example, a search for 'car' producing results that include 'carpet'.
Last edited by zoe on Fri Nov 25, 2005 12:13 pm, edited 1 time in total.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

Code: Select all

$designers = array("designer1","designer2");
if(in_array($businesstype, $designers))
    $where = "designer like '%$businesstype%'";
else
    $where = "Name LIKE '% $businesstype %' OR Name LIKE '$businesstype %' OR Name LIKE '% $businesstype'";
$query = "SELECT * FROM business_directory WHERE $where";
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

Thanks. You're too fast for me. I altered my previous post to make more sense.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

well using the like operator may not be your best solution. I'd suggest you look at using a full-text index search as that will accomplish what you're trying to do.
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

I have tried that and ran in to the same issue: I'm just not sure of the syntax.
Here's the code I used.

Code: Select all

$query = "SELECT *, MATCH (Name, Type, Keywords) AGAINST ('$businesstype' IN BOOLEAN MODE) FROM business_directory WHERE Village = '$village'";
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

include match / against in your where clause...
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

Sorry. I feel really silly, but I don't know how to bring that 's' in to the equation.

Code: Select all

$query = "SELECT *, MATCH (Name, Type, Keywords) AGAINST ('$businesstype' IN BOOLEAN MODE) FROM business_directory WHERE MATCH (Name, Type, Keywords) AGAINST ('$businesstype')";
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

with the full text index you don't need to worry about it.

the full text indexed search should find things like "designer" when you're searching for "designers". It will however not find "car" when searching for "carpet".
zoe
Forum Commoner
Posts: 36
Joined: Sat Jun 11, 2005 11:51 am
Location: Glasgow

Post by zoe »

I've taken a bit of a break from this one, but it doesn't seem to have gone away!
If the query I posted above should work correctly and return table entries containing 'designers' when the user asks for 'designer', I must be doing something else wrong.

Code: Select all

$businesstype = ($_GET['businesstype']);

$query = "SELECT *, MATCH (Name, Type, Keywords) AGAINST ('$businesstype' IN BOOLEAN MODE) FROM business_directory WHERE MATCH (Name, Type, Keywords) AGAINST ('$businesstype' IN BOOLEAN MODE)";

$result = mysql_query($query);
echo (mysql_num_rows($result));
I've cut the code right back. When $businesstype = 'designers', I get 12 results, but for 'designer' I get 0.
Anyone any ideas what I'm mising? The page in question is at http://www.g63directory.com/searchResults1.php if it's any help. Many thanks.
Post Reply