Asked  7 Months ago    Answers:  5   Viewed   29 times

When I'm trying to insert russian text into MySQL database it inserts it like: ????????????? ?? ????????
???????°???‹?? ?° ?‹???°??

So, I have two pages: registration.php and addUser.php. In each of them

 <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

Database consist of 11 tables, each table has collation: utf8_general_ci, type: MyISAM. Each field in every table has Collation: utf8_general_ci.

When I'm writing to database directly in phpMyAdmin and then show this data to web-page. In English and Russian - all OK.

But when I'm full my form with personal data on registration.php and then going to addUser.php - all russian characters displayed like I wrote upper - on page and in database too.

    function AddNewUser($Name, $Surname, $FatherName, $Email, $Password, $Phone, $DegreeID, $RankID, 
$Organization, $Department, $Country, $City, $Address, $Job)
{
        //fetch data from database for dropdown lists
        //connect to db or die)
    $db = mysql_connect($GLOBALS["gl_kussdbName"], $GLOBALS["gl_kussUserName"], $GLOBALS["gl_kussPassword"] ) or die ("Unable to connect");

    //to prevenr ????? symbols in unicode - utf-8 coding
    mysql_query("SET NAMES 'UTF8'");

    //select database
    mysql_select_db($GLOBALS["gl_kussDatabase"], $db);
    $sql = "INSERT INTO UserDetails (
UserFirstName,
UserLastName,
UserFatherName,
UserEmail,
UserPassword,
UserPhone,
UserAcadDegreeID,
UserAcadRankID,
UserOrganization,
UserDepartment,
UserCountry,
UserCity,
UserAddress,
UserPosition) 
VALUES(
'".$Name."',
'".$Surname."',
'".$FatherName."',
'".$Email."',
'".$Password."',
'".$Phone."',
'".$DegreeID."',
'".$RankID."',
'".$Organization."',
'".$Department."',
'".$Country."',
'".$City."',
'".$Address."',
'".$Job."'
);";
    //execute SQL-query
    $result = mysql_query($sql, $db);
    if (!$result) 
    {
        die('Invalid query: ' . mysql_error());
    }
    //close database  = very inportant
    mysql_close($db);

}
?>

There also such information in phpMyAdmin:

auto increment increment    1
auto increment offset   1
autocommit  ON
automatic sp privileges ON
back log    50
basedir usrlocalmysql-5.1
big tables  OFF
binlog cache size   32,768
binlog format   STATEMENT
bulk insert buffer size 8,388,608
character set client    utf8
(Global value)  cp1251
character set connection    utf8
(Global value)  cp1251
character set database  cp1251
character set filesystem    binary
character set results   utf8
(Global value)  cp1251
character set server    cp1251
character set system    utf8
character sets dir  usrlocalmysql-5.1sharecharsets
collation connection    utf8_general_ci
(Global value)  cp1251_general_ci
collation database  cp1251_general_ci
collation server    cp1251_general_ci
completion type 0
concurrent insert   1

So I need to properly show, save and select russian text from database. Thanx! connect timeout 10 datadir usrlocalmysql-5.1data

 Answers

56

Try calling mysql_set_charset('utf8'); after connecting to the database. I think it's similar to executing a SET NAMES query, but since the PHP manual says using that function over a SET NAMES query is recommended, I'd try it.

Also, when you display your content, you could try echo htmlentities($string, ENT_COMPAT, 'UTF-8');

Wednesday, March 31, 2021
 
Tak
answered 7 Months ago
Tak
86

Besides the other answer,

Firstly, this part of your query:

INSERT INTO test-table (id, name, lastname, radio, drop, check)

should read as

INSERT INTO `test-table` (id, name, lastname, radio, `drop`, `check`)

MySQL is interpreting your table as "test minus table". Use ticks, or rename it using an underscore test_table which is a safe seperator.

Then you're using two MySQL reserved words, "drop" and "check". Use ticks for those also.

Reference:

  • http://dev.mysql.com/doc/refman/5.6/en/keywords.html

See how MySQL is telling you where the syntax error begins?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'drop, check)

and it would have shown you another one after that being

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'check)

Just to clarify, you are mixing MySQL APIs with

mysql_query("INSERT ...

which those mysql_ functions do not intermix with your present method. Use the same from connection to query

mysqli_query($connect, "INSERT ...

and of course the fact that you're open to SQL injection. Use a prepared statement:

  • https://en.wikipedia.org/wiki/Prepared_statement

You should also check for empty fields against your POST arrays such as !empty().

Sidenote: If an apostrophe or any other character is to be inserted that MySQL will complain about, then you must escape them. Either way, you should.

Plus, remember to add exit; after header. Otherwise and if you have more code below that, it may want to continue and execute.

header("location: indextest1.php");
exit;
Wednesday, March 31, 2021
 
tedders
answered 7 Months ago
66

Your text, unless you're using a rich-text editor, such as Mark-down (as here on Stackoverflow), is styled by your CSS, not by the contents of the textarea itself.

If the problem is preservation of new-lines, then you could run the string through the nl2br($text) function before storing in, or retrieving from, the database.

I'd suggest on retrieval would be better, but that's just my opinion (and I can't offer any evidence to back it up).

Friday, July 30, 2021
 
derobert
answered 3 Months ago
72

You are confusing UTF-8 with Unicode.

0x00FC is the Unicode code point for ü:

mysql> select char(0x00FC using ucs2);
+----------------------+
| char(0x00FC using ucs2) |
+----------------------+
| ü                   | 
+----------------------+

In UTF-8 encoding, 0x00FC is represented by two bytes:

mysql> select char(0xC3BC using utf8);
+-------------------------+
| char(0xC3BC using utf8) |
+-------------------------+
| ü                      | 
+-------------------------+

UTF-8 is merely a way of encoding Unicode characters in binary form. It is meant to be space efficient, which is why ASCII characters only take a single byte, and iso-8859-1 characters such as ü only take two bytes. Some other characters take three or four bytes, but they are much less common.

Saturday, August 14, 2021
 
Rafael Romão
answered 3 Months ago
80

The only thing I can think of (without finding a collation that fits your needs) is to change something at the application layer (outside of MySQL) that will take care of the differentiation.

For instance, since you don't care about case, you can do something programmatically to lower the case of all the rows in the database. Then change the collation to utf8_bin.

Then you can, in the application, convert everything to lowercase before it enters the database (I'm guessing this will not affect the diacritic characters). That way, you will still get errors if people try to enter multiple cases, you should only have to change a few lines of code to precondition stuff entering the table, and you won't have the diacritic problem.

Tuesday, August 17, 2021
 
MAC
answered 2 Months ago
MAC
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 :