3


I have a form which is used to upload a .csv file to insert data in mysql table. The file has a date column in a format "dd/mm/yyy". However I want to insert this date string in mysql date format "yyyy-mm-dd". I have the following code so far.

//parse data from csv file line by line
        while(($line = fgetcsv($csvFile)) !== FALSE){
         //insert member data into database
         $db->query("INSERT INTO txns (txn_date, description, amount, status) VALUES ('".$line[0]."','".$line[1]."','".$line[2]."','".$line[3]."','".$line[3]."')");
            }
        }

        //close opened csv file
        fclose($csvFile);

Please help to convert the date string to mysql date (yyyy-mm-dd) format.

sohal07
  • 440
  • 8
  • 21
  • 1
    let's not make use of built in functionality like LOAD DATA INFILE and instead try to reinvent the wheel while giving the uploader a chance to carry out an sql injection attack. – e4c5 Jan 05 '17 at 10:18
  • @e4c5 Hmm can you get `LOAD DATA INFILE` to automatically do date conversions? – RiggsFolly Jan 05 '17 at 10:22
  • depending on the text, yes @RiggsFolly you can do it – e4c5 Jan 05 '17 at 10:27

3 Answers3

5

You can do it like this:

$var = '05/01/2017';
$date = str_replace('/', '-', $var);
$result = date('Y-m-d', strtotime($date));

Output: 2017-01-05.

3

Try this:

$date = date('Y-m-d', strtotime($line[0]));  
// convert date to yyyy-mm-dd

and use $date as the value of column txn_date

Mayank Pandeyz
  • 25,704
  • 4
  • 40
  • 59
0
// date in dd/mm/yyyy format
$date = "05/01/2017"; 

//Explode by / to get an array with day, month, year in separate indexes
$dateArray = explode("/", $date); 

//Concatenate year, month and day into any format you want. 
$newDate = $dateArray[2] . "-" . $dateArray[1] . "-" . $dateArray[0];

You can make a function for this

Goma
  • 2,018
  • 1
  • 10
  • 19
Zeeshan
  • 54
  • 6