通过PHP将带有日期的CSV导入到MySQL [英] import csv with date to mysql through php

问题描述

我正在尝试使用php将csv文件上传到我的mysql表中.

I'm trying to upload a csv file to my mysql table with php.

我听说过 inline 命令，但问题是csv日期格式为 dd/mm/yyyy ，而mysql格式为 yyyy-mm-dd .

I heard about the inline command but the problem is that the csv date format is dd/mm/yyyy and the mysql format is yyyy-mm-dd.

我尝试了几乎每个 Date 转换函数，但没有成功.总是在Mysql表格上看到0000-00-00.

I tried almost every Date convert function but it doesn't worked out. always see 0000-00-00 on the Mysql Table.

如何正确转换?

这是我最后尝试的方法:

This is the last thing I tried:

( $ date [0] 是csv中的日期单元格- 2016/05/26 ，而mysql中的第二列是日期)

($date[0] is the date cell in the csv. - 26/05/2016, and the second column in the mysql is the date)

while ($data = fgetcsv($handle, 1000, ",")) 
    {
        $date = strtotime($data[0]); 
        $newDate = date("d-m-Y", strtotime($data[0]));
        $trydate = date("Y-m-d", strtotime($newDate));
        echo $newDate . "<br>". $date. "<br>" . $data[0] . "<br>". $trydate. "<br>";
        $import="INSERT INTO `testresult` (cityid, date, testnum,result) VALUES('$data[1]','$newDate','$data[3]','$data[2]')";

        mysqli_query($con,$import) or die(mysql_error());
    }

输出为:

01-01-1970
(blank)
26/05/2015
1970-01-01

推荐答案

strtotime 等待输入您需要分割字符串，并从中创建一个 Y-m-d ，也可以使用 DateTime 对象.

You need to split your string, and create a Y-m-d fromat from it, or you can use the DateTime object.

$dateString = '11/05/2016';
$Date = DateTime::createFromFormat('d/m/Y', $dateString, new DateTimeZone(('UTC')));
echo $Date->format('Y-m-d');

输出

2016-05-11 

使用 strtotime

$string = '26/05/2016';
$trydate = date("Y-m-d", strtotime(substr($string, -4) . "-" . substr($string, 3, 2) . "-" . substr($string, 0, 2)));
echo $trydate;

结果相同.

这篇关于通过PHP将带有日期的CSV导入到MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！