Changing Database Collations

[Skhilled]

Database collations relate to how a database views and displays various language characters. For instance, an all English collation will not display accented characters in other languages well such as "æ, ò, ô, etc". For the most part, all collations should always be the same. To tell what your particular server collations are you should look in phpmyadmin or ask your hosting.

Different servers may have different collations while some will have the same. When you install software on a server (such as SMF) or add mods, themes, etc. from within a program such as SMF they will be installed and set to whatever collation the default is for the server.

Problems arise if you change hosting or move to another server that does not have the same collations. A previous server you were on may have had "utf8_general_ci" as the default and the new server that you just moved to may be set to "latin1_swedish_ci".

Installing things on the old server will give you "utf8_general_ci" collations. But after the move anything you install, such as a mod, will give you "latin1_swedish_ci". When you view your database in phpmyadim you will see "mixed" collations some for one and some for the other. The more you change hostings and encounter different collations you will get even more mixed collations.

The point is: mixed collations can cause problems for your mods and you'll may never know why.

Here are 2 easy ways to fix this:

1. Use the following script by making the necessary changes described below and uploading it to the root folder of your forum:

Code Select Expand

<?php
$db = mysql_connect('localhost','myuser_mydbuser','mypassword');
if(!$db) echo "Cannot connect to the database - incorrect details";
mysql_select_db('myuser_mydbname'); $result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {
foreach ($tables as $key => $value) {
mysql_query("ALTER TABLE $value COLLATE utf8_general_ci");
}}
echo "The collation of your database has been successfully changed!";
?>

Make sure to substitute in the above script:

- myuser_mydbname with your database name;

- myuser_mydbuser with your mysql username;

- mypassword with your password for the mysql user;

- utf8-general_ci with your new collation if different;

2.  Download: Phoca Changing Collation Tool

[hackrishna]

i used to get error messages of UTI-8 collations. It got solved after i ran a query thru phpmyAdmin, oldiesman had helped me for that a long ago.

Last week i found the same error while banning a member I have changed all collations to UTF-8 still why this hapening .. Can you help me ? 

[Skhilled]

Oldiesmann is a great guy!

First, you need to find out what your server collation is. Go to phpmyadmin and on the home page you'll see what it is supposed to be. You should confirm this with your hosting.

2nd, if you have moved from hosting to hosting you probably have mixed collations. go into phpmyadmin and select your forum's database. The left most column will show you the collations for all of your tables. At the very bottom of that column you'll see what the collation should be for the entire database.

[Skhilled]

BTW, if they not all the same then you should change them so they are using the tool in the first post. You can also manually do it in phpmyadmin but it will take you a month of Sundays to get it done. LOL

[hackrishna]

Oldiesmann is a great guy!

First, you need to find out what your server collation is. Go to phpmyadmin and on the home page you'll see what it is supposed to be. You should confirm this with your hosting.

2nd, if you have moved from hosting to hosting you probably have mixed collations. go into phpmyadmin and select your forum's database. The left most column will show you the collations for all of your tables. At the very bottom of that column you'll see what the collation should be for the entire database.

Yeah indeed he is!

Well, i have did all that you said ... all the collations are UTF-8 in phpmyAdmin ...

BTW, if they not all the same then you should change them so they are using the tool in the first post. You can also manually do it in phpmyadmin but it will take you a month of Sundays to get it done. LOL

LOL 
There is a query for that ... i will paste it here once i get back to my PC 

[Skhilled]

Yes, I saw the query on the net. i didn't post it here because I'd have to explain it in detail so new users could understand it. I try to post things here that way or explain them so a child can understand it.

[hackrishna]

Yes, I saw the query on the net. i didn't post it here because I'd have to explain it in detail so new users could understand it. I try to post things here that way or explain them so a child can understand it.

you're right.... it will confuse users, so i'm not posting it here

[Skhilled]

Actually, I think the script is in the first post. LOL But no one has asked or complained about it yet. I'll explain it further in needed. a lot of new users don't know much about cPanel or any of the things in it. So, I try to make sure there's a tutorial explaining such things when I post something like that or will write one soon.