CodeIgniter inserting data to database with quotes in string

Question

data is look like

$data = array
(
'question' => 'How to say “something” in'
'answer_one' => '1. Some Answer'
...
'correct_answer' => 1
);

I use

$this->db->insert('questions',$data);

When i run

$this->db->last_query()

I get:

INSERT INTO `questions` (`question`, `answer_one`, ... , `correct_answer`) VALUES ('How to say “Something”  in', '1. Some Answer', ... ,'1')

and Data saved as

How to say ?Something? in

And the

$data = array
(
'question' => 'My Name is…'
'answer_one' => '1. Some Answer'
...
'correct_answer' => 1
);

is run as

INSERT INTO `questions` (`question`, `answer_one`, ... , `correct_answer`) VALUES ('My Name is…', '1. Name', ... , '1')

Is inserted as

"My Name is?"

$data = array
(
'question' => 'What's your name?'
'answer_one' => '1. Some Answer'
...
'correct_answer' => 1
);

is inserted as

What?s your name?

And Question table is like

CREATE TABLE `questions` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 `question` varchar(200),
 `answer_one` varchar(80),
 ...
 `correct_answer` int(2),
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

is it just some funky double quotes you cut and paste off the web? — Drew
– Drew, Commented Nov 1, 2015 at 16:43
perhaps edit the question and show more info of the section of code or the flow that could intercept this and tweak it. Perhaps lose a tag and add Excel — Drew
– Drew, Commented Nov 1, 2015 at 19:20
can you show a schema of table questions and the actual raw string that is performed without ellipses I am confused how to help. Perhaps the issue is just getting the correct unicode value for the fancy quotes, and using the str_replace in Abdulla's answer — Drew
– Drew, Commented Nov 2, 2015 at 1:40

Community · Accepted Answer · 2023-11-21 15:10:29Z

1

Use htmlentities()

$data = array
(
    'question' => htmlentities('How to say “something” in'),
    'answer_one' => '1. Some Answer'
    ...
    'correct_answer' => 1
);

Or

Use str_replace()

$value = 'How to say “something” in';
$data = array
(
    'question' => str_replace('"', '', $value);,
    'answer_one' => '1. Some Answer'
     
     'correct_answer' => 1
);

edited Nov 21, 2023 at 15:10

CommunityBot

11 silver badge

answered Nov 1, 2015 at 16:50

Abdulla Nilam

39.7k18 gold badges85 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2473015 Over a year ago

didn't worked since " “ " and " ” " are not quotes :(

Drew · Accepted Answer · 2015-11-02 02:37:54Z

1

It is an issue with the client characterset. When, based on what I had before in mysql command line client (when in client), and I run status;, I get, among other things:

mysql> status;

mysql  Ver 14.14 Distrib 5.6.24, for Win64 (x86_64)

Connection id:          88
Server version:         5.6.24-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    cp850
Conn.  characterset:    cp850

Then:

mysql> use so_gibberish;
Database changed
mysql> select * from questions;
+-------------+----------------------------+----------------+----------------+---------------------+
| question_id | question                   | answer_one     | correct_answer | created_at          |
+-------------+----------------------------+----------------+----------------+---------------------+
|           1 | How to say ?Something?  in | 1. Some Answer |              1 | 2015-11-01 21:06:23 |
+-------------+----------------------------+----------------+----------------+---------------------+

Even creating another database like:

CREATE DATABASE newdb CHARACTER SET utf8 COLLATE utf8_bin;
use newdb;

CREATE TABLE `questions` (
 `question_id` int(11) NOT NULL AUTO_INCREMENT,
 `question` varchar(200) CHARACTER SET utf8 COLLATE utf8_bin, -- utf8_unicode_ci,
 `answer_one` varchar(80),
 `correct_answer` int(2),
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`question_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

or

ALTER TABLE questions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;

Did not make a difference.

It wasn't until I ran

mysql> charset utf8;
Charset changed
mysql> select * from questions;
+-------------+--------------------------------+----------------+----------------+---------------------+
| question_id | question                       | answer_one     | correct_answer | created_at          |
+-------------+--------------------------------+----------------+----------------+---------------------+
|           1 | How to say "Something"  in     | 1. Some Answer |              1 | 2015-11-01 21:06:23 |
+-------------+--------------------------------+----------------+----------------+---------------------+
1 row in set (0.00 sec)

was it good. Note when I run status; now I get

Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8

Refer to the mysql manual page entitled Connection Character Sets and Collations

Connection Lifetime

That worked fine, til I started the client up fresh again. So there are INI file or CONF file settings

Mysql Workbench

I never had an issue over there at all, even from the beginning. So it has a different client characterset

Your World

Whatever that connection is, it needs to get the client characterset over to UTF8

answered Nov 2, 2015 at 2:37

Drew

25k10 gold badges47 silver badges81 bronze badges

4 Comments

user2473015 Over a year ago

When i chage to UTF-8 data saved only "How to say", "My Name is", "What"

Drew Over a year ago

what technically is the client app? Is it mysql command line utility, a PHP connection? Show the code in detail, maybe in a pastie.org page if you need to. Then describe what other angle (what code, what utility) you went in to see the data wrong. There are too many permutations to make assumptions here

user2473015 Over a year ago

it is a codeigniter application. I'm using active record and query builder of it.

user2473015 Over a year ago

$this->db->insert('questions',$data);

Collectives™ on Stack Overflow

CodeIgniter inserting data to database with quotes in string

2 Answers 2

1 Comment

mysql> status;

Connection Lifetime

Mysql Workbench

Your World

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

mysql> status;

Connection Lifetime

Mysql Workbench

Your World

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related