Friday, 6 July 2018

How to Upload CSV and Save in Mysql database using PHP

How to Upload CSV and Save in Mysql database using PHP

New to php and don’t know how to upload and save CSV file in mysql database? Don’t worry in this post I am going to discuss with you how to do that.
Before I jump into the code let’s see what we are going to do.

Steps: How to upload CSV and save in Mysql database using PHP

  • Create a html form with input file field.
  • Add some CSS Style
  • Create connection to database.
  • Create table in which we will save CSV data.

HTML Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
<div class="container">
<div>
<?php
if(isset($_GET['file']) && $_GET['file'] !='')
{
echo "<div class='success-msg'> Csv Uploaded and Saved in database</div>";
}
if(isset($errorMsg) && $errorMsg !='')
{
echo "<div class='error-msg'>".$errorMsg."</div>";
unset();
}
?>
<h1>Upload Csv</h1>
<form action="" method="post" enctype="multipart/form-data">
<input type="file" name="uploadCsv" class="upload">
<button type="submit" name="submitFile">Submit</button>
</form>
</div>
</div>
 
When we want to add file field in the form then we must add enctype=”multipart/form-data”. Enctype attribute identifies how the form data should be encoded when data is going to server and it can be used only if the form method is POST. $_GET[‘file’] will use to print success message and $errorMsg will print error message if file will not a CSV.

CSS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
 
body{
background:#eeeeee;
}
 
.container{
width:320px;
margin:100px auto;
border:1px solid #a00101;
padding:10px;
}
 
h1{
font-family:verdana;
font-weight: 500;
font-size:26px;
line-height:28px;
padding:0px;
margin:0px 0px 10px 0px;
}
 
input[type=file]{
background:#ffffff;
height:32px;
}
 
button{
border:1px solid #a00101;
background:#a00101;
height:30px;
color:#ffffff;
}
 
.author{
position:absolute;
right:20px;
top:0px;
}
 
.success-msg{
background:#a1f2a7;
color:#3e9244;
border: 1px solid #3e9244;
padding:4px;
}
 
.error-msg{
background:##a00101;
color:#ffffff;
border: 1px solid #a00101;
padding:4px;
}
 

Mysql Table Structure

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE `students_grade` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_name` VARCHAR(90) NOT NULL,
`student_grade` CHAR(2) NOT NULL,
`student_marks` INT(3) NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;
 
I am using some php built-in function to accomplish CSV Upload task here they are:
  • Pathinfo (path,options): returns path information in array form. It takes 2 parameters. First is file path which is required and second is options parameter which is optional.
  • move_uploaded_file(file,newloc): moves uploaded file into new location. It also takes 2 required parameters. First parameter specifies the file to be moved and second specifies the new location of the current uploaded file.
  • fopen(filename,mode,include_path,context): opens a file or URL. If file or URL not exist it returns FALSE. Fopen() function takes 4 parameter filename and mode are required parameter, include_path and context are optional parameter. If you want to discover more about fopen then visit php.net fopen().
  • fgetcsv(file,length,separator,enclosure): parses a line from an open file and returns array of each row in CSV file. fgetcsv() takes 4 paramter. First parameter is required and rests of three are optional.

PHP Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
 
<?php
/*database connection*/
$host = "localhost";
$user = "root";
$pass = "";
$db   = "my_website";
 
$conn = mysqli_connect($host, $user, $pass,$db);
if(!$conn)
{
die("Error: ".mysqli_connect_error());
}
 
/*end database connection*/
 
//if form is submit
if(isset($_POST['submitFile']))
{
$uploadDirectory = "uploads/";
$createUploadPath = $uploadDirectory.$_FILES['uploadCsv']['name'];
$csvFileType = pathinfo($createUploadPath, PATHINFO_EXTENSION);
if ($csvFileType == 'csv')
{
if (move_uploaded_file($_FILES['uploadCsv']['tmp_name'], $createUploadPath))
{
$records = fopen($createUploadPath, 'r');
$rowCounter = 0;
while (($data = fgetcsv($records)) !== FALSE)
{
if($rowCounter > 0)
{
$studentName = $data[0];
$studentMarks = $data[1];
$studentGrade = $data[2];
$qry = "insert into students_grade (student_name, student_grade, student_marks) values('".$studentName."', '".$studentGrade."', '".$studentMarks."')";
$rs = mysqli_query($conn,$qry);
}
$rowCounter++;
}
header("location:index.php?file=success");
exit();
}
}
else
{
$errorMsg = "Please upload Csv file";
}
}
?>

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)