php - 将 CSV 插入 MySQL

Question

我想将 CSV 插入 MySQL。如果 MySQL 表中存在 sha1 列，如何才能只更新我的表？如果 sha1 列存在，则将其插入。

我有这个代码来插入 csv:

while(($csvdata = fgetcsv($handle,10000,","))!== FALSE){
    if($i>0) {
        $sha1 = $csvdata[0];
        $vsdt = $csvdata[1];
        $trendx  = $csvdata[2];
        $notes  = $csvdata[3];

        $sql = "INSERT INTO jeremy_table_trend (date_sourced,sha1,vsdt,trendx,notes) VALUES ('$date','$sha1','$vsdt','$trendx','$notes')";
        $query = mysqli_query($con , $sql);

        $c = $c+1;
        error_reporting(E_ALL ^ E_NOTICE);
    }
    $i++;
    error_reporting(E_ALL ^ E_NOTICE);
}

Answer 1

检查一下，这可能对您有帮助

<?php
while(($csvdata = fgetcsv($handle,10000,","))!== FALSE){
    if($i>0) {
        $sha1 = $csvdata[0];
        $vsdt = $csvdata[1];
        $trendx  = $csvdata[2];
        $notes  = $csvdata[3];

        // Get record where sha1 
        $check_sha = "SELECT sha1 FROM jeremy_table_trend WHERE sha1='".$sha1."'";
        $check_shaquery = mysqli_query($con , $check_sha);
        if($check_shaquery){
            $sha_count = mysqli_num_rows($check_shaquery);
        }

        // Check if sha1 already in database
        if(isset($sha_count) && $sha_count>0){
            $sql = "UPDATE `jeremy_table_trend` SET `date_sourced`='".$date."',`sha1`='".$sha1."',`vsdt`='".$vsdt."',`trendx`='".$trendx."',`notes`='".$notes."' WHERE sha1='".$sha1."'";
            $query = mysqli_query($con , $sql);
        }else{
            $sql = "INSERT INTO jeremy_table_trend (date_sourced,sha1,vsdt,trendx,notes) VALUES ('$date','$sha1','$vsdt','$trendx','$notes')";
            $query = mysqli_query($con , $sql);
        }

        $c = $c+1;
        error_reporting(E_ALL ^ E_NOTICE);
    }
    $i++;
    error_reporting(E_ALL ^ E_NOTICE);
}
?>