How can I export my database with emojis in .sql format?

Hosting Support
1

I am in depression… I have free account of infinityfree.
My problem is when i export my database in .sql format. The emojis are stored as ??? in .sql file so, how can I export my database in .sql format with emojis?

2
1 Like
3

Every thing is in utf8mb4 and emoji display on my webpage correctly, but I am asking How can I export my database with emojis in .sql format?
The emoji is replaced with question(??? ) in my exported filename.sql.

4

you can use their unicode

1 Like
5

Please step by step
im using utf8mb4_unicode_ci…
everything is ok but manually when i insert emoji in table’s column the icons replaced with ??? how to solve??

7

you can also use prepared statements instead of altering directly tables. at this solution. (example) :hourglass: becomes ⌛ with prepared stmt

8

How can i change the value of MySQL variables in infinityfree??

9

mysql variables? do you mean php variables?

11

Yes!
Run variable changed value query, before inserting something?

12

How can i edit my.cnf or (my.ini) file?

13

my.cnf can’t be edited on free hosting.

14

Which file encoding do you select when exporting the file in phpMyAdmin? Please make sure that’s set to UTF-8.

Also, how are you viewing this .sql file? Are you using some text editor for that? Which file encoding is the text editor configured to use?

If you haven’t done so yet, you could try to import the .sql file to another database and see if that restores the emoji.

1 Like
16

How do you know the emojis are stored like that? Do you know for certain it’s not just phpMyAdmin which doesn’t work well with it?

1 Like
18

Sorry i was trying to know what’s the emoji code called :sweat_smile:

would you try to change it to latin1_swedish_ci?

1 Like
19

also? use this in your query ⌛
it should make glasshour emoji

1 Like
21

I think infinityfree is not good hosting service provider.
So I am going to quit!:cry:

22

for the emoji issue?

23

OK, I did some testing with this, and I can confirm that the database itself does support emoji without any problem. This is the script I used to test:

<?php

$host = 'sqlXXX.epizy.com';
$db   = 'epiz_12345678_testdb';
$user = 'epiz_12345678';
$pass = 'hunter2';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
try {
     $pdo = new PDO($dsn, $user, $pass, $options);
} catch (\PDOException $e) {
     throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

$pdo->prepare("INSERT INTO unicode_test (content) VALUES (?)")->execute(["😀 😁 😂 🤣 😃 😄 😅 😆 😉 😊 😋 😎 😍"]);

$stmt = $pdo->prepare("SELECT content FROM unicode_test ORDER BY id desc LIMIT 1");
$stmt->execute();

echo "Last result: ".$stmt->fetchColumn(0);

I created the particular table through phpMyAdmin, and this is the SQL statement phpMyAdmin generated for the table definition:

CREATE TABLE IF NOT EXISTS `unicode_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) CHARACTER SET utf8mb4 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

What this script does it it inserts provided unicode characters in the database, and then retrieves these characters from the database and prints them.

And if you run this script yourself, you’ll see that the unicode characters are there.

It does appear that phpMyAdmin does not work with these unicode characters at all. But everything else does work, so it’s just phpMyAdmin. And I’m going to try to get that addressed.

If you want to export your database right now, you may be able to install phpMyAdmin on your own website and export the database from there. However, if you do go that route, I would highly recommend to use IP whitelisting or directory password protection to restrict access to phpMyAdmin, because it’s a common target for hackers.

2 Likes
24

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.