Site icon WDB24

How to Upload CSV and Save in Mysql database using PHP

Ahsan Zameer
php featured image

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

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

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

MySQL
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:

PHP Code

PHP
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";
}
}
?>
 

Exit mobile version