We are creating a function that exports data from a table to a CSV then deletes the data from the table. We have created the ability for the user to select if there is a header in the first row of the CSV (yes or no radio buttons). However, when no is selected, the function only runs for the very first row but doesn't continue to loop the other rows. Any ideas on what is missing?
<?php
require_once('connection.php');
session_start();
if (!$_SESSION['user']) {
header("Location: index.php"); // If session is not set that redirect to Login Page
}
//set successful imported rows count to 0
$successCount = 0;
if(isset($_POST['submit'])){
$skip = mysqli_real_escape_string($csvDatabase, $_POST['header']);
$colNumber = mysqli_real_escape_string($csvDatabase, $_POST['SUIDnumber']);
$colNumber = $colNumber - 1;
//get filename
$filename = $_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0) {
for($i=0; $i<count($filename); $i++) {
$file = $filename[$i];
//open file in read only
$files = fopen($file, "r");
//skips first line
if ($skip === "yes") {
fgetcsv($files, 10000, ",");
}
//get data from csv & uses comma to find separate values
while (($getData = fgetcsv($files, 10000, ",")) !== FALSE)
{
$fail = FALSE;
//store SUID from pre-set line in csv
$suid = $getData[$colNumber];
if (strlen($suid) === 9 && ctype_digit($suid) ) {
// start ldap look up
$basedn="***";
//Connect to server
$ds=ldap_connect("***");
if ($ds) {
//bind with our special account that retrieves more attributes
$ldaprdn = '***'; // ldap rdn or dn
$ldappass = '**'; // associated password
$r=ldap_bind($ds,$ldaprdn,$ldappass); // this is an authenticated bind
if (substr($suid, 0, 1) === ";" || is_numeric($suid)) {
if ($r) {
//filter to all objectclasses that the SUID we are looking for
$filter = "(&(objectClass=*)(syrEduSUID={$suid}))";
//We are only interested in retrieving these attributes
$justthese = array("displayName", "syrEduLevel", "syrEduProgramDesc", "syrEduProgram", "mail", "eduPersonPrimaryAffiliation", "eduPersonAffiliation", "uid" );
// Search SUID
$sr=ldap_search($ds, $basedn, $filter, $justthese );
$entry = ldap_get_entries($ds, $sr);
// if we have only one result, return the values, if not, we have a problem
if ($entry["count"] == 1) {
// get student name and email from suid
$studentName = mysqli_real_escape_string($csvDatabase, $entry[0]['displayname'][0]);
$studentEmail = mysqli_real_escape_string($csvDatabase, $entry[0]['mail'][0]);
$studentAffiliation = mysqli_real_escape_string($csvDatabase, $entry[0]['edupersonprimaryaffiliation'][0]);
$studentProgram = mysqli_real_escape_string($csvDatabase, $entry[0]['syreduprogramdesc'][0]);
$studentEduLevel = mysqli_real_escape_string($csvDatabase, $entry[0]['syredulevel'][0]);
$netID = mysqli_real_escape_string($csvDatabase, $entry[0]['uid'][0]);
$successCount++;
// close ldap
ldap_close($ds);
} else {
$msg = "Ldap search returned 0 or more than one result";
$fail = TRUE;
}
}
} else {
$msg = "Bind failed";
$fail = TRUE;
}
} else {
$msg = "LDAP connection failed";
$fail = TRUE;
}
//split full name
$studentName = trim($studentName);
$last_name = (strpos($studentName, ' ') === false) ? '' : preg_replace('#.*\s([\w-]*)$#', '$1', $studentName);
$first_name = trim( preg_replace('#'.$last_name.'#', '', $studentName ) );
//inserts data into import table
$sql = "INSERT into import (suid, firstName, lastName, studentEmail, studentAffiliation, studentProgram, studentEduLevel, netID) values ('$suid', '$first_name', '$last_name', '$studentEmail', '$studentAffiliation', '$studentProgram', '$studentEduLevel', '$netID')";
if (!$fail) {
if (mysqli_query($csvDatabase, $sql)) {
//once imported properly, export csv
} else {
echo "Error: " . $sql . "<br>" . mysqli_error($csvDatabase);
}
}
}
}
//closes file
fclose($files);
$query = "SELECT suid, firstName, lastName, studentEmail, studentAffiliation, studentProgram, studentEduLevel from import ORDER BY id ASC LIMIT {$successCount}";
$result = mysqli_query($csvDatabase, $query);
if ($result->num_rows > 0) {
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=data-export.csv');
$output = fopen("php://output", "w");
$headers = array('SUID', 'First Name', 'Last Name', 'Student Email', 'Student Affiliation', 'studentProgram', 'Student Edu Level');
fputcsv($output, $headers);
while($row = mysqli_fetch_assoc($result))
{
fputcsv($output, $row);
}
fclose($output);
//then delete records in database
$deletesql = "DELETE FROM import ORDER BY id DESC LIMIT {$successCount}";
if (mysqli_query($csvDatabase, $deletesql)) {
//echo "Record deleted successfully";
} else {
echo "Error deleting record: " . mysqli_error($csvDatabase);
}
}
}
} else {
echo "You did not upload a CSV file or the CSV file is blank.";
}
} else {
?>
<!doctype html>
<html class="no-js" 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.0" />
<title>CSV Import</title>
<link rel="stylesheet" href="css/foundation.min.css" />
<link rel="stylesheet" href="css/app.css" />
</head>
<body>
<!-- nav -->
<div class="top-bar">
<div class="top-bar-left">
<ul class="menu">
</ul>
</div>
<div class="top-bar-right">
</div>
</div>
<div class="row" style="margin-top: 5%;">
<div class="medium-12 columns">
<form name="upload_excel" method="post" enctype="multipart/form-data">
<h3>Import CSVs for Student Data</h3>
<div class="callout secondary">
<fieldset class="large-4 cell">
<legend>Does the CSV have a header in the first row?</legend>
<input type="radio" name="header" value="yes" id="yesHeader"><label for="yesHeader">Yes</label>
<input type="radio" name="header" value="no" id="noHeader"><label for="noHeader">No</label>
</fieldset>
<label for="SUIDnumber">
What number column is the SUID field in?
<input type="number" value="" id="SUIDnumber" name="SUIDnumber" required>
</label>
<p>Upload your CSV(s) with SUIDs. You will then be prompted to download the exported data.</p>
<input type="file" id="files" name="file[]" accept=".csv" multiple><br>
<input type="submit" class="button" id="submit" name="submit" value="Import CSV">
</div>
</form>
</div>
</div>
<script src="js/vendor/jquery.min.js"></script>
<script src="js/vendor/what-input.min.js"></script>
<script src="js/foundation.min.js"></script>
<script src="js/app.js"></script>
</body>
</html>
<?php } ?>
