Thread

Hi,

I’m new to codeIgniter so please bear with me,

I am using the following code to save data collected from a form into a MySQL database.

$query = $this->db->query("INSERT INTO competition_answers (competition_id, question_id, user_id, answer) VALUES (".$this->db->escape($competition_id).", ".$this->db->escape($value).", ".$this->db->escape($user_id).", ".$this->db->escape($answer).") ");

which produces the following query

INSERT INTO competition_answers (competition_id, question_id, user_id, answer) VALUES (1, '3', '27', 'test with £')

and work apart from the £ symbol not being inserted into the table.

I have set the encoding to UTF8 and the database collation is set to the same.  If I run the above query directly in phpMyAdmin the query run correctly and the £ symbol is insert correctly.

I am able to save other symbol such as !”$%^&* fine just the £ is missing.

Not sure where to start debugging?

Any suggestions?

Thanks for your help!!!

Still trying to debug, if I manually connect to the database and use mysql_query() to run the above sql statement the data is stored correctly in the database.

So it has to be something I’m missing with codeigniter?

What happens when the follow is run on the data

$this->db->escape()

Cheers

Sorry to bump… On a deadline of today the get this section finished…

Thanks

you can find out what it does in:

system/database/drivers/[database type]/[database type]_driver.php

I failed to mention that :

system/database/drivers/DB_driver.php

contains the actual ‘escape’ function, but the path I gave you contains the escape_str function which is what would be called by escape().

Stumped

function escape_str($str)    
    {    
        if (is_array($str))
        {
            foreach($str as $key => $val)
               {
                $str[$key] = $this->escape_str($val);
               }
           
               return $str;
           }

        if (function_exists('mysql_real_escape_string') AND is_resource($this->conn_id))
        {
            return mysql_real_escape_string($str, $this->conn_id);
        }
        elseif (function_exists('mysql_escape_string'))
        {
            return mysql_escape_string($str);
        }
        else
        {
            return addslashes($str);
        }
    }

I am guessing that is the common escape_str function in codeigniter.

Ok, going to rule out a problem with this function.

Currently in the DB_drive.php i have following set

var $char_set     = ‘utf8’;
var $dbcollat     = ‘utf8_general_ci’;

And in the config file

$config[‘charset’] = “UTF-8”;

And in the database.php config

$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

And the mysql table field is set to utf8_general_ci

Any one any suggestions?

I’m not sure what the problem is, but have you considered a quick rewrite to:

$sql = “INSERT INTO competition_answers (competition_id, question_id, user_id, answer) VALUES (?, ?, ?, ?)”;
$query = $this->db->query($sql, array($competition_id, $question_id, $user_id, $answer));

(if you don’t like Active Record)

Might save you lots of time looking for something obscure to just let it work the way it wants to.

kirk, try to change the position of the pound sign

other thoughts:
- did you try euro, dollar or other signs?
- check our column properties, is the max. length enough to store your data? (i got this one before, it toke me an hour to figure out the problem (-: )
- does a manual insert work? (via phpmyadmin, a single php script, etc)

Hi,

other signs work ok !”$%^&*()_,

If I run the script via phpmyadmin everything is insert correctly. Also if I use a simple script outside of codeigniter framework, it work as expected. Also if I use codeigniter with insert ‘test £ test’ only ‘test ’ is inserted into the database, the field is set to mediumtext

| Field  |   Type     | Collation       | Attributes | Null | Default 
| answer | mediumtext | utf8_unicode_ci |            | Yes  | NULL

If I use

$sql = “INSERT INTO competition_answers (competition_id, question_id, user_id, answer) VALUES (?, ?, ?, ?)”;
$query = $this->db->query($sql, array($competition_id, $question_id, $user_id, $answer));

I get the same problem

If I use htmlentities on the value, the pound sign encode to $pound; which works but there are over 100 different insert queries already written and can’t go through each and rewrite, so really need to find a solution or an understanding of why???

I have tried to use active records and still had the same problem.

Thanks for helping!

here a link:
http://forums.mysql.com/read.php?20,172624,172861#msg-172861
this guy solves it with setting the charset to latin1 and the collation to latin1_swedish_ci
maybe u can try that one?

another link, a complete dedication to your problem:
http://www.byteflex.co.uk/en/fun_with_utf8_php_and_mysql.html

enjoy!

Cheers Rob

I’m using

$this->db->query("SET NAMES 'latin1'");

And everything inserts correctly, quick fix for now, but long term need to change the database from UTF8 to latin1.

Can you think of any problem in storing the data as latin1 in a uft8 database?

Cheers

well kirk, I don’t know the exact diffs between those two 😊
I think a query on google (latin1 versus utf8) gives a lot stuff to read.

Did you try the second fix, with the content-typer header?
Ow, and did you try it in different browser?

after hours and hours and hours and hours and hours of messing changing / reading changing / testing /changing all I needed to do was add

<meta http-equiv="content-type" content="text/html; charset=utf-8">

To the header view…..

Leason learnt!

Thanks again Rob

ghehe, NP kirk.
Don’t you just hate it sometimes, being a webdeveloper? 😛