从CSV文件将日期导入mySql

Question

My son set up a facility on his hosted site for the family to record data from trips in a MySql file. These are then displayed on a web page on a map (uses google maps but is hosted privately.)

Unfortunately my son was taken in to hospital some weeks ago and is still there, so he suggested may get an answer to a problem if I posted it here.

The mysql data is created by using a php script to import it from csv files produced by loggers. My son unfortunately cannot remember where he obtained the script.

This script works EXCEPT for a date field (which is crucial).

The Script is as follows:

$tot = 0;
$handle = fopen($_FILES["uploaded"]["tmp_name"], "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    for ($c=0; $c < 1; $c++) {

if($data[0] !='Latitude'){ 
                mysql_query("INSERT INTO positions(
                Latitude,
                Longitude,
                DateOccurred,
                Altitude,
                Speed,
                Angle
                )VALUES(
                    '".mysql_real_escape_string($data[0])."',
                    '".mysql_real_escape_string($data[1])."',
                    '".mysql_real_escape_string($data[2])."',
                    '".mysql_real_escape_string($data[16])."',
                    '".mysql_real_escape_string($data[15])."',
                    '".mysql_real_escape_string($data[8])."'

                )")or die(mysql_error());
}
    $tot++;}
}
fclose($handle);    

This imports everything to mysql EXCEPT for the the third item ($data[2]) which is the date and imports as zeros (ie 00:00:00 00:00).

Are there any kind coders here who may be able to tell me how to correct this, or point me elsewhere that may be able to help.

If further info is required, please let me know.

Thanks

EDIT: Here is the sql table structure

|//**ID**//|int(11)|No|
|FK_Users_ID|int(11)|No|
|FK_Trips_ID|int(11)|Yes|NULL
|FK_Icons_ID|int(11)|Yes|NULL
|Latitude|double|No|
|Longitude|double|No|
|Altitude|double|Yes|0
|Speed|double|Yes|0
|Angle|double|Yes|NULL
|DateAdded|timestamp|No|CURRENT_TIMESTAMP
|DateOccurred|timestamp|Yes|0000-00-00 00:00:00
|Comments|varchar(255)|Yes|NULL
|ImageURL|varchar(255)|Yes|NULL
|SignalStrength|int(11)|Yes|NULL
|SignalStrengthMax|int(11)|Yes|NULL
|SignalStrengthMin|int(11)|Yes|NULL
|BatteryStatus|tinyint(4)|Yes|NULL

Here is a sample of the CSV file content:

Latitude,Longitude,DateOccurred,ID,IconName,Comments,TripName,ImageURL,Angle,CellID,SignalStrength,SignalStrengthMin,SignalStrengthMax,BatteryStatus,Source,Speed,Altitude
51.1923274,-3.102753383,20/03/2013 14:17,2352,,,dentist,,287.2,,,,,,0,7.71666666,13
51.19254205,-3.103862483,20/03/2013 14:17,2353,,,dentist,,285.4,,,,,,0,7.665222216,14
51.19268602,-3.105032683,20/03/2013 14:17,2354,,,dentist,,272.1,,,,,,0,9.054222214,11.1

If anything else is needed, let me know.

Thanks

EDIT 2

I tried the solution from Havelock - option 3.

As it was still returning zeros in the DateOccurred field I added another instruction to insert the date from the csv into a 'comments' text field in the mysql database.

Code is as follows

if($data[0] !='Latitude'){ 
           $timestamp = strtotime($data[2]);
           mysql_query("INSERT INTO positions(
           Latitude,
           Longitude,
           DateOccurred,
           Comments,
           Altitude,
           Speed,
           Angle
           )VALUES(
               '".mysql_real_escape_string($data[0])."',
               '".mysql_real_escape_string($data[1])."',
               '".mysql_real_escape_string($data[2])."',
               '".mysql_real_escape_string(date('Y-m-d H:i:s', $timestamp))."',
               '".mysql_real_escape_string($data[16])."',
               '".mysql_real_escape_string($data[15])."',
               '".mysql_real_escape_string($data[8])."'

                )")or die(mysql_error());

The result was that the data inserted into the 'Comments' field in all records read

1969-12-31 18:00:00

whereas the information in the csv was

20/03/2013 14:17

If I omit the lines

$timestamp = strtotime($data[2]);
and 
'".mysql_real_escape_string(date('Y-m-d H:i:s', $timestamp))."',

the comments field contains the correct data from the csv file (as text).

I'm not sure how signifcant this info is - but I thought it might mean something here.

EDIT 3

As the 'DateTime::createFromFormat()' should work with PHP 5.3.22 I revisited the script and entered some code to test it. I added this

$date = DateTime::createFromFormat('d/m/Y H:i', $data[2]);
           echo $date->format('Y-m-d H:i:s');

The echo was to print the results to see what they were. The script now looks like this

if($data[0] !='Latitude'){ 
           $date = DateTime::createFromFormat('d/m/Y H:i', $data[2]);
           echo $date->format('Y-m-d H:i:s');
           mysql_query("INSERT INTO positions(
           Latitude,
           Longitude,
           DateOccurred,
           Altitude,
           Speed,
           Angle
           )VALUES(
               '".mysql_real_escape_string($data[0])."',
               '".mysql_real_escape_string($data[1])."',
               '".mysql_real_escape_string(date('Y-m-d H:i:s'))."',
               '".mysql_real_escape_string($data[16])."',
               '".mysql_real_escape_string($data[15])."',
               '".mysql_real_escape_string($data[8])."'


                )")or die(mysql_error());
}

This resulted in the script printing a date and entering it in the correct format in the mysql field.

HOWEVER - the date that is being printed and stored is current date and time, not the date stored in the csv file, so the statement seems to be ignoring $data[2}

Answer 1

You could use DateTime::createFromFormat() to create a DateTime object and then output it in a MySQL-readable way. For example, suppose your date is written in the format 'dmY', e.g. '10012010' for January, 10th 2010. To learn more about the format specification read the PHP's spec page linked above.

You could use the following function to convert from 'dmY' format to mysql-format

function convert($date) {
  $d = DateTime::createFromFormat('dmY', $date);
  return $d->format('Y-m-d H:i:s');
}

On a side note, I suggest you take a look at MySQL's LOAD DATA INFILE which is a much faster option to import a cvs file into a table.