[thelist] character encoding nightmare

Garth Hagerman hagerman at mcn.org
Fri Mar 17 01:48:46 UTC 2017

>If you are not concerned about being able to search the data, maybe encoding everything use Base64 before you put it into the database would be helpful.
The data does need to be searchable.

>But it sound like you need to look at the PHP version of each service, and the DB setup / versions too. Something is out of whack there. Maybe comparing the base PHP.config files would help - see if there is something in there that is causing the insert into the second DB to be "extra" converted.
Site1 uses PHP 5.2 and PhpmyAdmin 3.5.5
Site2 uses PHP 5.6 and Phpmyadmin
The configurations are the defaults set by the hosts. I’m not sure if I’d be allowed to change them.
One possible clue I’ve failed to mention previously: site2 recently migrated to a new server and had some software updates. Before this, the problem was fairly minor. The insert to DB2 query would fail once in a while, I’d export that row, copy the SQL from DB1, paste it in DB2, and all was well. It was a mystery I never tried super-duper hard to solve. Now, the problem is much worse. Data which had been fine now displays with gobbledeegook characters sprinkled throughout. I’ve tried reverting to an older PHP version, but that doesn’t help. Jumping to PHP7 blows up the whole site.

>If you can provide an example of a raw string, and the different results of your encoding and conversions in addition to what the final text looks like when pulled back out of the DB, we might be able to recognize the hiccup via the changes being made.
The results are easy to see and compare:
Site 1 - <http://universitypressaudiobooks.com/detail.php/523>
Site 2 - <http://militaryaudiobooks.com/detail.php/523>
The raw strings aren’t so simple; my client does the data entry. It’s several fields in a few tables being copied and pasted from various sources. Perhaps I’ll set up a stripped down version for testing/adjusting that’s just one field so I can eliminate the complications. I was hoping there’d be a nice simple fix and I wouldn’t have to go down that route.

More information about the thelist mailing list