title: Multiple SET fields using LOAD DATA INFILE for Date Format
body: <p>I am able to use LOAD DATA INFILE to successfully get my csv file uploaded. I am able to skip all of the data/columns that I don't need (<a href="http://dev.mysql.com/doc/refman/5.0/en/load-data.html" rel="nofollow">Manual 13.2.6</a>)</p>
  
  <p>I was able to use the SET command to set the date format. My question is, What if I have multiple Date fields, any variation of the SET command combined with the above doesn't want to work when I have more than 1 SET.</p>
  
  <pre><code>&lt;?php 
  
  if (isset($_POST['load']))
  {
  include '_inc/include.php'; 
  
  $temp = $_FILES['myfile']['tmp_name'];
  $sqlstatement="LOAD DATA LOCAL INFILE '$temp' INTO TABLE 000_1616 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (id, @somedate, name, anotherdate, color, 1moredate) SET sdate = IF(LENGTH(@somedate)=7,STR_TO_DATE(@somedate,'%m/%d/%Y'),STR_TO_DATE(@somedate,'%m/%d/%y'))";
  mysql_query($sqlstatement) or die(mysql_error()); 
  
  echo "It worked";
  echo "&lt;p&gt;&lt;a href='upload-display.php'&gt;go to page&lt;/a&gt;&lt;/p&gt;";
  } 
  
  ?&gt;
  
  &lt;form method="post" action="upload.php" enctype="multipart/form-data"&gt;
  &lt;input name="myfile" type="file" /&gt;
  &lt;input name="load" type="submit" value="submit" /&gt;&lt;/form&gt;
  </code></pre>
  
  <p>My SET goes Like This</p>
  
  <pre><code>SET sdate = IF(LENGTH(@somedate)=7,STR_TO_DATE(@somedate,'%m/%d/%Y'),STR_TO_DATE(@somedate,'%m/%d/%y'))";
  </code></pre>
  
  <p>or</p>
  
  <pre><code>$sqlstatement="LOAD DATA LOCAL INFILE '$temp' INTO TABLE 000_1616 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (id, @somedate, name, anotherdate, color, 1moredate) SET sdate = IF(LENGTH(@somedate)=7,STR_TO_DATE(@somedate,'%m/%d/%Y'),STR_TO_DATE(@somedate,'%m/%d/%y'))";
  </code></pre>
  
  <p>I used @somedate for the first date field Which is really sdate. This works well so long as I don't try to add 4 more date fields using SET. I would like to add @anotherdate (anotherdate) and @1moredate(1moredate) Do I have to set up an array? Do I need to do something in the Table on the phpMyADMIN side? Right now they are formatted to Date but they all come back 000-00-00 except for the first date filed.</p>
  
  <p>Here is the SQL:</p>
  
  <pre><code>SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
  SET time_zone = "+00:00";
  
  
  /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
  /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
  /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
  /*!40101 SET NAMES utf8 */;
  
  --
  -- Database: `database_track`
  --
  
  -- --------------------------------------------------------
  
  --
  -- Table structure for table `000_datetest`
  --
  
  CREATE TABLE IF NOT EXISTS `000_datetest` (
    `id` int(11) NOT NULL auto_increment,
    `sdate` date default NULL,
    `name` varchar(100) collate utf8_unicode_ci default NULL,
    `anotherdate` date NOT NULL,
    `color` varchar(50) collate utf8_unicode_ci NOT NULL,
    `1moredate` date NOT NULL,
    PRIMARY KEY  (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=6 ;
  
  /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
  /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
  /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
  </code></pre>
  
  <p>csv example:</p>
  
  <pre><code>+----+---------------+-------------------+---------------------+------------+-------------------+
  | id | sdate          | name             | anotherdate     | color        | 1moredate     |
  +----+---------------+------------------------+---------------+------------+-------------------+
  |  1 | 2011-08-21 | Tom Thumb |    08/16/2010     |   Blue     | 1/5/08            |
  +----+---------------+------------------------+---------------+------------+-------------------+
  |  1 | 2009-05-12 | Don Duck      |    03/22/2012     |   Yellow   | 9/15/03       |
  +----+---------------+------------------------+---------------+------------+-------------------+
  1 row in set (0.00 sec)
  </code></pre>
  
  <p>I hope this helps, Please see physical file in comment below</p>