CODEWITHSUNDEEP
phpmysqlregex
grilldos
grilldos

Reputation: 11

Ignore punctuation in what is being searched

I'm pulling tag search queries from URL query strings; for example, http://website.com/?tag=hellothere would snatch entries tagged with "hellothere". In order to avoid various issues and keep the URLs looking clean, the only punctuation the searches contain are + (replacing space) and - (as itself). The problem comes as there are tags that contain apostrophes, question marks, or exclamation marks -- ' ? !.

The tag columns in the database are formatted like so: "tv, squirrels, paul's junk, clumps"

So, if someone is using the url http://website.com/?tag=pauls+junk, how can I structure a query that will return rows from table yourtable whose column yourtags contains the entry "paul's junk"?

To clarify, I'd like to avoid storing two versions as it would be fairly redundant in this case. The ONLY non-alphanumeric characters we'll be using are '!?-. Only mods will be adding these tags. There will be no instances of any punctuation or special characters other than those.

And I'd like to avoid urlencode'ing special characters to keep the search strings looking "pretty". Users will not be typing in searches into a box, they'll be clicking tag links.

Upvotes: 1

Views: 1292

Answers (4)

arnold
arnold

Reputation: 465

Replace the unwanted chars before the compare like :

SELECT * FROM yourtable WHERE REPLACE(REPLACE(REPLACE(REPLACE(products_name, '?' , '' ), '!' , '' ), '-' , '' ), '.' , '' ) = 'pauls junk'

Upvotes: 0

Will Hartung
Will Hartung

Reputation: 118611

the other option is to normalize the tags in the database. Store two versions, the original, and the one with all of the punctuation stripped out. The users never see that version, but that's the field you inevitably sort on. You can also do things like make them all lower case, remove extra spaces, etc.

Upvotes: 1

John Parker
John Parker

Reputation: 54425

A simple solution would be to store both the "raw" and "processed" tags in the database, so that you'd display the "raw" tag as a part of the site output (complete with apostrophes, etc.), but use the apostrophe free "processed" tags for lookup purposes, as these would be the same as those provided via the query string.

Upvotes: 2

cusimar9
cusimar9

Reputation: 5259

If you just urlencode your querystring parameters before you add them into your links then all the encoding will be done for you

Alternatively, if what you actually want is simplified URLs but want to retain the punctuations in the tags, you could store 2 versions of each tag in the database: the tag itself, then a URL tag, which you can format however you like

Upvotes: 0

Related Questions