1、php中将数据库中的数据内容导出到csv文件中：
<?php
// define database parameter
$dbTablename = "customer";
/*链接数据库*/
$Connect = @mysql_connect($HOSTNAME, $DBUSER, $DBPASSWORD) or die("Couldn't connect.");
$Db = @mysql_select_db($DBNAME, $Connect) or die("Couldn't select database.");
// set header infomation
$file_type = "vnd.ms-excel";
/*设置导出文件格式*/
$file_ending = "csv";
header("Content-Type: application/$file_type");
header("Content-Disposition: p_w_upload; filename=$dbTablename.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");
// export data to excel
date_default_timezone_set('Asia/Shanghai');
$now_date = date('Y-m-d H:i:s');
$title = "客户名称 , 客户性别(m或者w) , 客户手机(不能有重复) , 客户生日 , 累计积分 , 客户地址 , 客户备注 ,";
//$title = "客户信息备份";
echo("$title\n");
$sql = 'set names gbk;';
@mysql_query($sql,$Connect);
$sql='select c.cName,c.cSex,c.cMobile,c.cBirDay ,i.allIntegral ,cAddress,cComment from customer c,integral i where c.cId=i.cId';
$ALT_Db = @mysql_select_db($DBNAME, $Connect) or die("Couldn't select database");
$result = @mysql_query($sql,$Connect) or die(mysql_error());
$sep = ","; //","取代“\t”表示跳转到下一列
/*读取数据库的列名称信息*/
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . ",";
}
print("\n");
$i = 0;
/*读取数据库中的具体数据信息*/
while($row = mysql_fetch_row($result)) {
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++) {
if(!isset($row[$j]))
$schema_insert .= "NULL" . $sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]" . $sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$"," ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
$i++;
}
return (true);
?>
2、php中将csv中的文件导入到数据库中
1.选择需要导入数据的文件
<tr align="center">
<td colspan="2">选择导入文件：
<label>
<input type="file" name="file" id="file" />
<input type="submit" name="cusImport" value="导入" />
<span id="dataManager">
(*导入的数据信息格式以"客户信息导出"的文件格式为准)
</span>
</label></td>
</tr>
2.接收文件信息并遍历导入数据库
<?php
$fileName=$_FILES["file"]["name"];
if ($_FILES["file"]["error"] > 0)
{
echo "Return Code: " . $_FILES["file"]["error"] . "<br />";
}
else
{
if (file_exists("upload/".$fileName))
{
echo ("<script>alert('该文件名已经存在，请更改导入文件的文件名！')</script>");
echo "<script>window.location.href='dataManager.php'</script>";
exit("");
}
else
{
move_uploaded_file($_FILES["file"]["tmp_name"],
"upload/" . $fileName);
}
}
?>
<?
/**
* UTF8编码转GBK，此函数用于从Excel中读取数据后，需将数据进行转化，以解决中文乱码问题
*
*/
$con = mysql_connect($HOSTNAME,$DBUSER,$DBPASSWORD);
mysql_query("SET NAMES 'gbk'");
function HKGBKToUTF8($strparam){
return iconv('GBK','GBK',$strparam);
}
//解析并读取文件
$filePath="upload/".$fileName;
//$filePath="upload/customer.csv";
/*设置时间时区*/
date_default_timezone_set('Asia/Shanghai');
$batchno='batchno'.date('YmdHis',time());
if(file_exists('upload/customer.csv'))
{
$fp=fopen($filePath,"r");
$j=0;
$uploadData=array();
$item=array();
while (!feof ($fp)){
$buffer = fgets($fp, 4096); //fgets() 函数用于从文件中逐行读取文件,,读取的字节数。默认是 1024 字节。
if (($buffer!=null)||(strlen($buffer)!=0)){
$data=explode(",",$buffer); //用“空格”对操作的一行（$buffer)进行分割，返回由字符串组成的数组。
//此处一定要检测读入每行数组的长度后再赋值
$item['cName']=trim(count($data)>0 && isset($data[0]) && strlen($data[0])<256?HKGBKToUTF8($data[0]):'');
$item['cSex']=trim(count($data)>0 && isset($data[1]) && strlen($data[1])<256?HKGBKToUTF8($data[1]):'');
$item['cMobile']=trim(count($data)>0 && isset($data[2]) && strlen($data[2])<256?HKGBKToUTF8($data[2]):'');
$item['cBirDay']=trim(count($data)>0 && isset($data[3]) && strlen($data[3])<256?HKGBKToUTF8($data[3]):'');
$item['allIntegral']=trim(count($data)>0 && isset($data[4]) && strlen($data[4])<256?HKGBKToUTF8($data[4]):'');
$item['cAddress']=trim(count($data)>0 && isset($data[5]) && strlen($data[5])<256?HKGBKToUTF8($data[5]):'');
$item['cComment']=trim(count($data)>0 && isset($data[6]) && strlen($data[6])<256?HKGBKToUTF8($data[6]):'');
$uploadData[$j]= $item;//将从excel表中读取数据封装而成的数组赋值给uploadData数组
$j++;
}
}
if($j>1)
{
$flag=true;
try{
for($i=2;$i<$j;$i++) //i表示从excel的第i行开始读取数据
{
$cName=$uploadData[$i]['cName'];
$cSex=$uploadData[$i]['cSex'];
$cMobile=$uploadData[$i]['cMobile'];
$cBirDay=$uploadData[$i]['cBirDay'];
$allIntegral=$uploadData[$i]['allIntegral'];
$cAddress=$uploadData[$i]['cAddress'];
$cComment=$uploadData[$i]['cComment'];
$insertCustomer="INSERT INTO customer (cName,cSex,cMobile,cBirDay,cAddress,cComment) values ('$cName','$cSex','$cMobile','$cBirDay','$cAddress','$cComment')";
if (!$con){
die('Could not connect: ' . mysql_error());
}
mysql_select_db($DBNAME, $con);
/*插入从excel中读取的一条客户记录*/
mysql_query($insertCustomer,$con);
/*维护客户表和积分表的关联关系*/
$selCusId="SELECT cId FROM customer WHERE cName='$cName' and cMobile='$cMobile'";
$result = mysql_query($selCusId);
/*查询得到刚插入用户记录的ID*/
$cId;
while($row = mysql_fetch_array($result))
{
$cId=$row['cId'];
echo "<br />";
}
//date_default_timezone_set('PRC');
$curDate=date('Y-m-d H:i:s');
/*维护客户表和积分表的关联关系*/
$insIntegral="INSERT INTO integral (cId,allIntegral,avaIntegral,chaIntegral,cDate,cState) values ($cId,$allIntegral,$allIntegral,$allIntegral,'$curDate','increase')";
/*维护客户表和积分变更表的关联关系*/
$insIntInfo="INSERT INTO integral_info (cId,cMobile,iAllIntegral,iAvaIntegral,iChaIntegral,iState,iDate) VALUES ($cId,$cMobile,$allIntegral,$allIntegral,$allIntegral,'increase','$curDate');";
/*维护客户表和积分变更表的关联关系*/
mysql_query($insIntegral);
mysql_query($insIntInfo);
}
}catch(Exception $e){
$flag=false;
echo "<script type=\"text/javascript\">";
echo "alert(\"导入失败，请检查表格数据是否符合要求！\")";
echo "</script>";
echo "<script>window.location.href='dataManager.php'</script>";
}
if($flag && $i>=2){
$i=$i-2;
echo "<script type=\"text/javascript\">";
echo "alert(\"成功导入". $i ."笔数据！\")";
echo "</script>";
echo "<script>window.location.href='dataManager.php'</script>";
}
}else{
echo "<script type=\"text/javascript\">";
echo "alert(\"表格中没有数据，请填写数据！\")";
echo "</script>";
echo "<script>window.location.href='dataManager.php'</script>";
}
}
?>