PHP Format Dates with fgetcsv

I am trying to run an import in a program I’m writing, but the dates are not coming back properly.

In my Excel CSV spreadsheet, the dates are listed in this format under the column Invoice_Date:

07/02/2020

The code I’ve written so far completes the import, however the dates come back as 0000-00-00.

Here is my current import code:

<?php

include('dbconnect.php');

if(isset($_POST['btn_upload'])){
	$filename = $_FILES['file']['tmp_name'];
	$file = fopen($filename,"r");


	// Read and throw away the header row
	fgetcsv($file);

	$stmt = $pdo->prepare("INSERT INTO invoices (Invoice_Number, Customer_Number, Customer_Name, Invoice_Date, Sales_Order_Number, Sales_Amount, Progress_Billing, Extra_Charge_Amount, Tax_Amount, Invoice_Amount, Writeoff_Amount, Paid_Amount, Balance_Due, Use_Tax, Tax_Stat, Salesperson_1, Comm_Rate_1, Salesperson_2, Comm_Rate_2, Salesperson_3, Comm_Rate_3, Deposit_Applied, Sales_Tax_Codes, Sales_Tax_Amounts, Customer_Address1, Customer_Address2, Customer_City, Customer_State, Customer_Zip, Customer_Country) VALUES (:Invoice_Number, :Customer_Number, :Customer_Name, :Invoice_Date, :Sales_Order_Number, :Sales_Amount, :Progress_Billing, :Extra_Charge_Amount, :Tax_Amount, :Invoice_Amount, :Writeoff_Amount, :Paid_Amount, :Balance_Due, :Use_Tax, :Tax_Stat, :Salesperson_1, :Comm_Rate_1, :Salesperson_2, :Comm_Rate_2, :Salesperson_3, :Comm_Rate_3, :Deposit_Applied, :Sales_Tax_Codes, :Sales_Tax_Amounts, :Customer_Address1, :Customer_Address2, :Customer_City, :Customer_State, :Customer_Zip, :Customer_Country)");

	while (! feof($file)) {
  		$row = fgetcsv($file);
  		$stmt->bindParam(':Invoice_Number', $row[0]);
		$stmt->bindParam(':Customer_Number', $row[1]);
		$stmt->bindParam(':Customer_Name', $row[2]);
		$stmt->bindParam(':Invoice_Date', $row[3]);
  		$stmt->bindParam(':Sales_Order_Number', $row[4]);
  		$stmt->bindParam(':Sales_Amount', $row[5]);
  		$stmt->bindParam(':Progress_Billing', $row[6]);
  		$stmt->bindParam(':Extra_Charge_Amount', $row[7]);
  		$stmt->bindParam(':Tax_Amount', $row[8]);
  		$stmt->bindParam(':Invoice_Amount', $row[9]);
  		$stmt->bindParam(':Writeoff_Amount', $row[10]);
  		$stmt->bindParam(':Paid_Amount', $row[11]);
  		$stmt->bindParam(':Balance_Due', $row[12]);
			$stmt->bindParam(':Use_Tax', $row[13]);
			$stmt->bindParam(':Tax_Stat', $row[14]);
			$stmt->bindParam(':Salesperson_1', $row[15]);
			$stmt->bindParam(':Comm_Rate_1', $row[16]);
			$stmt->bindParam(':Salesperson_2', $row[17]);
			$stmt->bindParam(':Comm_Rate_2', $row[18]);
			$stmt->bindParam(':Salesperson_3', $row[19]);
			$stmt->bindParam(':Comm_Rate_3', $row[20]);
			$stmt->bindParam(':Deposit_Applied', $row[21]);
			$stmt->bindParam(':Sales_Tax_Codes', $row[22]);
			$stmt->bindParam(':Sales_Tax_Amounts', $row[23]);
			$stmt->bindParam(':Customer_Address1', $row[24]);
			$stmt->bindParam(':Customer_Address2', $row[25]);
			$stmt->bindParam(':Customer_City', $row[26]);
			$stmt->bindParam(':Customer_State', $row[27]);
			$stmt->bindParam(':Customer_Zip', $row[28]);
			$stmt->bindParam(':Customer_Country', $row[29]);
  		$stmt->execute();
}

	fclose($file);
	header("Location: ../members_list.php");
}

if(isset($_POST['btn_back'])) {
	header("Location: ../members_list.php");
}

?>

I read on StackOverflow that I can make a stored procedure, but I’m not sure how to make one to convert the date properly. What should I do?

MySQL will expect entries for a DATE field to be in YYYY-MM-DD format.

You can either explode/implode the date value in the php code to get it into the correct format or you can use STR_TO_DATE() in the sql query to produce a date value from the supplied format.

How would I set that up?

STR_TO_DATE(str,format);

would it be:

STR_TO_DATE(:Invoice_Date, %Y, %m, %d);

or would implode/explode be easier?

You can use the following to parse your date into a PHP DateTime object, then format it as MySQL expects:

\DateTime::createFromFormat('d/m/Y', $row[3])->format('Y-m-d')

Replace the reference to $row[3] in your bindParam() call with the above, and it should work.

The \DateTime::createFromFormat() part accepts a format string and an input string, and creates a DateTime object representing that date. It will optionally do time as well, but you don’t need that here.

The ->format('Y-m-d') part outputs the created DateTime object as a string with the provided format.

I get the following error when I replace the code:

Fatal error: 

Uncaught Error: Call to a member function format() on bool in 
C:\xampp\htdocs\cascotax\api\import.php:20 Stack trace: #0 {main} thrown in 
C:\xampp\htdocs\cascotax\api\import.php on line 20

Show us the current version of your code?