CODEWITHSUNDEEP
databasecodeignitercharacter-encodinginsert
SimDion
SimDion

Reputation: 1080

codeigniter insert : special characters trims my string values

I have a problem executing insert commands that are loaded from a text file. I use codeIgniter "file" helper to load an sql line then I perform a simple db->query(content of my file). The problem is when the sql is loaded from a file the special character trims the rest of the string.

Here is an example that works

INSERT INTO test(test) VALUES("<p>There is <strong>no special character</strong> in this string</p>");

Example that will not work

INSERT INTO test(test) VALUES("<p>this character <em>é</em> is a <strong>special character</strong></p>");

In the second example, only "<p> this character <em>" will be saved. This is weird because if I execute the same line in phpMyAdmin it works fine.

Anyone knows why this happens? or what I do wrong?

Thanks

Here is a simple "step to reproduce".

Simple table :

CREATE TABLE `test` (
`test` TEXT CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
) ENGINE = InnoDB;

A file "application/view/text.txt" that contains :

INSERT INTO test(test) VALUES("<p>this character <em>é</em> is a <strong>special character</strong></p>");

The code I use to perform the insert

$this->load->helper('file');
$loaded_sql = read_file(BASEPATH . "../application/views/test.txt");
$this->db->query($loaded_sql);

My database config

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

CI Config

$config['charset'] = 'UTF-8';

Upvotes: 0

Views: 4251

Answers (2)

SimDion
SimDion

Reputation: 1080

I finally got it. Needed to use "utf8_encode()" when reading the file to unsure that special character gets encoded properly. Text file must be encoded in ANSI (default notepad encoding). If file is UTF-8 or UNICODE it won't work!

Code that resolved the problem :

$loaded_sql = utf8_encode( read_file(BASEPATH . "../application/views/test.txt") );

Upvotes: 2

monish k
monish k

Reputation: 45

Try to use before inserting record in table.

$this->db->db_set_charset('latin1', 'latin1_swedish_ci');

Make sure you have same setting in table & table column.

Upvotes: 1

Related Questions