PHP PDO Import CSV File but webpage times out?

I am trying to run a members import script on a webpage using this code:

Form:

<?php

include('nav/head.php');

if($role_id != '1') {
	header('Location: error.php');
	exit();
}

?>

<!DOCTYPE html>
<html lang="en">

<head>

  <meta charset="utf-8">
  <meta http-equiv="X-UA-Compatible" content="IE=edge">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="">
  <meta name="author" content="">

  <title>CABGOP | Import Members List</title>

  <?php include('nav/header.php');  ?>

		<!-- ENTER CONTENT HERE -->

		<h1 class="h3 mb-2 text-gray-800">Import Members List</h1>
	<br>
	<form action="api/import.php" method="post" enctype="multipart/form-data">
		<div class="form-group">
			<input type="file" id="file" name="file" accept=".csv" autocomplete="off" />
		</div>
		<div class="form-group">
			<small>File types accepted: .csv</small>
		</div>
			<input type="submit" name="btn_upload" class="btn btn-success" value="Upload">
			<input type="submit" name="btn_back" class="btn btn-danger" value="Return to Members List">
	</form>

	<br>
	<a href="#" data-toggle="modal" data-target="#importModal"><small>Need Help?</small></a>
			<div class="modal fade" id="importModal" role="dialog" aria-labelledby="exampleModalLabel" aria-hidden="true" data-backdrop="static">
				<div class="modal-dialog modal-lg modal-dialog-scrollable" role="document">
					<div class="modal-content">
						<div class="modal-header">
							<h5 class="modal-title" id="exampleModalLabel">Import Troubleshooting</h5>
							<button class="close" type="button" data-dismiss="modal" aria-label="Close">
								<span aria-hidden="true">×</span>
							</button>
						</div>
						<div class="modal-body">
							<p>Before you can import your CSV file, you will likely have to modify the data that was originally generated by your software in your spreadsheet. Each spreadsheet that is imported needs to contain a column labeled <b><i>id</i></b>. This will act as a primary key in the database making each record unique. <br><br> Your spreadsheet will also need to match this exact table structure: </p>
								<ul>
									<li>id</li>
									<li>last_name</li>
									<li>first_name</li>
									<li>middle_name</li>
									<li>suffix</li>
									<li>residential_address</li>
									<li>mailing_address</li>
									<li>precinct</li>
									<li>age</li>
									<li>ethnicity</li>
									<li>gender</li>
									<li>race</li>
									<li>status</li>
									<li>phone</li>
									<li>reg_date</li>
								</ul>
								<b><u>Tips for Importing</u></b>
								<p>If your spreadsheet is an Excel workbook file <i>(.xlsx),</i> you will need to convert the file to a CSV before you can import your data.</p>
								<br>
								<p>If your spreadsheet does not match this table structure, you will get an error on import and will need to purge the database before importing again.</p>
								<br>
								<p>If your spreadsheet contains additional columns not mentioned in the order above, you will have to delete them as they cannot be added into the database.</p>
								<br>
								<p>If your spreadsheet has these columns in a different order, you will have to modify the order of your columns in your spreadsheet to match the table structure listed above.</p>
								<br>
								<p>If your spreadsheet contains these columns with a different name, you will have to rename the column in your spreadsheet.</p>
								<br>
								<p>If there is a column that is missing or has not been included in the original data, you will need to add it in the spreadsheet before importing even if there is no data.</p>
						</div>
						<div class="modal-footer">
						</div>
					</div>
				</div>
			</div>

        </div>
        <!-- /.container-fluid -->

      </div>
      <!-- End of Main Content -->

	<?php include('nav/footer.php'); ?>

</html>

API:

