Welcome folks today in this blog post we will be importing csv file
inside mysql
table using prepared statements
in php. All the full source code of the application will be shown below.
Get Started
In order to get started you need to create the users
table by copy paste the below sql
code inside the phpmyadmin
section
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `users` ( `id` int(11) NOT NULL, `Name` varchar(255) NOT NULL, `Age` int(11) NOT NULL, `Country` varchar(255) NOT NULL ); ALTER TABLE `users` ADD PRIMARY KEY (`id`); ALTER TABLE `users` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; |
Now basically if you execute this code then it will create the users
table which will have the structure as shown below
As you can see the ID
field is auto-incremented and is the primary
key of the table and then we have the columns for storing the name,age
and country
of the user.
Now make a index.php
file and copy paste the below code
index.php
1 2 3 4 5 6 7 8 9 10 11 |
<?php $mysqli = new mysqli("localhost", "root", "", "exceldb"); $csvFilePath = "file.csv"; $file = fopen($csvFilePath, "r"); while (($row = fgetcsv($file)) !== FALSE) { $stmt = $mysqli->prepare("INSERT INTO users (Name,Age,Country) VALUES (?, ?, ?)"); $stmt->bind_param("sss", $row[1], $row[2], $row[3]); $stmt->execute(); } ?> |
As you can see we are making the connection to database
using the mysqli_connect()
method and then we are processing the input
csv file which is file.csv
and then we are opening the file using fopen()
method and then we are using the while
loop to get the contents of the csv file using fgetcsv()
method. And after that we are using the prepared
statements to insert the data from the csv to mysql table.
Now you can just create a sample csv
file called file.csv
into the root directory and copy paste the records
file.csv
1 2 3 4 |
1,Gautam,45,New Zealand 2,John,56,India 3,Sanjay,45,Mumbai 4,David,34,England |
Now if you execute the php
script inside the browser you will see the data
will be inserted inside the mysql
table as shown below