CODEWITHSUNDEEP
phpmysqlcodeigniterutf-8character-encoding
Phil Poore
Phil Poore

Reputation: 2256

Problems with UTF8 data with MySQL and CI

I'm building a quick website that uses the "geoanems" database for places round the world. I'm using Mysql, PHP and CodeIgniter but I'm having an issue with the UTF8 places names. I've imported the DB and everything is setup with UTF-8.

However, I'm running a simple test to see if I can get the names in the browser correctly, and I'm plagued by question marks all over the place. MySQL shows the correct hex for 1123230's name and sname, but the same char "C481" is stripped in 1127766's sname and been replaced by "3F".

What's going on? Seeing as this is MySQL running the hex why has it changed the values? Also if I can see it fine for one row, why not the other?

I've setup the server for UTF8 and ran checks on MySQL to see if that was set correctly. This doesn't work in CodeIgniter either :(. I've also sent "SET NAMES 'UTF8'", but still no joy.

This is the SQL:

$sql = 'select geonameid,name,sname, hex( name ) ,hex( sname ) from pp_adm1 where country = "af" COLLATE utf8_general_ci';

enter image description here

Sequel Pro - Showing Correct Encoding in DB

SequelPro - Showing Correct Encoding in DB

Upvotes: 0

Views: 1580

Answers (4)

Phil Poore
Phil Poore

Reputation: 2256

[FIXED] - No idea what caused the problem. 4 hours and I, DROP'd the effected table and rebuilt it VERY VERY carefully...

HEX() - function helps a lot...!!

Sorry all.. Cant give a better insight.

Upvotes: 0

venkat
venkat

Reputation: 2310

As elvispt mentioned, check the charset and dbcollat.

The following link will be useful to convert charset of mysql (if thats the problem)

http://www.bdunagan.com/2011/09/29/converting-mysql-from-latin1-to-utf8/

To verify the character set and collation, you can always query the MySQL variables:

show variables like 'collation%';
show variables like 'character%';

Upvotes: 1

elvispt
elvispt

Reputation: 4942

Check charset and dbcollat configuration parameters in database.php. Check if the charset is the same both on the database and CI. Also wouldn't hurt to check the php.ini default_charset.

Upvotes: 0

abhishek009
abhishek009

Reputation: 9

Ya also same as me. i had also this problem . And you see right which is HEX code :) The common solution is to use UTF-8 encoding file with UTF-8 database and whenever you write queries like SELECT , UPDATE whatever , befrore that you must ass this query.

For Exmaple :

/* add this line before the query */

<?php
mysql_set_charset('utf8');
SELECT * FROM bla bla bla....  

?>

and also 1 thing i want to tell you that whenever you connect with database same thing will be added .

For Example .

<?php

mysql_set_charset('utf8');
mysql_connect('localhost','root','');
mysql_select_db('yourDatabase');

?>

I hope this examples will help . anyways Sorry for my Bad ENGLISH :(

Upvotes: 0

Related Questions