<?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 members (id, last_name, first_name, 
middle_name, suffix, residential_address, mailing_address, precinct, age, 
ethnicity, gender, race, status, phone, reg_date) 
VALUES (:id, :last_name, :first_name, :middle_name, :suffix, 
:residential_address, :mailing_address, :precinct, :age, :ethnicity, :gender, 
:race, :status, :phone, :reg_date)");

	while (! feof($file)) {
  		$row = fgetcsv($file);
			if($row[0] == NULL) {
				continue;
			}
			$stmt->bindParam(':id', $row[0]);
			$stmt->bindParam(':last_name', $row[1]);
		$stmt->bindParam(':first_name', $row[2]);
		$stmt->bindParam(':middle_name', $row[3]);
		$stmt->bindParam(':suffix', $row[4]);
  		$stmt->bindParam(':residential_address', $row[5]);
  		$stmt->bindParam(':mailing_address', $row[6]);
  		$stmt->bindParam(':precinct', $row[7]);
  		$stmt->bindParam(':age', $row[8]);
  		$stmt->bindParam(':ethnicity', $row[9]);
  		$stmt->bindParam(':gender', $row[10]);
  		$stmt->bindParam(':race', $row[11]);
  		$stmt->bindParam(':status', $row[12]);
  		$stmt->bindParam(':phone', $row[13]);
			$date = date_create($row[14]);
			$dfstr = date_format($date, 'Y-m-d');
			$stmt->bindParam(':reg_date', $dfstr);
  		$stmt->execute();
}

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

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

?>

This is the same structure of code from a tax application I wrote. That codes looks like this:

Form:

<?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 (id, 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 (:id, :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);
			if($row[0] == NULL) {
				continue;
			}
			$stmt->bindParam(':id', $row[0]);
			$stmt->bindParam(':Invoice_Number', $row[1]);
		$stmt->bindParam(':Customer_Number', $row[2]);
		$stmt->bindParam(':Customer_Name', $row[3]);
		$date = date_create($row[4]);
		$dfstr = date_format($date, 'Y-m-d');
		$stmt->bindParam(':Invoice_Date', $dfstr);
  		$stmt->bindParam(':Sales_Order_Number', $row[5]);
  		$stmt->bindParam(':Sales_Amount', $row[6]);
  		$stmt->bindParam(':Progress_Billing', $row[7]);
  		$stmt->bindParam(':Extra_Charge_Amount', $row[8]);
  		$stmt->bindParam(':Tax_Amount', $row[9]);
  		$stmt->bindParam(':Invoice_Amount', $row[10]);
  		$stmt->bindParam(':Writeoff_Amount', $row[11]);
  		$stmt->bindParam(':Paid_Amount', $row[12]);
  		$stmt->bindParam(':Balance_Due', $row[13]);
			$stmt->bindParam(':Use_Tax', $row[14]);
			$stmt->bindParam(':Tax_Stat', $row[15]);
			$stmt->bindParam(':Salesperson_1', $row[16]);
			$stmt->bindParam(':Comm_Rate_1', $row[17]);
			$stmt->bindParam(':Salesperson_2', $row[18]);
			$stmt->bindParam(':Comm_Rate_2', $row[19]);
			$stmt->bindParam(':Salesperson_3', $row[20]);
			$stmt->bindParam(':Comm_Rate_3', $row[21]);
			$stmt->bindParam(':Deposit_Applied', $row[22]);
			$stmt->bindParam(':Sales_Tax_Codes', $row[23]);
			$stmt->bindParam(':Sales_Tax_Amounts', $row[24]);
			$stmt->bindParam(':Customer_Address1', $row[25]);
			$stmt->bindParam(':Customer_Address2', $row[26]);
			$stmt->bindParam(':Customer_City', $row[27]);
			$stmt->bindParam(':Customer_State', $row[28]);
			$stmt->bindParam(':Customer_Zip', $row[29]);
			$stmt->bindParam(':Customer_Country', $row[30]);
  		$stmt->execute();

}

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

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

?>

API:

