PHP, or MYSQL, or Unicode issue... I don't know for sure

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
Bill_Thompson
Registered User
Posts: 259
Joined: Mon Jun 20, 2005 2:58 am
Location: Bellevue, WA
Contact:

PHP, or MYSQL, or Unicode issue... I don't know for sure

Post by Bill_Thompson »

I moved my database from one provider to another and some characters were transformed. I figure this is a double-byte issue. But I need to go in and fix this. There are only clues as to what the characters should be changed to. For example, If I know what the city of "Alingsås, Sweden" could be, then I can know what to replace "å" with.
Bill_Thompson
Registered User
Posts: 259
Joined: Mon Jun 20, 2005 2:58 am
Location: Bellevue, WA
Contact:

Re: PHP, or MYSQL, or Unicode issue... I don't know for sure

Post by Bill_Thompson »

I have found that it is
Alingsås

So that is one character down.


And I know that
résumé must be
Résumés

So that makes two. This is going to be a lengthy process.

Has anyone seen this before or know where I can find some sort of conversion table?

My forum is full of such characters. For example there is
Repsol Exploración (Spanish Company, Moscow, Russia)
StefanL
Registered User
Posts: 2265
Joined: Wed Jul 23, 2003 10:10 pm

Re: PHP, or MYSQL, or Unicode issue... I don't know for sure

Post by StefanL »

Here is probably what happened to you. On your old host they were using a MySQL version 4.1.x or higher and on your new host they are using MySQL version 4.0.x or lower. I don’t know an easy fix for this, when I had the problem I did it the hard way. Can you still access your old host?
When I suggest that you should alter your files, BACKUP them BEFORE.
Bill_Thompson
Registered User
Posts: 259
Joined: Mon Jun 20, 2005 2:58 am
Location: Bellevue, WA
Contact:

Re: PHP, or MYSQL, or Unicode issue... I don't know for sure

Post by Bill_Thompson »

StefanL wrote:Here is probably what happened to you. On your old host they were using a MySQL version 4.1.x or higher and on your new host they are using MySQL version 4.0.x or lower. I don’t know an easy fix for this, when I had the problem I did it the hard way. Can you still access your old host?
I suspect this is some sort of mish-mashing of Unicode
with ascii code that has caused this.

According to an ascii table
( http://www.petefreitag.com/cheatsheets/ascii-codes/ )
à is 195 in ascii which is C3 in hex
¥ is 165 in ascii which is A5 in hex

å is C3A5 in UTF-8

http://www.fileformat.info/info/unicode ... /index.htm

So at least I have a clue. In order to do this without having to do it "the hard way" as you did, I suspect, by hand, I will have to write a litte C++ code to convert everythng after I dump the database into a file.
StefanL
Registered User
Posts: 2265
Joined: Wed Jul 23, 2003 10:10 pm

Re: PHP, or MYSQL, or Unicode issue... I don't know for sure

Post by StefanL »

There is an easier way than write some code if you can access your old site. On this site install [2.0.20] SQL Backup in PHPBB.
Download the backup to your disk and open it in a text editor and
FIND

Code: Select all

CREATE TABLE phpbb_search_wordlist ( word_text varchar(50) NOT NULL, word_id mediumint(8) unsigned NOT NULL auto_increment, word_common tinyint(1) unsigned NOT NULL, PRIMARY KEY (word_text), KEY word_id (word_id));
REPLACE WITH

Code: Select all

CREATE TABLE phpbb_search_wordlist (
  word_text varchar(50) binary NOT NULL default '',
  word_id mediumint(8) UNSIGNED NOT NULL auto_increment,
  word_common tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY (word_text),
  KEY word_id (word_id)
);
or you will get the famous duplicate key error. Restore it to your new server. That’s what I call the hard way. But all extended characters will be correct.
Hope this helps.
When I suggest that you should alter your files, BACKUP them BEFORE.
Locked

Return to “2.0.x Support Forum”