So many problems seem to come up with branded food names.
Some have + signs
There is T.G.I.F. Friday’s with a single quote in the name
Then some food names have double quotes "
I am pulling the names using PHP/SQL. Wrapping it up in JSON, then parsing that JSON.
I guess I will try to escape characters on the PHP/SQL query part and see if that fixes things.
This post is half ranting, half asking: what is your experience with escaping these trouble characters? (As it were)
Wow. I do not love data-grooming-before-import. bleh
I think I usually went through a process, though:
define the stringifying character like apostrophe and replace this first with, say, an escaped version of that
it’s probably then best to also replace the other one (double-quote) with its equivalent
review how the database was installed from a collation-indexing standpoint because things may sort in a strange way if this is wrong versus the encoding of the incoming data
decide if you can store unicode or not. if not then “Black Bean Olé Seasoning” could be a problem (I sometimes see DBAs do a replacement of é => e’ which is probably more like e’ or e0x39 possibly but I honestly think that looks just wrong to me.)
decide if you want to store the “&” equivalent of an ampersand (the HTML-friendly display code itself) for things like this. this isn’t a good solution if you’re also doing EDI with other businesses, creating sales orders and such with them since that may or may not look good in those reports.
In a case like this I usually ask the client to begin with the end: what kinds of reports do you expect to see from this and how are they produced? Okay, it’s just a web page… then you could in theory just store HTML-friendly equivalents. These days, though, it seems like unicode is the way to go.