在PHP(mysql)中从CSV导入会显示类似"???"的字符 [英] Import from CSV in PHP (mysql) shows characterses like '???'

查看:101
本文介绍了在PHP(mysql)中从CSV导入会显示类似"???"的字符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

当我从CSV导入时,它向我显示诸如???

之类的字符

我的数据库和表被铅密地设置为utf8 unici ci和阿拉伯语,并且当我选择*时其他数据正确显示

但是当我从CSV导入并使用以下代码时.返回成功上传的我,但数据显示为???以员工姓名

请在下面找到代码

从CSV导入...

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
    header('Content-Type: text/html; charset=utf-8');
    mb_internal_encoding('UTF-8');
    mb_http_output('UTF-8');
?>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<head>
    <style type="text/css">
    body
    {
        margin: 0;
        padding: 0;
        background-color:#D6F5F5;
        text-align:center;
    }
    .top-bar
        {
            width: 100%;
            height: auto;
            text-align: center;
            background-color:#FFF;
            border-bottom: 1px solid #000;
            margin-bottom: 20px;
        }
    .inside-top-bar
        {
            margin-top: 5px;
            margin-bottom: 5px;
        }
    .link
        {
            font-size: 18px;
            text-decoration: none;
            background-color: #000;
            color: #FFF;
            padding: 5px;
        }
    .link:hover
        {
            background-color: #9688B2;
        }
    </style>

    <script>
      (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
      (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
      m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
      })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

      ga('create', 'UA-60962033-1', 'auto');
      ga('send', 'pageview');

    </script>
</head>

<body>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <div class="top-bar">
        <div class="inside-top-bar">
            <a href="" width="500px"></a>
            <br><br>
            <a href="../insert.php" class="link">&larr; Back to main Website</a>
        </div>

    </div>
    <h1> CSV Should be in Below Format:</h1><b>Full Name:&nbsp;Employee Number:&nbsp;Department:&nbsp;Email:</b>
    <div style="border:1px dashed #333333; width:300px; margin:0 auto; padding:10px;">

    <form name="import" method="post" enctype="multipart/form-data">
        <input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
    </form>
<?php
    include ("connect.php");

    if(isset($_POST["submit"]))
    {   
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        $c = 0;
        setlocale(LC_ALL, 'ar_AE.utf8');
        while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
        {
            $name = $filesop[0];
            $emp_number = $filesop[1];
            $department = $filesop[2];
            $email = $filesop[3];
            //$name = $filesop[0];
            //$email = $filesop[1];
            mysqli_query($link,"SET character_set_client=utf8");
            $sql = $link->query("INSERT INTO employees (first_name, emp_number,department,email) VALUES ('$name','$emp_number','$department','$email')");
            $c = $c + 1;
        }

            if($sql){
                echo "You database has imported successfully. You have inserted ". $c ." Records";
                echo '<td><a href="../view.php">&larr; View Inserted Records</a></td>';
            }else{
                echo "Sorry! There is some problem.";
            }

    }
?>

    </div>
    <hr style="margin-top:300px;" />    

    <div align="center" style="font-size:18px;"><b><a href="">&copy; ALL RIGHTS RESERVED BY DIBBA MUNICIPALITY FUJAIRAH</a></b></div>

</body>
</html>

我正在测试的数据如下 محمودعبدالعزيز

1 abc def 其中阿拉伯语为全名/名字,1为员工编号,abc为部门,def为电子邮件

解决方案

能否请您检查您列的排序规则是否也是UTF8 Unicode?

您可以运行mysql> show full columns from employees;

并检查输出以确保即使列的排序规则也能够接受UTF8.

编辑 除非设置了UTF-8字节顺序标记(BOM),否则CSV无法存储UTF-8数据.

我不知道您保存的CSV是否保留UTF-8字符.也许您应该尝试在noteapd中打开CSV并查看是否正确获取了字符.

EDIT2 我刚刚修改了代码,以使其能够正确读取CSV,并且表单具有新的accept属性.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
    ini_set('display_errors', 'On');
    error_reporting(-1);
    header('Content-Type: text/html; charset=utf-8');
    mb_internal_encoding('UTF-8');
    mb_http_output('UTF-8');
?>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<head>
    <style type="text/css">
    body
    {
        margin: 0;
        padding: 0;
        background-color:#D6F5F5;
        text-align:center;
    }
    .top-bar
        {
            width: 100%;
            height: auto;
            text-align: center;
            background-color:#FFF;
            border-bottom: 1px solid #000;
            margin-bottom: 20px;
        }
    .inside-top-bar
        {
            margin-top: 5px;
            margin-bottom: 5px;
        }
    .link
        {
            font-size: 18px;
            text-decoration: none;
            background-color: #000;
            color: #FFF;
            padding: 5px;
        }
    .link:hover
        {
            background-color: #9688B2;
        }
    </style>


