<?php
# 时区
date_default_timezone_set('Asia/Shanghai');
try {//鏈接數據庫
$conn = new PDO('mysql:dbname=bud2009;host=192.168.33.2', 'root', 'mysql50');
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
$conn->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$conn->query("set names utf8");
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
set_time_limit(0);
ob_end_clean();
$handle = fopen("import01.csv", "r"); //讀取csv文件數據
$data = array();
if($handle){
while (!feof($handle )) { //逐行讀取
$buffer=fgetss($handle);
$data[]=explode(",",$buffer); //每行數據包含多列,用“,”分隔
}
fclose($handle);
}
$userCount=0;
foreach($data as $key=>$val){ //循環數據
echo '现在导入第' . $key . '条数据!';
if (empty($val[1]) || empty($val[0]))
continue;
$email=$val[0];
$mobile=$val[1];
$name=$val[2];//站内昵称
$password=$val[3];
$c_password=$val[3];
$birthday=empty($val[4]) ? '1980年1月' : $val[4];
$array = explode("年", $birthday);
$born_month = str_replace("月", "", $array[1]);
$born_year = $array[0];
$true_name=$val[5];
$gender=$val[6]=='男'?0:1;
$areaName = $val[8];
$id_card=$val[7];
$userData=array(
'email'=>$email,
'mobile'=>$mobile,
'name'=>$name,
'password'=>$password,
'created_at'=>mktime()
);
//查找数据表是否存在相同email或者相同mobile
$result = $conn->prepare("select count(id) from users where email=? or mobile=?");
// $result = $conn->prepare("select id from users where email='{$email}' or mobile='{$mobile}'");
$count = 0;
if ($result->execute(array($email, $mobile))) {
$count = $result->fetchColumn(0);
}
if($count > 0){//如果有,就更改数据
$userData['email'] = $userData['email'].'_'.mt_rand(1,100);
$userData['mobile'] = '13800' . mt_rand(100000,200000);
}
$userSql = $conn->prepare("insert into users
(email,mobile,password,created_at,name,)
values(
'{$userData['email']}',
'{$userData['mobile']}',
'{$userData['password']}',
'{$userData['created_at']}',
'{$userData['name']}',)");//插入user表
$userResult = $userSql->execute();
echo '插入' . $key . '条user记录成功!<br />';
if($userResult){//插入成功
$userCount+=1;
}
}
echo 'users已成功插入数据'.$userCount.'条! ;
?>
Execl数据导入数据库
最新推荐文章于 2024-09-24 09:50:23 发布
该PHP脚本演示了如何将CSV文件中的数据导入到MySQL数据库。它首先连接到数据库,然后读取CSV文件,检查每一行数据,避免空值,并处理已有邮箱或手机号的冲突。最后,它将数据插入到users表中。
摘要由CSDN通过智能技术生成