How to deal with search criteria with apostrophe

PHP
1

I am assuming this can be done on the php side of things.

Basically my site has a search facility where a location is entered into a form field.

I have a problem where if the location in the database is

St Mary’s

And someone enters

St Marys

My search is not finding the record in the database.

What is the best way of dealing with this? I guess there could be a similar issue with hypens

For example, the database may have

Kingston-Upon-Thames

And someone could enter

Kingston Upon Thames

Any advice much appreciated

Thanks as always

Paul

2

Store the variations (with/without apostrophes or hyphens, different spellings) of the location names in the database so that you can search against them.

3

If I was going to expect some ambiguous search terms like you detail, I’d be looking to offer ranked search results using a variant of FULL-TEXT search.

You searched for:- St Marys

We found:-
St Marys (100%)
St Mary’s (90%)
St Maries (80%)
…
…

4

Sorry, for delay in replying - I just found the notification in my SPAM folder.

I was hoping there was just a simple way to remove all apostrophes and replace hyphens with spaces but thinking about this, it would probably need to be done in the MYSQL query?

I don’t want to have to store variations as it seems excessive if there is just a simple way of calling a function like TRIM on the field names before comparing.

Shoudl I ask this question in the MYSQL forum instead?

5

If you don’t like idea of storing normalized form of text in the table, you can bother yourself not by posting in mysql forums.
Because answer will remain the same: Normalization. Or fulltext search with relevance.

6

Eep didn’t’ mean to sound ungrateful. I was just thinking that there may be an easier way.

If I am only concerned about apostrophes and hyphens, I thought maybe there was a way of calling a simple function to remove/replace these characters when making the query.

If I was to store these “normalized” forms in my database table, where would I store them.

For example, the issue could be on any of the following fields

address1
address2
townCity
county

Do I need additional fields for each of these?

7

thought maybe there was a way of calling a simple function

this function will lead your database to hang.
because it will search through the whole table, not picking only one row using indexes

Do I need additional fields for each of these?

yes

by the way, do you search using LIKE or equation?

8

Thanks Shrapnel

My current search query is as follows:




$sql = "SELECT ? FROM accommodation WHERE status=5 AND (accommodation.address2 LIKE '%".$db->prepare($this->loc)."%' OR accommodation.townCity LIKE '%".$db->prepare($this->loc)."%' OR accommodation.county LIKE '%".$db->prepare($this->loc)."%' OR accommodation.postcode LIKE '%".$db->prepare($this->loc)."%')";

The prepare function is as follows:



	function prepare($string) {
		return mysql_escape_string(trim($string));
	}
9

well, if you’re using like, it doesn’t matter then.
you can use some replace function here.
but when you’ll notice that your query goes to slow (it can be either from the data size or site traffic), you’ll have to switch to more efficient technology

10

Thanks Shrapnel for all the great advice. I will look into this replace function solution.

Fortunately my site does not have much traffic or indeed many entries in the database so things run OK at the moment.

Thanks again

Paul