This is a very interesting publication in which we dealt with topics such as uploading CSV file data. Before we import them into the MySQL table, we can edit the CSV file data on the website using jquery Ajax using PHP. So what are the advantages of this function? Assuming we want to edit the data in the CSV file, we can edit it directly in the CSV file and then continue with the import. Assume that you do not know that there is a spelling mistake, that the information in the CSV file data is incorrect, and that you have started loading the CSV file to import data. Then you cannot cancel the import process and the wrong data will be imported. directly in the MySQL table. If you have loaded the CSV file in this function to import it into the MySQL table, all data of the CSV file will first be displayed on the website in tabular format on the website, and you can edit the data in the table column beforehand to import After uploading the file you can check all the data in the CSV file on the website. If wrong data is found, you can edit it and after importing it into the database you can use PHP with Ajax jQuery.
In today's web development, new and new innovations have been developed to reduce errors. Therefore, this function is also an innovation when importing data via the CSV file. Since the data was checked twice and incorrect information was found, we can edit the data in the CSV file live before importing it. We all know that the CSV file format is often used to import and export data from any web application. Then, if you have created a company-level web application and have CSV file import data, this feature will reduce the workload because we can edit the CSV file data after loading the CSV file and before importing it into the database. , Therefore, this function reduces the import of incorrect data into the database. Here we have another tutorial on editing CSV file data live and after importing it into the MySQL database using PHP with Ajax jQuery.
In today's web development, new and new innovations have been developed to reduce errors. Therefore, this function is also an innovation when importing data via the CSV file. Since the data was checked twice and incorrect information was found, we can edit the data in the CSV file live before importing it. We all know that the CSV file format is often used to import and export data from any web application. Then, if you have created a company-level web application and have CSV file import data, this feature will reduce the workload because we can edit the CSV file data after loading the CSV file and before importing it into the database. , Therefore, this function reduces the import of incorrect data into the database. Here we have another tutorial on editing CSV file data live and after importing it into the MySQL database using PHP with Ajax jQuery.
index.php
This is the main file of this tutorial. Here you will find the HTML code and the jQuery Ajax code. In the HTML code we created a form in which we defined the file name for selecting the CSV file and the upload button for sending the CSV file to the server. And in the jQuery Ajax code, which you can see below, we wrote the jQuery code in a two-button click event.
The first click event sends the selected CSV file to the fetch.php file. To send file data to the server, we used a new FormData () object here. After the success of the Ajax request, you will receive data in JSON format. This data is converted into HTML table format and displayed on the website. With the table it will also make the import button with the ID import_data also make the import data in the MySQL table. It has an attribute like class = "student_name" in the column of the student name table and an attribute like class = "student_phone" in the column of the student's phone table. Therefore, the data in this column is retrieved from the jQuery code, and these two columns can be edited with the contenteditable attribute.
In the second jQuery click event you wrote on the "Import" button, this code is executed when you click the "Import" button. In this first step, you called up the attribute class data and saved it under the local variable in matrix format. The value of the local variable is then sent to the PHP script using the Ajax request. The Ajax request sends a request to the import.php file to import data into the database. After the data has been successfully imported, a success message is displayed on the website.
The first click event sends the selected CSV file to the fetch.php file. To send file data to the server, we used a new FormData () object here. After the success of the Ajax request, you will receive data in JSON format. This data is converted into HTML table format and displayed on the website. With the table it will also make the import button with the ID import_data also make the import data in the MySQL table. It has an attribute like class = "student_name" in the column of the student name table and an attribute like class = "student_phone" in the column of the student's phone table. Therefore, the data in this column is retrieved from the jQuery code, and these two columns can be edited with the contenteditable attribute.
In the second jQuery click event you wrote on the "Import" button, this code is executed when you click the "Import" button. In this first step, you called up the attribute class data and saved it under the local variable in matrix format. The value of the local variable is then sent to the PHP script using the Ajax request. The Ajax request sends a request to the import.php file to import data into the database. After the data has been successfully imported, a success message is displayed on the website.
<!DOCTYPE html>
<html>
<head>
<title>Live Import CSV File Data into MySQL using PHP Ajax jQuery</title>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>
<style>
.box
{
max-width:600px;
width:100%;
margin: 0 auto;;
}
</style>
</head>
<body>
<div class="container">
<br />
<h3 align="center">Live Import CSV File Data into MySQL using PHP Ajax jQuery</h3>
<br />
<form id="upload_csv" method="post" enctype="multipart/form-data">
<div class="col-md-3">
<br />
<label>Select CSV File</label>
</div>
<div class="col-md-4">
<input type="file" name="csv_file" id="csv_file" accept=".csv" style="margin-top:15px;" />
</div>
<div class="col-md-5">
<input type="submit" name="upload" id="upload" value="Upload" style="margin-top:10px;" class="btn btn-info" />
</div>
<div style="clear:both"></div>
</form>
<br />
<br />
<div id="csv_file_data"></div>
</div>
</body>
</html>
<script>
$(document).ready(function(){
$('#upload_csv').on('submit', function(event){
event.preventDefault();
$.ajax({
url:"fetch.php",
method:"POST",
data:new FormData(this),
dataType:'json',
contentType:false,
cache:false,
processData:false,
success:function(data)
{
var html = '<table class="table table-striped table-bordered">';
if(data.column)
{
html += '<tr>';
for(var count = 0; count < data.column.length; count++)
{
html += '<th>'+data.column[count]+'</th>';
}
html += '</tr>';
}
if(data.row_data)
{
for(var count = 0; count < data.row_data.length; count++)
{
html += '<tr>';
html += '<td class="student_name" contenteditable>'+data.row_data[count].student_name+'</td>';
html += '<td class="student_phone" contenteditable>'+data.row_data[count].student_phone+'</td></tr>';
}
}
html += '<table>';
html += '<div align="center"><button type="button" id="import_data" class="btn btn-success">Import</button></div>';
$('#csv_file_data').html(html);
$('#upload_csv')[0].reset();
}
})
});
$(document).on('click', '#import_data', function(){
var student_name = [];
var student_phone = [];
$('.student_name').each(function(){
student_name.push($(this).text());
});
$('.student_phone').each(function(){
student_phone.push($(this).text());
});
$.ajax({
url:"import.php",
method:"post",
data:{student_name:student_name, student_phone:student_phone},
success:function(data)
{
$('#csv_file_data').html('<div class="alert alert-success">Data Imported Successfully</div>');
}
})
});
});
</script>
fetch.php
This PHP script received Ajax's request to retrieve data from selected Ajax CSV files. Here you first opened the CSV file with the function fopen (), then after reading the first line, which shows the table column of the CSV file with the method fgetcsv (), and saved it under the local variable. To read all the data from the CSV file afterwards, you used the while cycle with the fgetcsv () method and read the data from the CSV file and saved the data under the local variable in matrix format. To send a response to Ajax's request in json format, he used the json_encode () method.
<?php
//fetch.php
if(!empty($_FILES['csv_file']['name']))
{
$file_data = fopen($_FILES['csv_file']['tmp_name'], 'r');
$column = fgetcsv($file_data);
while($row = fgetcsv($file_data))
{
$row_data[] = array(
'student_name' => $row[0],
'student_phone' => $row[1]
);
}
$output = array(
'column' => $column,
'row_data' => $row_data
);
echo json_encode($output);
}
?>
import.php
This script received Ajax's request to insert data into the mysql table. First, you checked that no Ajax was received because you didn't use the isset () function. When you have received data, connect to the database and save the data storage array under the local variable. To generate multiple data insert queries, you have a loop, and after generating multiple insert queries, you have run all queries at the same time. This way you can insert or import multiple data at the same time.
<?php
//import.php
if(isset($_POST["student_name"]))
{
$connect = new PDO("mysql:host=localhost;dbname=testing", "root", "");
$student_name = $_POST["student_name"];
$student_phone = $_POST["student_phone"];
for($count = 0; $count < count($student_name); $count++)
{
$query .= "
INSERT INTO tbl_student(student_name, student_phone)
VALUES ('".$student_name[$count]."', '".$student_phone[$count]."');
";
}
$statement = $connect->prepare($query);
$statement->execute();
}
?>
Database
--
-- Database: `testing`
--
-- --------------------------------------------------------
--
-- Table structure for table `tbl_student`
--
CREATE TABLE `tbl_student` (
`student_id` int(11) NOT NULL,
`student_name` varchar(250) NOT NULL,
`student_phone` varchar(20) NOT NULL,
`image` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Indexes for table `tbl_student`
--
ALTER TABLE `tbl_student`
ADD PRIMARY KEY (`student_id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `tbl_student`
--
ALTER TABLE `tbl_student`
MODIFY `student_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
