How to insert emoticons into MySQL using CodeIgniter

Question

How to insert unicode characters into MySQL and retrieve them using CodeIgniter? Is there any settings to be done.

This is the query when I print it.

INSERT INTO `reviews` (`description`) VALUES ('ðŸ˜€')

But, it is saving as ????.

Here is what I have done. CodeIgniter database.php

$db['default']['char_set'] = 'utf8mb4';
$db['default']['dbcollat'] = 'utf8mb4_unicode_ci';

which Collation type of reviews column you are use in mysql? — Paresh Gami, Apr 27 '15 at 10:10
reviews(table) collation : utf8mb4_unicode_ci, description(field) collation is also set to utf8mb4_unicode_ci — Satish Ravipati, Apr 27 '15 at 10:23
U shud not insert emoticons into the DB. Insert them after utf8_encode of php but doing it directly is not recommended. You can later interpret and change the same during display. — Arcanyx, Apr 27 '15 at 13:29

score 3 · Accepted Answer · answered Apr 27 '15 at 11:04

It works fine for me when I test it (editor is Sublime Text).

Create MySQL table:

CREATE TABLE `reviews` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Run an insert in CI:

$this->db->insert('reviews', array('description' => 'ðŸ˜€'));

Grab our data back:

$result = $this->db->get('reviews');

Output our result:

echo '<pre>';
print_r($result->row());
echo '</pre>';

Our result:

stdClass Object
(
    [id] => 1
    [description] => ðŸ˜€
)

Thanks, it is working, I was just worried about the encrypted data saved into database...it is getting retried well — Satish Ravipati, Apr 28 '15 at 15:11

score 0 · Answer 2 · answered Oct 02 '20 at 17:52

CREATE TABLE reviews ( id int(11) unsigned NOT NULL AUTO_INCREMENT, description varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

add the following code in database.php

$db['default']['char_set'] = 'utf8mb4'; $db['default']['dbcollat'] = 'utf8mb4_unicode_ci';

How to insert emoticons into MySQL using CodeIgniter

2 Answers2