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…