Concerted to SMF Manually and Funny Characters Appeared

[mattizzy]

Hello, I recently moved to SMF from a self created forum script. I just backed up my database and created another database called BACKUO and inserted all data from the self created forum script.

I head over to SMF database which will created on install, I used a php command to insert data from BACKUP to SMF and I started seeing “ ,”, ‘ e.t.c in between posts. can you explain why this happened and how to fix?

[shawnb61]

You wrote the conversion script yourself?

There is something wrong with your charset in conversion.

First guess is your script didn't issue a  SET NAMES to get mysqli thinking in UTF8.

Charset of source db?   Charset of smf?  Did you issue a SET NAMES in your converter?   Or issue a mysqli_set_charset()?

[mattizzy]

Yes I created it my self crested with regular php INSERT SELECT Statement. by inserting from My Custom Site DB to SMF DB

[shawnb61]

Charset of source db? 
Charset of smf?
Did you issue a SET NAMES in your converter?   
Or issue a mysqli_set_charset()?

[mattizzy]

Character of Custom site DB UTF8mb_Unicode,

Charset of SMF is UTF8_general_ci

[shawnb61]

Did you issue a SET NAMES in your converter?   

Or issue a mysqli_set_charset()?

[mattizzy]

Charset of source db? 
Charset of smf?
Did you issue a SET NAMES in your converter?   
Or issue a mysqli_set_charset()?

How can I know the charset, is the collocation prefix the charset?

Tell me how to set names also so I can run the script again?

The source DB and SMF DB are using the same collocation. latin1_swedish_ci

but the columns collocation are fluctuating from utf8_general_ci to utf8_bin respectively e.t.c

[shawnb61]

Yes, the charset is the prefix on the collation.  latin1_swedish_ci is the "latin1" charset (aka ISO 8859-1) with the swedish_ci collation. 

The reason it is easy to get confused is that MySQL has a hierarchy of defaults so you can see different settings depending on where you look.  There is a database-level default, that is used when making records.  There are also record-level defaults used when adding columns. 

Despite this, when making updates to MySQL databases, you need to inform it what charset you're working with via SET NAMES or mysqli_set_charset(). 
   https://stackoverflow.com/questions/1650591/whether-to-use-set-names

IMO, it really oughta know what it's updating & not be told what it's updating...   But it nonetheless does need to be told via SET NAMES or mysqli_set_charset().  Without doing so bad things happen. 

So...

My suggestion is to:
(1) Modify your code to issue a SET NAMES utf8.
(2) Modify your database-level default to be utf8_general_ci (so mods don't introduce a bunch of latin1/swedish tables/columns by accident...)
(3) Reinstall SMF so everything is consistently utf8_general_ci (the SMF default when UTF8 is specified).
(4)  Try your converter again & see if the SET NAMES fixes your conversion...

Hope this helps,