Issue with Php And utf8 inserting to mysql

Question

I have a problem with php & mysql, insert to database using utf-8. first file: addsite:

<?php
include 'header.php';
if(isset($data)) {
foreach($_POST as $key => $value) {
$posts[$key] = filter($value);
}
if(isset($posts['type'])){
if($posts['url'] == "http://" || $posts['url'] == ""){
$error = "Add your page link!";
}else if($posts['title'] == ""){
$error = "Add your page title!";
}else if(!preg_match("/\bhttp\b/i", $posts['url'])){
$error = "URL must contain http://";
}else if(!preg_match('|^http(s)?://[a-z0-9-]+(.[a-z0-9-]+)*(:[0-9]+)?(/.*)?$|i',       $posts['url'])){
$error = "Please do not use special characters in the url.<";
}else{
    include "plugins/" . $posts['type'] . "/addsite.php";
}
}
?>
<div class="contentbox">
<font size="2">
<li>Pick the type of exchange you are promoting from the dropdown menu.</li>
<li>Set the amount of coins you wish to give per user complete(CPC).</li>
<li>The higher the amount of coins the higher the Links position.</li>
</div>
<div class="contentbox">
<div class="head">Add Site</div>
<div class="contentinside">
    <?php if(isset($error)) { ?>
    <div class="error">ERROR: <?php echo $error; ?></div>
    <?php }
    if(isset($success)) { ?>
    <div class="success">SUCCESS: <?php echo $success; ?></div>
    <?php }
    if(isset($warning)) { ?>
    <div class="warning">WARNING: <?php echo $warning; ?></div>
    <?php } ?>

    <form class="contentform" method="post">
        Type<br/>
        <select name="type"><?php $select = hook_filter('add_site_select', ""); echo   $select; ?></select><br/><br/>
        Link<br/>
        <input name="url" type="text" value="<?php if(isset($posts["url"])) { echo $posts["url"]; } ?>"/><br/><br/>
        Title<br/>
        <input name="title" type="text" value="<?php if(isset($posts["title"])) { echo $posts["title"]; } ?>"/><br/><br/>
        Cost Per Click<br/>
        <?php if($data->premium > 0) { ?>
        <select name="cpc"><?php for($x = 2; $x <= $site->premcpc; $x++) { if(isset($posts["cpc"]) && $posts["cpc"] == $x) { echo "<option selected>$x</option>"; } else { echo "<option>$x</option>"; } } ?></select><br/><br/>
        <?php }else{ ?>
        <select name="cpc"><?php for($x = 2; $x <= $site->cpc; $x++) { if(isset($posts["cpc"]) && $posts["cpc"] == $x) { echo "<option selected>$x</option>"; } else { echo "<option>$x</option>"; } } ?></select><br/><br/>
        <?php } ?>
        <input style="width:40%;" type="Submit"/>
    </form>
</div>
 </div>
<?php
 }
else
 {
echo "Please login to view this page!";
 }
 include 'footer.php';
  ?>

second file , plugin addsite.php

<?php
$num1 = mysql_query("SELECT * FROM `facebook` WHERE `url`='{$posts['url']}'");
$num = mysql_num_rows($num1);
if($num > 0){
$error = "Page already added!";
 }else if(!strstr($posts['url'], 'facebook.com')) {
$error = "Incorrect URL! You must include 'facebook.com'";
}else{
mysql_query($qry);
  mysql_query("INSERT INTO `facebook` (user, url, title, cpc) VALUES('{$data->id}', '{$posts['url']}', '{$posts['title']}', '{$posts['cpc']}') ");
$success = "Page added successfully!";
}
?>

when i write arabic language in the form and submit , it went to database with unkown language like :

 &Oslash;&pound;&Oslash;&sup3;&Ugrave;

database collaction : utf8_general_ci

<?php
error_reporting(E_ALL);
ini_set('display_errors', '0');

$host       =   "localhost"; // your mysql server address
$user       =   "z*******"; // your mysql username
$pass       =   "m********"; // your mysql password
$tablename  =   "z*******"; // your mysql table

session_start();
$data = null;
if(!(@mysql_connect("$host","$user","$pass") && @mysql_select_db("$tablename"))) {
    ?>
    <html>
    MSQL ERROR
    <?
    exit;
}

