Skip to content

WebNinjaDeveloper.com

Programming Tutorials




Menu
  • Home
  • Youtube Channel
  • Official Blog
  • Nearby Places Finder
  • Direction Route Finder
  • Distance & Time Calculator
Menu

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

Posted on December 26, 2022

 

 

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

 

 

MySQL
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

 

 

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

 

 

 

Recent Posts

  • React.js Twitter API Tutorial to Embed Profile & Timeline, Hashtags of User in Browser Using Javascript
  • Android Java Tutorial to Change Styles & Visibility of System Bars (Top, Action & Status) Full Example
  • Android Java Project to Display & Play Audio Files From Storage inside ListView Using MediaPlayer Class
  • Android Java Project to Build MP4 Video to MP3 Audio Converter Using MediaMuxer Class
  • Android Java Project to Merge Multiple PDF Documents From Gallery Using iTextPDF Library
  • Angular
  • Bunjs
  • C#
  • Deno
  • django
  • Electronjs
  • java
  • javascript
  • Koajs
  • kotlin
  • Laravel
  • meteorjs
  • Nestjs
  • Nextjs
  • Nodejs
  • PHP
  • Python
  • React
  • ReactNative
  • Svelte
  • Tutorials
  • Vuejs




©2023 WebNinjaDeveloper.com | Design: Newspaperly WordPress Theme