PHP 7 Script to Import CSV File Data inside MySQL Table Using Prepared Statements

Welcome folks today in this blog post we will be importing csv file inside mysql table using prepared statementsin 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

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;

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

<?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();
}
?>

<?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,Gautam,45,New Zealand
2,John,56,India
3,Sanjay,45,Mumbai
4,David,34,England

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