include_once 'functions.php';
require_once "includes/pluggable.php";
foreach( glob("plugins/*/index.php")  as $plugin) {  
  require_once($plugin);  
}  

hook_action('initialize');

$site = mysql_fetch_object(mysql_query("SELECT * FROM settings"));
?>

after removing

$posts[$key] = filter($value); " from header.php

The output shift from: To: & Oslash; & pound; & Oslash; ³& Ugrave

to : Ù Ù Ù

header file : http://zwdha.com/header.txt

Stop flooding SO with duplicate questions. http://stackoverflow.com/questions/14761684/issue-with-utf8-and-inserting-to-mysql; http://stackoverflow.com/questions/14761684/issue-with-utf8-and-inserting-to-mysql; http://stackoverflow.com/questions/14761012/issue-with-php-mysql-and-insert-into-utf-8; http://stackoverflow.com/questions/14756039/porblem-with-php-script-and-insert-utf8-to-mysql; http://stackoverflow.com/questions/14754259/issue-with-php-mysql-and-insert-into-utf-8 — Mr. Radical, Feb 09 '13 at 00:08
Have you tried setting you charset to UTF-8 `mysql_set_charset('utf8');` inside your php code? — Mr. Radical, Feb 09 '13 at 00:18
start with a basic script just to test if everything works. So first try to insert data into your database with a simple insert query. Then check how it is saved inside your database. Then proceed to then next step use a select to query your database. If this works you can expand to more elaborate scripts like the one posted above. — Mr. Radical, Feb 09 '13 at 00:26

score 0 · Answer 1 · answered Feb 09 '13 at 00:01

0

You might need to add some ini_set statements if your db is full utf8: http://www.polak.ro/php-mysql-utf-8.html

answered Feb 09 '13 at 00:01

Alb Dum

1,121
3
11
26

where i add these code ? in config.php file ? – Amr Ezzat Feb 09 '13 at 00:02

score 0 · Answer 2 · answered Feb 09 '13 at 00:53

This a very basic insert query to see if you can insert data in you database with PHP.

<?php

function inserting_data() {
    $host = "host";
    $user = "username";
    $password = "password";
    $database = "database";
    $charset = "utf8";

    $link = mysqli_connect($host, $user, $password, $database);
    mysql_set_charset($charset, $link);
    IF (!$link) {
        echo('Unable to connect to the database!');
    } ELSE {
        /*
         * this just to test if something gets inserted into your table. 
         * If some of your columns are set to not null you should add them tot this insert query.
         */

        $query = "INSERT INTO facebook (`title`) VALUES('test_title'), ('test_title1')";
        mysqli_query($link, $query);
    }
    mysqli_close($link);
}

?>

This is a very basic select query to retrieve data form your database.

<?php

function selecting_data(){

    $host = "host";
    $user = "username";
    $password = "password";
    $database = "database";
    $charset = "utf8";

    $link = mysqli_connect($host, $user, $password, $database);
    mysql_set_charset($charset, $link);
    IF (!$link) {
        echo('Unable to connect to the database!');
    } ELSE {
        /*
         * this just to test if something gets returned from your table. 
         */

        $query = "SELECT * FROM facebook";
        $result = mysqli_query($link, $query);
        while ($rows = mysqli_fetch_array($result, MYSQLI_BOTH)){
            echo $rows['title']; 
        }
    }
    mysqli_close($link);


}
?>

My advise is to first test if you can get data in your database and retrieve it. By trial and error you will need to solve your question. B.T.W. I have used MYSQLI_ function instead of MYSQL. The mysql_ functions will depreciate soon. Hope this helps.

score 0 · Answer 3 · answered Feb 09 '13 at 01:21

a - you should not use mysql_ functions. use PDO. this error is one of the reasons.

b - mysql_set_charset('utf8');

c - are the columns you are trying insert data using utf8_general_ci ?

d - on my.cf set default-character-set and character-set-server

if this doesn't work read this: http://www.oreillynet.com/onlamp/blog/2006/01/turning_mysql_data_in_latin1_t.html

and this: http://www.phpwact.org/php/i18n/utf-8

if after that you still dont get it right, make sure to listen to people and use PDO.

Issue with Php And utf8 inserting to mysql

3 Answers3