</head>

<body>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <div class="top-bar">
        <div class="inside-top-bar">
            <a href="" width="500px"></a>
            <br><br>
            <a href="#" class="link">&larr; Back to main Website</a>
        </div>

    </div>
    <h1> CSV Should be in Below Format:</h1><b>Full Name:&nbsp;Employee Number:&nbsp;Department:&nbsp;Email:</b>
    <div style="border:1px dashed #333333; width:300px; margin:0 auto; padding:10px;">

    <form name="import" method="post" enctype="multipart/form-data" accept-charset="UTF-8">
        <input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
    </form>
<?php
    $link = mysqli_connect('dbhost', 'dbuser', 'dbpass', 'test_arabic');

    if (!$link) {
        echo "Error: Unable to connect to MySQL." . PHP_EOL;
        echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
        echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
        exit;
    }

    if(isset($_POST["submit"]))
    {   
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        $c = 0;
        setlocale(LC_ALL, 'ar_AE.utf8');
        while(($filesop = fgetcsv($handle, 1000, ";")) !== false)
        {

            $name = $emp_number = $department = $email = '';
            $filesop2 = explode(',', $filesop[0]);
            $name = $filesop2['0'];
            $emp_number = $filesop2['1'];
            $department = $filesop2['2'];
            $email = $filesop2['3'];

            mysqli_query($link, "SET NAMES utf8");
            mysqli_query($link, "set characer set utf8");


            $sql = $link->query("INSERT INTO employees (first_name, emp_number,department,email) VALUES ('".$name."','".$emp_number."','".$department."','".$email."')");
            $c = $c + 1;
        }

            if($sql){
                echo "You database has imported successfully. You have inserted ". $c ." Records";
                echo '<td><a href="../view.php">&larr; View Inserted Records</a></td>';
            }else{
                echo "Sorry! There is some problem. <br>".$link->error;
            }

    }
?>

    </div>
    <hr style="margin-top:300px;" />    

    <div align="center" style="font-size:18px;"><b><a href="">&copy; ALL RIGHTS RESERVED BY DIBBA MUNICIPALITY FUJAIRAH</a></b></div>

</body>
</html>

When I am importing from CSV, it is showing me characters like ???

My DB and tables are aleady set to utf8 unicode ci and arabic and other data shows properly when i do select *

But when I import from CSV and use below code. It returns me uploaded successfully but data is shown as ??? in employee name

Please find code below

Import From CSV...

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
    header('Content-Type: text/html; charset=utf-8');
    mb_internal_encoding('UTF-8');
    mb_http_output('UTF-8');
?>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<head>
    <style type="text/css">
    body
    {
        margin: 0;
        padding: 0;
        background-color:#D6F5F5;
        text-align:center;
    }
    .top-bar
        {
            width: 100%;
            height: auto;
            text-align: center;
            background-color:#FFF;
            border-bottom: 1px solid #000;
            margin-bottom: 20px;
        }
    .inside-top-bar
        {
            margin-top: 5px;
            margin-bottom: 5px;
        }
    .link
        {
            font-size: 18px;
            text-decoration: none;
            background-color: #000;
            color: #FFF;
            padding: 5px;
        }
    .link:hover
        {
            background-color: #9688B2;
        }
    </style>

    <script>
      (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
      (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
      m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
      })(window,document,'script','//www.google-analytics.com/analytics.js','ga');

      ga('create', 'UA-60962033-1', 'auto');
      ga('send', 'pageview');

    </script>
</head>

<body>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <div class="top-bar">
        <div class="inside-top-bar">
            <a href="" width="500px"></a>
            <br><br>
            <a href="../insert.php" class="link">&larr; Back to main Website</a>
        </div>

    </div>
    <h1> CSV Should be in Below Format:</h1><b>Full Name:&nbsp;Employee Number:&nbsp;Department:&nbsp;Email:</b>
    <div style="border:1px dashed #333333; width:300px; margin:0 auto; padding:10px;">

    <form name="import" method="post" enctype="multipart/form-data">
        <input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
    </form>
<?php
    include ("connect.php");

    if(isset($_POST["submit"]))
    {   
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        $c = 0;
        setlocale(LC_ALL, 'ar_AE.utf8');
        while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
        {
            $name = $filesop[0];
            $emp_number = $filesop[1];
            $department = $filesop[2];
            $email = $filesop[3];
            //$name = $filesop[0];
            //$email = $filesop[1];
            mysqli_query($link,"SET character_set_client=utf8");
            $sql = $link->query("INSERT INTO employees (first_name, emp_number,department,email) VALUES ('$name','$emp_number','$department','$email')");
            $c = $c + 1;
        }

            if($sql){
                echo "You database has imported successfully. You have inserted ". $c ." Records";
                echo '<td><a href="../view.php">&larr; View Inserted Records</a></td>';
            }else{
                echo "Sorry! There is some problem.";
            }

    }
