In most of the PHP web applications we stores data into MySQL database. We also export data in PHP or import data in PHP.
Here in this article I'm explaining how you can achieve the task of import CSV (comma-separated values) into MySQL database with PHP.
In most of the projects uploading data or reports is a common task which we do so this article is very important for you to learn storing the CSV file data into MySQL.
A CSV file stores data in tabular format that can be separated by either comma(,) or semicolon(;). So this is very easy to read every row and insert all the rows into MySQL database.
Here we have HTML form code for uploading the CSV file.
index.php
1
2
3
4
5
6
7
8
9
10
11
|
<form action="importcsv.php" method="post" name="upload_csv" enctype="multipart/form-data">
<fieldset>
<!-- Form Name -->
<legend>Import CSV</legend>
<input type="file" name="file" id="file" class="input-large">
<br><br>
<button type="submit" id="submit" name="Import">Import</button>
</fieldset>
</form>
|
Always remember if your form has POST method and you are uploading any file by the form you need to set the enctype like below code otherwise you file will not be uploaded.
1
|
enctype="multipart/form-data"
|
I have created a csv database in MySQL and inside that I have users table like below query:
1
2
3
4
5
6
7
8
9
|
CREATE TABLE `users` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`phone` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `users`
ADD PRIMARY KEY (`id`);
|
Here 'id' is the primary key which is auto incremented. We just need to import 3 columns data and that is name, email and phone.
Here is the code for MySQL database connection with PHP.
1
2
3
4
5
6
7
8
9
|
<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "csv";// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
|
Here is the PHP code to import the CSV file into MySQL.
importcsv.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
|
<?php
include_once 'connection.php';
if(isset($_POST["Import"])){
$filename=$_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
while (($getData = fgetcsv($file, 10000, ",")) !== FALSE)
{
$sql = "INSERT into users (name,email,phone)
values ('".$getData[0]."','".$getData[1]."','".$getData[2]."')";
$result = mysqli_query($conn, $sql);
if(!isset($result))
{
echo "<script type=\"text/javascript\">
alert(\"Invalid File:Please Upload CSV File.\");
window.location = \"index.php\"
</script>";
}
else {
echo "<script type=\"text/javascript\">
alert(\"CSV File has been successfully Imported.\");
window.location = \"index.php\"
</script>";
}
}
fclose($file);
}
}
|
You also can explore how to export the MySQL data into excel/CSV in PHP Here.