php - 将日期从 csv 更新到 mysql 返回 0000-00-00

Question

我有一个包含列的 csv 文件:date_sourced、sha1、vsdt、trendx、notes 这些是 csv 中的一些示例:

DATE SOURCED, SHA1, VSDT, TRENDX, NOTES, MTF
11/9/2018,fffe455520ddc68e4f7a56a053efc78a208eb384,MS Office 1-0,Not Supported,test,

当我将我的 csv 插入到 mysql 时，它是正确的，但为什么我在 mysql 中的日期在我使用此代码更新后返回 0000-00-00:

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

            // Get record where sha1 
            $check_sha = "SELECT sha1 FROM jeremy_table_test 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_test` 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_test (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

因为你的表格列类型是日期尝试strtotime改变日期格式

if(isset($sha_count) && $sha_count>0){
                    $sql = "UPDATE `jeremy_table_test` SET `date_sourced`='".date('Y-m-d',strtotime($date))."',`sha1`='".$sha1."',`vsdt`='".$vsdt."',`trendx`='".$trendx."',`notes`='".$notes."' WHERE sha1='".$sha1."'";
                    $query = mysqli_query($con , $sql);
                }else{
                    $sql = "INSERT INTO jeremy_table_test (date_sourced,sha1,vsdt,trendx,notes) VALUES ('".date('Y-m-d',strtotime($date))."','$sha1','$vsdt','$trendx','$notes')";
                    $query = mysqli_query($con , $sql);
            }