本文介绍了在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">← Back to main Website</a>
</div>
</div>
<h1> CSV Should be in Below Format:</h1><b>Full Name: Employee Number: Department: 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">← 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="">© 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">← Back to main Website</a>
</div>
</div>
<h1> CSV Should be in Below Format:</h1><b>Full Name: Employee Number: Department: 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">← 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="">© 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">← Back to main Website</a>
</div>
</div>
<h1> CSV Should be in Below Format:</h1><b>Full Name: Employee Number: Department: 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">← 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="">© ALL RIGHTS RESERVED BY DIBBA MUNICIPALITY FUJAIRAH</a></b></div>
</body>
</html>
The data I am testing is as follows محمود عبدالعزيز
1 abc defWhere 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.
EDITCSV 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.
EDIT2I 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">← Back to main Website</a>
</div>
</div>
<h1> CSV Should be in Below Format:</h1><b>Full Name: Employee Number: Department: 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">← 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="">© ALL RIGHTS RESERVED BY DIBBA MUNICIPALITY FUJAIRAH</a></b></div>
</body>
</html>
这篇关于在PHP(mysql)中从CSV导入会显示类似"???"的字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！