【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;
}