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}
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.
Check this:
// ...
$formatFromFile = 'Y-m-d'; // your format of date in csv file
$date = DateTime::createFromFormat($formatFromFile, $data[2]);
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->format('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());
// ...
It's depends on the format of the date on the CSV file. Read about the function strtotime, maybe it's the solucion
It seems to me the problem is due to the different format of the date / time in your CSV file. From the info about wrong data inserted in the table, I'd assume the type of that column is DATETIME. This type has the following formatting YYYY-MM-DD HH:MM:SS, so if you have the date in the CSV file in a fomrat similar to 05/23/2012 5:35 p.m. or something else, which isn't the expected format, it's understandable that the problem occurs. In my opinion, you have two options:
Option 1:
Save the date in your CSV file, so it can be conform to MySQL's DATETIME. That is something like
2013-03-23 15:45:25
Then the insert should work properly.
Option 2:
This option might be more "fiddlier" than the previous, especially if you're not that way inclined (from reading the full text of the question). You'd need to create a new DateTime object in the PHP script and use its format() method when saving the data.
Unfortunately I don't know the format from the CSV file so I can just make a wild guess. Supposing the date in your file is formatted like this 03/24/2013 5:24 PM
if($data[0] !='Latitude'){
$date = DateTime::createFromFormat('m/d/Y g:i a', $data[2]);
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->format('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());
}
If I'm wrong about the format in the CSV file, that is the 'm/d/Y g:i a' part, you can have a look at the DateTime documentation to see how to fix it by choosing an appropriate format, so it fits your case.
Option 3:
As you seem to have PHP version < 5.3 installed on your hosting server, try the following:
if($data[0] !='Latitude'){
$timestamp = strtotime(str_replace('/', '-', $data[2]));
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', $timestamp))."',
'".mysql_real_escape_string($data[16])."',
'".mysql_real_escape_string($data[15])."',
'".mysql_real_escape_string($data[8])."'
)")or die(mysql_error());
}
Update
I've edited the $timestamp related line to match the format supplied by your CSV file.