Importing CSV Data into MySQL Database with PHP File Handling
Import CSV to Mysql using PHP File Read
There are many ways to implement CSV import. We have seen one or two examples of it previously. Even, this article gives two examples on CSV to MySQL import To import a CSV file into a MySQL database using PHP, you can follow these steps and use the following code as an example. In this example, we’ll assume you have a CSV file named “data.csv” with columns “column1,” “column2,” and “column3,” and you want to import it into a MySQL table named “mytable.”.
Target MySQL database
CREATE TABLE `tbl_users` ( `id` int(11) NOT NULL, `userName` varchar(255) NOT NULL, `firstName` varchar(255) NOT NULL, `lastName` varchar(255) NOT NULL, `create_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() ); ALTER TABLE `tbl_users` ADD PRIMARY KEY (`id`); ALTER TABLE `tbl_users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
HTML form to choose CSV source
This HTML form allows users to choose the CSV source file. It accepts CSV and Excel files to read data to import.
index.php (Import Form)
CSS and JavaScript validation script
The above form calls the validation script on submit. The validation script is in JavaScript to check if the file input is not empty. It is in the head section of the same index.php page.
index.php (Validation) add in script tag top of the head
function validateFile() {
var csvInputFile = document.forms["frmCSVImport"]["file"].value;
if (csvInputFile == "") {
error = "No source found to import. Please choose a CSV file. ";
$("#response").html(error).addClass("error");;
return false;
}
return true;
}
CSV to MySQL import in PHP
On submitting the form, the PHP code triggers the CSV read handler. The readUserRecords() function of the UserModel class is doing this CSV parsing.
index.php (php code add top of the page)
namespace Phppot;
use Phppot\DataSource;
require_once __DIR__ . '/lib/UserModel.php';
$userModel = new UserModel();
if (isset($_POST["import"])) {
$response = $userModel->readUserRecords();
}
and the UserModel class contains the functions to do the following.
Read CSV data using PHP fgetcsv()
Verify CSV to ignore empty rows.
Read all imported data from the MySQL database.
This model class connects the database in its constructor and set the connection object.
UserModel.php
namespace Phppot;
use Phppot\DataSource;
class UserModel
{
private $conn;
function __construct()
{
require_once 'DataSource.php';
$this->conn = new DataSource();
}
function getAllUser()
{
$sqlSelect = "SELECT * FROM users";
$result = $this->conn->select($sqlSelect);
return $result;
}
function readUserRecords()
{
$fileName = $_FILES["file"]["tmp_name"];
if ($_FILES["file"]["size"] > 0) {
$file = fopen($fileName, "r");
$importCount = 0;
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
if (! empty($column) && is_array($column)) {
if ($this->hasEmptyRow($column)) {
continue;
}
if (isset($column[1], $column[3], $column[4])) {
$userName = $column[1];
$password = $column[2];
$firstName = $column[3];
$lastName = $column[4];
$insertId = $this->insertUser($userName, $password, $firstName, $lastName);
if (! empty($insertId)) {
$output["type"] = "success";
$output["message"] = "Import completed.";
$importCount ++;
}
}
} else {
$output["type"] = "error";
$output["message"] = "Problem in importing data.";
}
}
if ($importCount == 0) {
$output["type"] = "error";
$output["message"] = "Duplicate data found.";
}
return $output;
}
}
function hasEmptyRow(array $column)
{
$columnCount = count($column);
$isEmpty = true;
for ($i = 0; $i < $columnCount; $i ++) {
if (! empty($column[$i]) || $column[$i] !== '') {
$isEmpty = false;
}
}
return $isEmpty;
}
function insertUser($userName, $password, $firstName, $lastName)
{
$sql = "SELECT userName FROM users WHERE userName = ?";
$paramType = "s";
$paramArray = array(
$userName
);
$result = $this->conn->select($sql, $paramType, $paramArray);
$insertId = 0;
if (empty($result)) {
$hashedPassword = password_hash($password, PASSWORD_DEFAULT);
$sql = "INSERT into users (userName,password,firstName,lastName)
values (?,?,?,?)";
$paramType = "ssss";
$paramArray = array(
$userName,
$hashedPassword,
$firstName,
$lastName
);
$insertId = $this->conn->insert($sql, $paramType, $paramArray);
}
return $insertId;
}
}
?>
List imported data from the database
This is for closing the CSV to MySQL import process loop. With this step, the user experiences that the import is completed successfully.
It shows the imported data on the screen. Instead of showing a static message like ‘Imported successfully’, this will give a good user experience.
list.php
<?php
namespace
$result = $userModel->getAllUser();
if (! empty($result)) {
?>
<h3>Imported records:</h3>
<table id='userTable'>
<thead>
<tr>
<th>User Name</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
</thead>
<?php
foreach ($result as $row) {
?>
<tbody>
<tr>
<td><?php echo $row['userName']; ?></td>
<td><?php echo $row['firstName']; ?></td>
<td><?php echo $row['lastName']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php
}
?>
Output: CSV to MySQL import
