Asked  7 Months ago    Answers:  5   Viewed   50 times

I am using Emoji character in my project. That characters are saved (??) into mysql database. I had used database Default collation in utf8mb4_general_ci. It show

1366 Incorrect string value: 'xF0x9Fx98x83xF0x9F...' for column 'comment' at row 1

 Answers

31

Step 1, change your database's default charset:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

if the db is not created yet, create it with correct encodings:

CREATE DATABASE database_name DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_unicode_ci;

Step 2, set charset when creating table:

CREATE TABLE IF NOT EXISTS table_name (
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;

or alter table

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE table_name MODIFY field_name TEXT CHARSET utf8mb4;
Tuesday, June 1, 2021
 
BetaRide
answered 7 Months ago
94

You do not need to do that. You are using prepared statements, which escape the variables automatically.

Wednesday, March 31, 2021
 
laurent
answered 9 Months ago
52

Have you tried stripslashes(), regarding the linebreaks just use the nl2br() function.

Example:

$yourString = "That's alln folks";
$yourString = stripslashes(nl2br($yourString));
echo $yourString;

Note: \ double slashes will turn to single slashes


You should probably setup your own function, something like:

$yourString = "That's alln folks";

function escapeString($string) {
    return stripslashes(nl2br($string));
}

echo escapeString($yourString);

There are also several good examples in the nl2br() docs


Edit 2

The reason your are seeing these is because mysql is escaping line breaks, etc. I am guessing you are using mysql_* functions. You should probably look into mysqli or PDO.

Here is an example:

$yourString = "That's all
 folks";
echo mysql_escape_string($yourString);

Outputs:

That's allrn folks

Saturday, May 29, 2021
 
saad
answered 7 Months ago
81

When you use just "localhost" the MySQL client library tries to use a Unix domain socket for the connection instead of a TCP/IP connection. The error is telling you that the socket, called MySQL, cannot be used to make the connection, probably because it does not exist (error number 2).

From the MySQL Documentation:

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs. For connections to localhost, MySQL programs attempt to connect to the local server by using a Unix socket file. This occurs even if a --port or -P option is given to specify a port number. To ensure that the client makes a TCP/IP connection to the local server, use --host or -h to specify a host name value of 127.0.0.1, or the IP address or name of the local server. You can also specify the connection protocol explicitly, even for localhost, by using the --protocol=TCP option.

There are a few ways to solve this problem.

  1. You can just use TCP/IP instead of the Unix socket. You would do this by using 127.0.0.1 instead of localhost when you connect. The Unix socket might by faster and safer to use, though.
  2. You can change the socket in php.ini: open the MySQL configuration file my.cnf to find where MySQL creates the socket, and set PHP's mysqli.default_socket to that path. On my system it's /var/run/mysqld/mysqld.sock.
  3. Configure the socket directly in the PHP script when opening the connection. For example:

    $db = new MySQLi('localhost', 'kamil', '***', '', 0, 
                                  '/var/run/mysqld/mysqld.sock')
    
Tuesday, June 1, 2021
 
Anand
answered 7 Months ago
18

You have 2 options:

  1. Use varchar

  2. Use three numeric fields, Major, Minor, Patch

  3. Use both.

Each option has its advantages and disadvantages.

Option 1 is only one field, so it's easy to get the version. But it isn't necessarily sortable, since 2.0.0 will be lexicographically higher than 10.0.0.

Option 2 will be easily sortable, but you have to get three fields.

Option 3 Can be implemented using a view:

Table tversion (
  major NUMBER(3),
  minor NUMBER(3),
  patch NUMBER(3)
)

View vversion is 
  select major || '.' || minor || '.' || patch AS version,
         major * 1000000 + minor * 1000 + patch AS sortorder from tversion;
Tuesday, August 3, 2021
 
TheLovelySausage
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :  
Share