?>

    </div>
    <hr style="margin-top:300px;" />    

    <div align="center" style="font-size:18px;"><b><a href="">&copy; ALL RIGHTS RESERVED BY DIBBA MUNICIPALITY FUJAIRAH</a></b></div>

</body>
</html>

The data I am testing is as follows محمود عبدالعزيز

1 abc def Where arabic one is the full name/first_name, 1 is employee number, abc is department and def is email

解决方案

Could you please check if your column's collation is also UTF8 unicode?

You can run mysql> show full columns from employees;

and check the output to make sure even the column's collation is able to accept UTF8.

EDIT CSV can't store UTF-8 data unless the UTF-8 Byte Order Mark (BOM) is set.

I don't know if the CSV you are saving is retaining the UTF-8 characters. Maybe you should try opening the CSV in noteapd and see if you get the characters properly.

EDIT2 I just modified the code to have it read the CSV properly and the form has a new accept attribute.

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<?php
    ini_set('display_errors', 'On');
    error_reporting(-1);
    header('Content-Type: text/html; charset=utf-8');
    mb_internal_encoding('UTF-8');
    mb_http_output('UTF-8');
?>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<html>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
<head>
    <style type="text/css">
    body
    {
        margin: 0;
        padding: 0;
        background-color:#D6F5F5;
        text-align:center;
    }
    .top-bar
        {
            width: 100%;
            height: auto;
            text-align: center;
            background-color:#FFF;
            border-bottom: 1px solid #000;
            margin-bottom: 20px;
        }
    .inside-top-bar
        {
            margin-top: 5px;
            margin-bottom: 5px;
        }
    .link
        {
            font-size: 18px;
            text-decoration: none;
            background-color: #000;
            color: #FFF;
            padding: 5px;
        }
    .link:hover
        {
            background-color: #9688B2;
        }
    </style>


</head>

<body>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>
    <div class="top-bar">
        <div class="inside-top-bar">
            <a href="" width="500px"></a>
            <br><br>
            <a href="#" class="link">&larr; Back to main Website</a>
        </div>

    </div>
    <h1> CSV Should be in Below Format:</h1><b>Full Name:&nbsp;Employee Number:&nbsp;Department:&nbsp;Email:</b>
    <div style="border:1px dashed #333333; width:300px; margin:0 auto; padding:10px;">

    <form name="import" method="post" enctype="multipart/form-data" accept-charset="UTF-8">
        <input type="file" name="file" /><br />
        <input type="submit" name="submit" value="Submit" />
    </form>
<?php
    $link = mysqli_connect('dbhost', 'dbuser', 'dbpass', 'test_arabic');

    if (!$link) {
        echo "Error: Unable to connect to MySQL." . PHP_EOL;
        echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
        echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
        exit;
    }

    if(isset($_POST["submit"]))
    {   
        $file = $_FILES['file']['tmp_name'];
        $handle = fopen($file, "r");
        $c = 0;
        setlocale(LC_ALL, 'ar_AE.utf8');
        while(($filesop = fgetcsv($handle, 1000, ";")) !== false)
        {

            $name = $emp_number = $department = $email = '';
            $filesop2 = explode(',', $filesop[0]);
            $name = $filesop2['0'];
            $emp_number = $filesop2['1'];
            $department = $filesop2['2'];
            $email = $filesop2['3'];

            mysqli_query($link, "SET NAMES utf8");
            mysqli_query($link, "set characer set utf8");


            $sql = $link->query("INSERT INTO employees (first_name, emp_number,department,email) VALUES ('".$name."','".$emp_number."','".$department."','".$email."')");
            $c = $c + 1;
        }

            if($sql){
                echo "You database has imported successfully. You have inserted ". $c ." Records";
                echo '<td><a href="../view.php">&larr; View Inserted Records</a></td>';
            }else{
                echo "Sorry! There is some problem. <br>".$link->error;
            }

    }
?>

    </div>
    <hr style="margin-top:300px;" />    

    <div align="center" style="font-size:18px;"><b><a href="">&copy; ALL RIGHTS RESERVED BY DIBBA MUNICIPALITY FUJAIRAH</a></b></div>

</body>
</html>

这篇关于在PHP(mysql)中从CSV导入会显示类似"???"的字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