MySQL Forums
Forum List  »  PHP

Re: Need help with complicated query
Posted by: Peter Brawley
Date: April 05, 2015 03:18AM

> Exact Business name match (LIKE, to correct for spelling mistakes, such as Wildes heating & cooling instead of Wilds)

Sorry, that's oxymoronic: "LIKE, to correct for spelling mistakes" is by definition inexact.

And, LIKE is useless for seeing through spelling mistakes except those you specify, eg ...tbl.name LIKE 'Wild%'... will catch 'wild, 'wilde', 'wildes' &c, but what will you do for Gustaffson, Gustaffsen, Gustavsen, Gustavson, Gustafson's &c and so on for many names?

> The fields are simply cat1 - cat6

Yikes, dreadful design, that normalisation failure ensures inefficient, awkward, slow query processing. If the requirement is to store up to six categories, there should be a biz-cats table with two columns(biz, cat) and an application rule allowing up to six biz-cats entries per biz.

So, the first thing to do is to normalise the design to that effect.

Then you'll need to discuss with the client whether soundex matching, which is crude, or something more refined like Levenshtein distance (http://www.artfulsoftware.com/infotree/queries.php) is to be preferred for fuzzy name searches.

Finally, latitude/longitude searching is hard to do efficiently, have a look at http://mysql.rjweb.org/doc.php/latlng.

Options: ReplyQuote


Subject
Written By
Posted
Re: Need help with complicated query
April 05, 2015 03:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.