Search functionality like www.yell.co.uk

mattis2k

Hi,

I'm not a newbie to php or mySQL, but I'm having problems with a search function on a "directory" website.

I have 3 search fields:

Who,What and Where

The database contains these main fields (there are more obviously)

[company]
[district]
[town]
[county]
[postcode]
[category] (comma seperated list)

Now.

One example of data is [district] = "Wearfield" with other records having [county] = "Tyne and Wear"

If a users was to search on "Wear" in the Where input box, it should return

Did you mean?

Tyne and Wear
Wearfield

and any others that might contain "wear"...

Basically ive been stuck for a "decent" solution for days.

Can anyone help ?

Matt

bubblenut

SELECT * FROM table WHERE district LIKE '%$where%' || county LIKE '%$where%'

Is that what you mean? You can then process the output with php to find which field contained the search criteria.
HTH
Bubble

mattis2k

currently i use this

$sql = "SELECT DISTINCT district, town, county, postcode FROM customer WHERE MATCH (district, town, county, postcode) AGAINST ('".$companyLocation."*');";

See I also need to take simple spelling mistakes into account, match doesnt do that too well. maybe fulltext will or similar_text

You say i can porcess the result to find out which location it was ? can you give an example of this ?

Cheers for your response.

bubblenut

$sql = "SELECT DISTINCT district, town, county, postcode FROM customer WHERE MATCH (district, town, county, postcode) AGAINST ('".$companyLocation."*');";
$res=mysql_query($sql);
$preg="/$companyLocation/iS";
while($row=mysql_fetch_array($res)) {
  if(preg_match($preg, $row['district'])) {
    //it's in the district
  } elseif(preg_match($preg, $row['town'])) {
    //it's in the town
  } elseif($preg_match($preg, $row['county'])) {
    //it's in the county
  }
}

Another option would be to put some logic in the query so that an extra field is set depending on which field matched but this would be very untidy.

HTH
Bubble

mattis2k

Thanks, i'll give that a go, you any idea on the "spelling" issue

bubblenut

Well, there's metaphone and soundex. I built a fuzzy search which I'm working on at the moment but it requires quite a lot of work (and even once you've got it up it requires a lot of tweaking to get it right), when I say quite a lot of work it I mean, two extra tables, some fairly complex code in your content management system for when you're adding new elements, and some pretty (at times verging on very) complex code for the actual searching algorithm. I'm still not decided as to whether it was all worth it, I'm undecided because I don't know how much more work still needs to go into it to get it working just right 😕