2

When I try to insert a utf8 charcter into my mysql web server database (table is also having collation: utf8_general_ci) unknown junk characters are inserted into the db. I'm trying to input this name (ടോണി) in malayalam language into mysql db using an html file and when this value is submitted on the text box and send to the server db, it appears as like this: (ടോണി). I have also added this line in my php file:

mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'",$con);

but still no use. The name is in malayalam. But what I get from db is unknown language. How is that happening?? I will show you my php files and html files:

insert.php

<?php   
$con=mysqli_connect("localhost","username","password","db_name"); 
if (mysqli_connect_errno($con)) 
{ 
    echo "Failed to connect to MySQL: " . mysqli_connect_error(); 
} 
$txt= $_POST['ta']; // get the unicode text from a submit action. 
$unicodeText = $txt; 
mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'",$con);
$cQry= "insert into doc_test (unicodeText) values ('".$unicodeText."')" ; 
if(!mysqli_query($con,$cQry))
{
    die('Error: ' . mysqli_error($con));
}
echo "1 record added";
mysqli_close($con);
?>

This is my html file:

<html>
<head>
<meta charset="UTF-8" />  
</head>
<body>

<form action="insert.php" method="post">
Name: <input type="text" name="ta">
<input type="submit">
</form>
</body>
</html>

when i run this on my server and enter a value in malayalam in the textbox, and press the submit button, the value will be inserted into the mysql db, but it is not the one which I had entered in the textbox. Also there is a warning shown after the value is entered in the db. It is shown below:

PHP Error Message

Warning: mysql_query(): supplied argument is not a valid MySQL-Link resource in /home/a6001661/public_html/malayalam/insert.php on line 9

1 record added

What is the possible reason for this warning?? Can someone please correct me out. Also suggest the ideas to enter the value in the correct font malayalam in db. Thanks in advance...

