Save pound symbol to database

[eluser]kirk112[/eluser]
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.

Code:

$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

Code:

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!!!

[eluser]kirk112[/eluser]
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

Code:

$this->db->escape()

Cheers

[eluser]kirk112[/eluser]
Sorry to bump... On a deadline of today the get this section finished...

Thanks

[eluser]theprodigy[/eluser]
you can find out what it does in:

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

[eluser]theprodigy[/eluser]
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().

[eluser]kirk112[/eluser]
Stumped

Code:

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

Code:

$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?

[eluser]jmadsen[/eluser]
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.

[eluser]Rob Gordijn[/eluser]
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)

[eluser]kirk112[/eluser]
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

Code:

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

If I use

Code:

$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!

[eluser]Rob Gordijn[/eluser]
here a link:
http://forums.mysql.com/read.php?20,1726...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_ut...mysql.html

enjoy!