I'm working on file import feature from an Excel file. During the import, I'm using PhpSpreadSheet library to convert this file to CSV format. Next, my function extractData() will loop csv file, detect specific headers, combine data corresponding to them, and insert them into database.
My configuration on these machines :
-
Dev Machine :
- Debian 9
- PHP 7.3.2
- Local Machine :
- Debian 8
- PHP 7.3.2
My algorithm is working well on my local machine, but when I'm testing it on my development machine, it sends me back these errors :
PHP Warning: array_combine() expects parameter 2 to be array, null given
PHP Warning: array_intersect_key(): Expected parameter 1 to be an array, null given
Is PHP more stable on Debian 8 and not enough on Debian 9 ?
How can I fix this problem?
Example of CSV :
const PATTERNS = [
'fullName' => '/^(?:noms?\/pré?e?noms?)/ui',
'lastName' => '/^(?:votre nom|nom)/ui',
'firstName' => '/^(?:votre prénom|prénom|prénoms)/ui',
'zipcode' => '/^(?:secteur|commune|zone|ville hbt|Dans quelle zone géographique habitez-vous \?)/ui',
'birthday' => '/^(?:votre date de naissance|Date de naissance|En quelle année êtes-vous née?)/ui',
'gender' => '/^(?:votre sexe|sexe)/ui',
'contact' => '/^(?:contact tel\/mail|contacts?)/ui',
'postalAddress' => '/^(?:adresse|zone hbt|secteur d\'habitation)/ui',
'phoneNumber' => '/^(?:votre numéro de téléphone|Téléphone|n° de téléphone|contact tel|tel)/ui',
'employment' => "/^(?:votre profession|votre métier|profession|métier|dans quel(?:\(s\))? secteur(?:\(s\))? d'activité exercez-vous cette activité \?|secteur d'activité)/ui",
'email' => '/^(?:votre adresse e-mail|votre adresse mail|votre mail|votre email|e-mail|mail)/ui',
'ratings' => '/^(?:appréciation)/ui',
'comments' => '/^(?:commentaires?|remarque)/ui',
];
function extractData($file, $cnx)
{
$headers = [];
$state = 0;
$flag = true;
if(($handle = fopen($file, 'r')) !== FALSE)
{
foreach (fgetcsv($handle, 1000, ";") as $key => $header)
{
foreach (PATTERNS as $symbol => $pattern)
{
if (!in_array($symbol, $headers) && preg_match($pattern, $header))
{
$headers[$key] = $symbol;
break;
}
}
}
// Loop file from lign 2 to more
while ($line = fgetcsv($handle, 1000, ";"))
{
$line = array_combine($headers, array_intersect_key($line, $headers));
if($line['lastName'] === "" || $line['firstName'] === "")
{
continue;
}
if($line['fullName'] !== NULL)
{
$formatNames = formatNames($line['fullName']);
$line['lastName'] = $formatNames[0];
$line['firstName'] = $formatNames[1];
}
if($line['contact'] !== NULL)
{
$formatContact = formatContact($line['contact']);
$line['phoneNumber'] = $formatContact[0];
$line['email'] = $formatContact[1];
}
$lastName = $line['lastName'] !== NULL ? $line['lastName'] : "";
$firstName = $line['firstName'] !== NULL ? $line['firstName'] : "";
$formatZipcode = translateZipcode($line['zipcode']);
$zipcode = $formatZipcode !== NULL ? $formatZipcode : "";
$birthday = $line['birthday'] !== NULL ? $line['birthday'] : "";
$gender = $line['gender'] !== NULL ? $line['gender'] : "";
$postalAddress = $line['postalAddress'] !== NULL ? $line['postalAddress'] : "";
$formatPhoneNumber = formatPhoneNumber($line['phoneNumber']);
if($line['contact'] !== NULL)
{
$phoneNumber = $line['phoneNumber'] !== NULL ? $line['phoneNumber'] : "";
}
else
{
$phoneNumber = $formatPhoneNumber !== NULL ? $formatPhoneNumber : "";
}
$availableDays = $line['availableDays'] !== NULL ? $line['availableDays'] : "";
$employment = $line['employment'] !== NULL ? $line['employment'] : "";
$transportMeans = $line['transportMeans'] !== NULL ? $line['transportMeans'] : "";
$candidacyType = $line['candidacyType'] !== NULL ? $line['candidacyType'] : "";
$email = $line['email'] !== NULL ? $line['email'] : "";
$query = $cnx->prepare('INSERT INTO candidacies (lastName, firstName, zipcode, birthday, gender, postalAddress, phoneNumber, availableDays, employment, transportMeans, candidacyType, email, candidacies_UserId, candidacy_CreatedAt, state) VALUES (:lastName, :firstName, :zipcode, :birthday, :gender, :postalAddress, :phoneNumber, :availableDays, :employment, :transportMeans, :candidacyType, :email, :candidacies_UserId, NOW(), :state)');
$query->bindValue(':lastName', $lastName, PDO::PARAM_STR);
$query->bindValue(':firstName', $firstName, PDO::PARAM_STR);
$query->bindValue(':zipcode', $zipcode, PDO::PARAM_STR);
$query->bindValue(':birthday', $birthday, PDO::PARAM_STR);
$query->bindValue(':gender', $gender, PDO::PARAM_STR);
$query->bindValue(':postalAddress', $postalAddress, PDO::PARAM_STR);
$query->bindValue(':phoneNumber', $phoneNumber, PDO::PARAM_STR);
$query->bindValue(':availableDays', $availableDays, PDO::PARAM_STR);
$query->bindValue(':employment', $employment, PDO::PARAM_STR);
$query->bindValue(':transportMeans', $transportMeans, PDO::PARAM_STR);
$query->bindValue(':candidacyType', $candidacyType, PDO::PARAM_STR);
$query->bindValue(':email', $email, PDO::PARAM_STR);
$query->bindValue(':candidacies_UserId', $_SESSION['user']->userId, PDO::PARAM_INT);
$query->bindValue(':state', "In progress", PDO::PARAM_STR);
if($query->execute())
{
$state = 1;
}
$query = $cnx->prepare('SELECT MAX(candidacyId) AS id FROM candidacies');
$query->execute();
$getCandidacyId = $query->fetch(PDO::FETCH_OBJ);
if($line['comments'] !== NULL)
{
$comments = $line['comments'];
if($comments === "")
{
continue;
}
$query = $cnx->prepare('INSERT INTO comments (comments, comment_CreatedAt, comments_UserId, comments_CandidaciesId) VALUES (:comments, NOW(), :comments_UserId, :comments_CandidaciesId)');
$query->bindValue(':comments', $comments, PDO::PARAM_STR);
$query->bindValue(':comments_UserId', $_SESSION['user']->userId, PDO::PARAM_INT);
$query->bindValue(':comments_CandidaciesId', $getCandidacyId->id, PDO::PARAM_INT);
if($query->execute())
{
$state = 1;
}
}
$query = $cnx->prepare('SELECT MAX(commentsId) AS id FROM comments');
$query->execute();
$getCommentId = $query->fetch(PDO::FETCH_OBJ);
if($line['ratings'] !== NULL)
{
$ratings = formatRatings($line['ratings']);
if($ratings === "")
{
continue;
}
$query = $cnx->prepare('INSERT INTO ratings (ratings, rating_CreatedAt, ratings_UserId, ratings_CandidacyId, ratings_CommentId) VALUES (:ratings, NOW(), :ratings_UserId, :ratings_CandidacyId, :ratings_CommentId)');
$query->bindValue(':ratings', $ratings, PDO::PARAM_INT);
$query->bindValue(':ratings_UserId', $_SESSION['user']->userId, PDO::PARAM_INT);
$query->bindValue(':ratings_CandidacyId', $getCandidacyId->id, PDO::PARAM_INT);
$query->bindValue(':ratings_CommentId', $getCommentId->id, PDO::PARAM_INT);
if($query->execute())
{
$state = 1;
}
}
}
fclose($handle);
return $state;
}
}
if(extractData($loadedSheetName.'.csv', $cnx) === 1)
{
$_SESSION['flash']['uploadFile'] = "<div class='alert alert-success'>Success !</div>";
header('Location: ../listCandidacies.php');
}
else
{
$_SESSION['flash']['uploadFile'] = "<div class='alert alert-danger'>Error !</div>";
exit(0);
}