I have an issue with DATE formatting. I am using LOAD DATA INFILE and the file is being imported by the user. The file has several DATE columns. The table columns containing the DATE are not text they are DATE. All I get imported to the table in the DATE columns are 000-00-00. I know if I change them to text I can use STR to call or change them to Date format, but my question is If I want to run a lot of queries on these fields like comparing them or counting days between them doesn't this have to be DATE in the table, or does the value carry over if I use STR. Is this an output thing or do I have to do something during the input?
I did find this code and it looks like it makes total and complete sense using session variables. The SET command allows us to define how the value is to be assigned to that particular field. I want to use the SET command during input, however I am not sure how to use it, or where exactly to put it or is my table fields wrong or is this a MySQL thing where I need to set it up differently.
Now what if I have more than 1 date column/field
SET MyDateColumn1 = IF(LENGTH(@somedate)=8,STR_TO_DATE(@somedate,'%m/%d/%y'),STR_TO_DATE(@somedate,'%m/%d/%Y'));
Here is the code I am using now:
Any help would be greatly appreciated
$temp = $_FILES['myfile']['tmp_name'];
$sqlstatement="LOAD DATA LOCAL INFILE '$temp' INTO TABLE myupload_table FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@COL1, @COL2, @COL3, @COL4, @COL5, @COL6, @COL7, @COL8, @COL9, @COL10, @COL11, @COL12, @COL13, date1, date2, date3, @COL17, normalfield1, date4, date5, date6, @COL22, @COL23, @COL24, normalfield2, normalfield3, @COL27, @COL28, @COL29, .......)";
mysql_query($sqlstatement) or die(mysql_error());
echo "It worked";
echo "<p><a href='test.php'>go to page</a></p>";
<form method="post" action="upload.php" enctype="multipart/form-data">
<input name="myfile" type="file" />
<input name="load" type="submit" value="submit" /></form>