abigail Posted February 2, 2014 Share Posted February 2, 2014 How do get .csv date into MySql date. .csv always changes my text cell into a date cell, which MySql turns into "0000-00-00" Link to comment Share on other sites More sharing options...
HartleySan Posted February 3, 2014 Share Posted February 3, 2014 Without knowing more about the data itself, there's not much we can do to help. Sorry. Link to comment Share on other sites More sharing options...
abigail Posted February 3, 2014 Author Share Posted February 3, 2014 Here is .csv file, excel always changes it into type date (not text): 1/1/2014 1 2 3 4 5 6 1/2/2014 7 1 2 3 4 5 1/3/2014 7 1 2 3 4 5 1/4/2014 8 9 1 2 3 4 1/5/2014 8 9 1 2 3 4 Here is the query ($dd is the date): $q = "INSERT INTO I_FL_Draws (draw_date, morning_1, morning_2, morning_3, evening_1, evening_2, evening_3) VALUES ($dd, $mb1, $mb2, $mb3, $eb1, $eb2, $eb3)";$r = mysqli_query ($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));if ( (!mysqli_error ($dbc)) && (1 == mysqli_affected_rows($dbc)) ) { // If it inserted OK. $mycount++;} else { echo "<p class=\"error\">Could not insert one.</p>"; $sqlfailure = 1;} // did not insert ok And here is how I read $dd from .csv file: // open uploaded infile for read$fd = fopen ($realfile, "rb") or exit ("Unable to open file!");$mycount = 0;while(!feof($fd) ) { // Read one line from infile $oneline = fgetcsv($fd, 1024); if (isset($oneline[0])) $dd = $oneline[0]; But here is the oddest thing that I can't figure out. I debugged this. I changed the draw_date column to be not unique and allow null and I used NOW() for the date and it worked. So I then changed that to use $dd and it worked. Then I worked with another similar routine, which didn't work, I came back to this one and now it doesn't work. It inserts NOW() but $dd becomes 0000-00-00. Link to comment Share on other sites More sharing options...
Larry Posted February 3, 2014 Share Posted February 3, 2014 Your CSV dates are not in a format recognized by MySQL. You'll need to convert them within PHP prior to running the insert. Link to comment Share on other sites More sharing options...
Recommended Posts