时间戳

首页 > 解决方案 > 当从 excel 文件中插入日期到 MySQL 数据库中时，我遇到了日期更改问题

php - 当从 excel 文件中插入日期到 MySQL 数据库中时，我遇到了日期更改问题

问题描述

当我尝试将 excel 文件中的日期插入到我的数据库中时，日期更改如下：在 excel 中，日期是 29-08-2020 到我的数据库中，它变成这样 03-07-2044 我该怎么做才能解决这个问题。这是我使用的 PHP 代码

    $object = PHPExcel_IOFactory::load($_FILES["excel_file"]["tmp_name"]);  
    foreach($object->getWorksheetIterator() as $worksheet) {   
        $highestRow = $worksheet->getHighestRow();  
        for($row=2; $row<=$highestRow; $row++)  {  
            $id = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(0, $row)->getValue());  
            $name = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(1, $row)->getValue());  
            $mufadala_date = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(2, $row)->getValue());
            $mark = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(3, $row)->getValue());  
            $secondary_school = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(4, $row)->getValue());  
            $secondary_type = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(5, $row)->getValue());
            $secondary_date = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(6, $row)->getValue());  
            $specialization = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(7, $row)->getValue());  
            $is_syrian = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(8, $row)->getValue());  
            $with_mufadala = mysqli_real_escape_string($connect, $worksheet->getCellByColumnAndRow(9, $row)->getValue());
            $DbDate = date("YYYY-mm-dd", strtotime($mufadala_date);
            mysqli_set_charset($connect,"utf8"); 
            $query = "INSERT INTO tbl_accepted  
                                (Student_Name, Mufadala_Date, Mark, 
                                Secondary_School, Secondary_Type , 
                                Secondary_Date, Specialization , 
                                IsSyrian , WithMufadala)   
                        VALUES ('".$name."', '".$DbDate."', '".$mark."', 
                                '".$secondary_school."', 
                                '".$secondary_type."', '".$secondary_date."', 
                                '".$specialization."', '".$is_syrian."', 
                                '".$with_mufadala."')  ";  
            mysqli_query($connect, $query);   
        }  
    }  
    $output = '';  
    $output .= "تم تحميل الملف بنجاح ";  
    echo $output;  
}

标签： phpmysqlexcel

解决方案

假设在excel文件中写入的日期以$datedd-mm-yyyy格式存储。你必须创建你的 php 日期时间对象：

$dateObj = DateTime::createFromFormat('d-m-Y', $date);
$dataToStoreInDB = $dateObj -> format('Y-m-d');

现在您可以对 $dateTOStoreInDB var 进行插入查询。

推荐阅读