<?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 (id, 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 (:id, :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);
			if($row[0] == NULL) {
				continue;
			}
			$stmt->bindParam(':id', $row[0]);
			$stmt->bindParam(':Invoice_Number', $row[1]);
		$stmt->bindParam(':Customer_Number', $row[2]);
		$stmt->bindParam(':Customer_Name', $row[3]);
		$date = date_create($row[4]);
		$dfstr = date_format($date, 'Y-m-d');
		$stmt->bindParam(':Invoice_Date', $dfstr);
  		$stmt->bindParam(':Sales_Order_Number', $row[5]);
  		$stmt->bindParam(':Sales_Amount', $row[6]);
  		$stmt->bindParam(':Progress_Billing', $row[7]);
  		$stmt->bindParam(':Extra_Charge_Amount', $row[8]);
  		$stmt->bindParam(':Tax_Amount', $row[9]);
  		$stmt->bindParam(':Invoice_Amount', $row[10]);
  		$stmt->bindParam(':Writeoff_Amount', $row[11]);
  		$stmt->bindParam(':Paid_Amount', $row[12]);
  		$stmt->bindParam(':Balance_Due', $row[13]);
			$stmt->bindParam(':Use_Tax', $row[14]);
			$stmt->bindParam(':Tax_Stat', $row[15]);
			$stmt->bindParam(':Salesperson_1', $row[16]);
			$stmt->bindParam(':Comm_Rate_1', $row[17]);
			$stmt->bindParam(':Salesperson_2', $row[18]);
			$stmt->bindParam(':Comm_Rate_2', $row[19]);
			$stmt->bindParam(':Salesperson_3', $row[20]);
			$stmt->bindParam(':Comm_Rate_3', $row[21]);
			$stmt->bindParam(':Deposit_Applied', $row[22]);
			$stmt->bindParam(':Sales_Tax_Codes', $row[23]);
			$stmt->bindParam(':Sales_Tax_Amounts', $row[24]);
			$stmt->bindParam(':Customer_Address1', $row[25]);
			$stmt->bindParam(':Customer_Address2', $row[26]);
			$stmt->bindParam(':Customer_City', $row[27]);
			$stmt->bindParam(':Customer_State', $row[28]);
			$stmt->bindParam(':Customer_Zip', $row[29]);
			$stmt->bindParam(':Customer_Country', $row[30]);
  		$stmt->execute();

}

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

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

?>

The tax application import code works, but the members import doesn’t. When I run the import script, it’s like the webpage times out completely and says it couldn’t process the request? What do I do to solve this? Is there a difference in these two scripts that I am not seeing? I feel like this is something so simple that I’m overlooking…

Are there a lot of entries in the members CSV file? You could alter the timeout value.

Also, remember you can bind_param() before the loop, and just call execute() each time you read a new set of values into the variables.

The following is a warning in the feof() documentation -

Warning
If the passed file pointer is not valid you may get an infinite loop, because feof() fails to return TRUE.

Because your code is not validating that the file upload was successful, before referencing any of the uploaded file information, and is not testing if the fopen() worked, before looping using feof() as the while loop condition, you have an infinite loop. feof() is always false, so !false is always true. If you used the fgetcsv() in the while loop condition, the infinite loop wouldn’t have occurred. You also apparently don’t have php’s error related settings set up so that php would help you by reporting and displaying all the errors it detects. The fopen(), initial fgetcsv() and each feof() call would be producing php errors to alert you to the problem.

1 Like

@mabismad
The tax code is working though and the code is practically the same except for the data that it is importing. So why would this matter?

I have had success with the following and well worth setting up because it only takes seconds to import thousands of records:

Yes there are. How can I alter the timeout value to test the import?

I’m not sure, but a quick google leads me to set_time_limit(): https://www.php.net/manual/en/function.set-time-limit.php

Seems as if you could keep extending the time limit each time you’ve imported ‘x’ number of rows, rather than just setting it to some arbitrary massive value.