njnjnj
  • 928
  • 4
  • 22
  • 52
  • You're mixing MySQL APIs, they do **not** mix. Change to `mysqli_query` and move your connection variable at the beginning. – Funk Forty Niner Jul 24 '14 at 16:27
  • Use this `mysqli_query($con,"SET NAMES 'utf8' COLLATE 'utf8_general_ci'");` – Funk Forty Niner Jul 24 '14 at 16:30
  • 1
    He means that not all function names that start with `mysql` are from the same extension. Just look them up in the manual and you'll see they're filed under different chapters. – Álvaro González Jul 24 '14 at 16:31
  • I know that i should use mysqli_real_escape_string for validating/ escaping the string. But I have done this way to make it simple. – njnjnj Jul 24 '14 at 16:31
  • Side note: With Malayalam you are going to run into deep trouble if you are not going to use prepared statements. – bansi Jul 24 '14 at 16:32
  • *"then should I use mysqli everywhere in my program, since mysql is deprecated?"* - Yes, but not just that, again, see my [`first comment`](https://stackoverflow.com/questions/24939185/malayalam-font-characters-saving-into-the-mysql-database-as-unknown-characters#comment38755975_24939185). – Funk Forty Niner Jul 24 '14 at 16:33
  • I strongly recommend you don't use `mysqli_real_escape_string()`. It's very verbose and error prone. Use prepared statements when possible. – Álvaro González Jul 24 '14 at 16:33
  • Use [**`mysqli_*` with prepared statements**](http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php), or [**PDO**](http://php.net/pdo) with [**prepared statements**](http://php.net/pdo.prepared-statements). That is what you should use. – Funk Forty Niner Jul 24 '14 at 16:34
  • Did you not try `mysqli_query($con,"SET NAMES 'utf8' COLLATE 'utf8_general_ci'");` instead of `mysql_query("SET NAMES 'utf8' COLLATE 'utf8_general_ci'",$con);`? – Funk Forty Niner Jul 24 '14 at 16:41
  • 1
    Add error reporting to the top of your file(s) after your opening ` – Funk Forty Niner Jul 24 '14 at 16:53
  • 1
    Also, try placing `$con->set_charset("utf8");` right before your query. – Funk Forty Niner Jul 24 '14 at 16:55
  • @Fred-ii- Success...The warning has been removed and the value is now properly entering into the mysql db..Thanks for your help... – njnjnj Jul 24 '14 at 17:41
  • @TeeJay Actually, I would prefer if I did, for future readers who may happen to fall upon your question. – Funk Forty Niner Jul 24 '14 at 17:49
  • While trying to display the same enterd data, There is also this problem of unknown characters... – njnjnj Jul 24 '14 at 17:50
  • Also, make sure everything in your MySQL database uses UTF-8. See this: http://cameronyule.com/2008/07/configuring-mysql-to-use-utf-8/ – Mihai Nita Jul 24 '14 at 22:02

2 Answers2

2

I've decided to make my comment as an answer, for possible future readers and to close the question.

Try placing $con->set_charset("utf8"); right before your query.

That is one method that has worked for many here on SO, where a similar problem was faced.

Also, add error reporting to the top of your file(s) after your opening <?php tag:

error_reporting(E_ALL);
ini_set('display_errors', 1);

Sidenote: You are presently mixing MySQL APIs using mysql_query().

It does not mix with mysqli_* functions inside the same code. It would need to be mysqli_query()

Edit:

"Success.. Adding <meta charset="utf-8"> solved my problem"

as per comments below.

Funk Forty Niner
  • 73,764
  • 15
  • 63
  • 131
  • set_charset("utf8"); $cresult = mysqli_query($con,"SELECT unicodeText FROM doc_test"); while ($crow = mysqli_fetch_array($cresult,MYSQL_ASSOC)) { echo ( $crow['unicodeText']); } mysqli_close($con); ?> – njnjnj Jul 24 '14 at 17:53
  • This gives the output like this: പി.വി പോളàµâ€à´Žà´‚.à´±àµà´±à´¿. ജോസഫàµâ€Œà´Ÿàµ‹à´£à´¿à´Ÿàµ‹à´£à´¿ ജേകàµà´•à´¬àµà´¬àµ – njnjnj Jul 24 '14 at 17:53
  • Is your file saved as UTF-8 and without BOM (byte order mark)? @TeeJay – Funk Forty Niner Jul 24 '14 at 17:58
  • This is my php file to read the entered utf8 values from mysql db and display it on browser... But still when i run this code, unknown characters are displaying on my browser instead of malayalam characters... Please suggest me the errors in it... – njnjnj Jul 24 '14 at 17:59
  • you mean the php file to view the data from mysql db?? – njnjnj Jul 24 '14 at 17:59
  • 1
    When you save a file, there are a few options to save them as. I use Notepad++ and not the Windows plain Notepad to save file with. The extra options available are ANSI, UTF-8 (with, or without BOM). @TeeJay this could make a big difference. – Funk Forty Niner Jul 24 '14 at 18:02
  • I'm also using notepad++ for editing... But I didn't find any extra options in it while saving a file... – njnjnj Jul 24 '14 at 18:05
  • 1
    @TeeJay Under the "Encoding" dropdown menu option, you will see "Encode in..." and "Convert to...", choose "Convert to UTF-8", then either with the BOM, or without the BOM. Then, just do save. – Funk Forty Niner Jul 24 '14 at 18:06
  • 1
    @TeeJay Also have a look at this page on SO http://stackoverflow.com/questions/279170/utf-8-all-the-way-through there are other possible answers in there you can look into. One of which being to add `` inside the `` of your document. – Funk Forty Niner Jul 24 '14 at 18:09
  • 1
    Did you also add `mysqli_query($con,"SET NAMES 'utf8' COLLATE 'utf8_general_ci'");` as I suggested too? @TeeJay and [`my comment`](http://stackoverflow.com/questions/24939185/malayalam-font-characters-saving-into-the-mysql-database-as-unknown-characters/24940647#comment38759584_24940647) above it. – Funk Forty Niner Jul 24 '14 at 18:11
  • @TeeJay That is great news! Sometimes, the simplest of things solve big problems :-) Very glad to hear it, *cheers* You're very much welcome, always glad to help out. – Funk Forty Niner Jul 24 '14 at 18:14
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/57941/discussion-between-tee-jay-and-fred-ii). – njnjnj Jul 24 '14 at 18:23
1

You can get this in three simple steps.

  1. simply set column Collation to "utf8_general_ci" in your database

  2. header('Content-type: text/html; charset=UTF-8'); in your file.

and

  1. $mysqli->set_charset("utf8"); this after mysql connect query.

thats all.

Rajilesh Panoli
  • 512
  • 6
  • 13