Page 1 of 1

basic concatenation/FULLTEXT search question

Posted: Fri Nov 25, 2005 11:39 am
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:

Posted: Fri Nov 25, 2005 11:59 am
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?

Posted: Fri Nov 25, 2005 12:08 pm
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'.

Posted: Fri Nov 25, 2005 12:12 pm
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";

Posted: Fri Nov 25, 2005 12:16 pm
by zoe
Thanks. You're too fast for me. I altered my previous post to make more sense.

Posted: Fri Nov 25, 2005 12:22 pm
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.

Posted: Fri Nov 25, 2005 12:27 pm
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'";

Posted: Fri Nov 25, 2005 12:34 pm
by Burrito
include match / against in your where clause...

Posted: Fri Nov 25, 2005 12:44 pm
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')";

Posted: Fri Nov 25, 2005 1:34 pm
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".

Posted: Thu Dec 01, 2005 7:28 am
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.