【PHP】将服务器端存储的TXT、CSV类型的文本数据写入数据库

需求：将上传在服务器端的文本数据导入数据库

案例代码：

$fileName = "upload/access.log";
 txt_to_db ($fileName, $conn );
 $str = "/apk/dshq.apk"; $num1 =count_download($conn,$str); print "下载量：".$num1; /** * 将txt类型文本数据插入数据库 * @param unknown $fileName * @param unknown $conn */ function txt_to_db($fileName, $conn) { $file = fopen ( $fileName, "r" ); $row = 1; $splitChar = ','; // 判断是否到达文件末尾 while ( ! feof ( $file ) ) { $data = preg_replace ( '/(\s*)(\n+)(\s*)/i', '', fgets ( $file ) ); $row ++; $arr = explode ( " ", $data ); var_dump ( $arr ); try { $ip = $arr [0]; if($ip!=''){ $query = "SELECT * FROM download_url WHERE ip ='" . $ip."'"; $stmt = $conn->prepare ( $query ); $stmt->execute (); $rowNum = $stmt->fetch (); if ($rowNum [0] > 0) { $sql = "UPDATE download_url SET count=count+1 WHERE ip ='" . $ip."'"; $stmt = $conn->prepare ( $sql ); $stmt->execute (); echo "UPDATE " . $ip . " Succeed!" . "<br/>"; echo "<hr/>"; } else { $sql = "INSERT INTO download_url(ip,time,road) VALUES(?,?,?)"; $stmt = $conn->prepare ( $sql ); $stmt->bindParam ( 1, $ip ); $stmt->bindParam ( 2, $time ); $stmt->bindParam ( 3, $road ); $ip = $arr [0]; $time = $arr [3]; $road = $arr [6]; $stmt->execute (); echo "INSERT " . $ip . " Succeed!" . "<br/>"; echo "<hr/>"; } } } catch ( PDOException $e ) { } } } /** *将CSV类型文本数据插入数据库 * @param unknown $fileName * @param unknown $conn */ function csv_to_db($fileName, $conn) { $row = 1; $handle = fopen ( $fileName, "r" ); // fgetcsv() 解析读入的行并找出 CSV格式的字段然后返回一个包含这些字段的数组。 while ( $data = fgetcsv ( $handle, 1000, "," ) ) { $num = count ( $data ); $row ++; var_dump($data); if ($row > 1) { try { $mobile = iconv ( "gbk", "utf-8//IGNORE", $data [0] ); $query = "SELECT id FROM brands "; $stmt = $conn->prepare ( $query ); $stmt->execute (); $rowNum = $stmt->fetch (); if ($rowNum [0] > 0) { $sql = "UPDATE salary SET name=?,position=?,base_salary=?,work_day=? WHERE phone=" . $mobile; $stmt = $conn->prepare ( $sql ); $stmt->bindParam ( 1, $name ); $stmt->bindParam ( 2, $position ); $stmt->bindParam ( 3, $salary ); $stmt->bindParam ( 4, $day ); $name = iconv ( "gbk", "utf-8//IGNORE", $data [0] ); $position = iconv ( "gbk", "utf-8//IGNORE", $data [2] ); $salary = iconv ( "gbk", "utf-8//IGNORE", $data [3] ); $day = iconv ( "gbk", "utf-8//IGNORE", $data [4] ); echo $name . "--" . $mobile . "--" . $position . "--" . $salary . "--" . $day . "<br/>"; $stmt->execute (); echo "UPDATE " . $name . " Succeed!" . "<br/>"; echo "<hr/>"; } else { $sql = "INSERT INTO salary(name,phone,position,base_salary,work_day) VALUES(?,?,?,?,?)"; $stmt = $conn->prepare ( $sql ); $stmt->bindParam ( 1, $name ); $stmt->bindParam ( 2, $phone ); $stmt->bindParam ( 3, $position ); $stmt->bindParam ( 4, $salary ); $stmt->bindParam ( 5, $day ); $name = iconv ( "gbk", "utf-8//IGNORE", $data [0] ); $phone = $mobile; $position = iconv ( "gbk", "utf-8//IGNORE", $data [2] ); $salary = iconv ( "gbk", "utf-8//IGNORE", $data [3] ); $day = iconv ( "gbk", "utf-8//IGNORE", $data [4] ); echo $name . "--" . $phone . "--" . $position . "--" . $salary . "--" . $day . "<br/>"; $stmt->execute (); echo "INSERT " . $name . " Succeed!" . "<br/>"; echo "<hr/>"; } } catch ( PDOException $e ) { } } } fclose ( $handle ); } /** * 统计下载数 * 根据条件统计 * */ function count_download($conn,$str){ $sql = "SELECT COUNT(ip) FROM download_url WHERE road='".$str."'"; $stmt = $conn->prepare ( $sql ); $stmt->execute (); $rowNum = $stmt->fetch (); return $rowNum[0]; } /* * pdo链接数据库 */ function getPDOConn() { $host = "192.168.0.6"; // 本地IP $hosttest = "localhost"; $usesrname = "root"; $password = ""; $dbname = "demo"; try { $conn = new PDO ( "mysql:host=$hosttest;dbname=$dbname", $usesrname, $password ); // 设置编码，防止中文乱码 $conn->exec ( "SET names utf8" ); // 防止sql注入攻击 $conn->setAttribute ( \PDO::ATTR_EMULATE_PREPARES, false ); print "connection succeed!<br/>"; } catch ( PDOException $e ) { print "connection error!<br/>" . $e->getMessage (); } return $conn; }

 

转载于:https://www.cnblogs.com/ziwuyeyue/p/6170283.html