PHP MySQLi How To Save CSV Data To Database Table Tutorial

https://onlinewebtutorblog.com/php-mysqli-how-to-save-csv-data-to-database-table-tutorial/

PHP MySQLi How To Save CSV Data To Database Table Tutorial

June 18, 2022 by Sanjay Kumar

Table of Contents

• Create Database & Table

• Dummy Data for Application

• Application Folder Structure

• Database Configuration

• Application Programming

• Application Testing

Share this Article

Reading Time: 6 minutes 2 Views

Inside this article we will see the concept i.e PHP MySQLi How To Save CSV file to Database. Article contains classified information. It will give the complete idea of parsing a CSV file and save into PHP MySQLi database.

This tutorial will be super easy to understand and it’s steps are easier to implement in your code as well. If you will learn only reading CSV file, you can use the same concept in data seeding to database via CSV file. This is step by step tutorial in PHP about CSV file data seeding inside mysql database.

Learn More –

• PHP MySQLi Dynamic Dependent Dropdown using jQuery Ajax

• PHP MySQLi How To Call Stored Procedure Tutorial

• PHP MySQLi jQuery UI Autocomplete Database Search

• How To Generate QR Code in PHP Using Library Tutorial

Let’s get started.

Create Database & Table

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE php_applications;

Inside this database, we need to create a table.

Tables we need – students

CREATE TABLE `students` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(150) NOT NULL,
 `email` varchar(50) NOT NULL,
 `gender` enum('male','female','others') NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Dummy Data for Application

Here, you need to copy mysql query from this given file and run into sql tab of phpmyadmin. First download it and run.

Download students.csv

We will use this csv data and insert into students table. You need to put this downloaded file into your application setup.

Application Folder Structure

You need to create a folder structure to develop this application in PHP and MySQLi. Have a look the files and folders inside this application –

Create a folder with name php-mysqli-csv and create these 2 files into it.

Database Configuration

Open dbconfig.php file from folder. Add these lines of code into it.

dbconfig.php

<?php

/*

 @Author: Sanjay Kumar

 @Project: PHP & MySQLi How To Save CSV Data To Database Tutorial

 @Email: [e-mail]

 @Website: https://onlinewebtutorblog.com/

*/

​

// Database configuration

$host   = "localhost";

$dbuser = "admin";

$dbpass = "Admin@123";

$dbname = "php_applications";

​

// Create database connection

$conn = new mysqli($host, $dbuser, $dbpass, $dbname);

​

// Check connection

if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

}

​

Application Programming

Open index.php file and write this following code into it.

• 

index.php

<?php

/*

 @Author: Sanjay Kumar

 @Project: PHP & MySQLi How To Save CSV Data To Database Tutorial

 @Email: [e-mail]

 @Website: https://onlinewebtutorblog.com/

*/

​

// Include the database file

require 'dbconfig.php';

​

$students = [];

​

if (($open = fopen("students.csv", "r")) !== FALSE) {

​

    while (($data = fgetcsv($open, 1000, ",")) !== FALSE) {

        $students[] = $data;

    }

​

    fclose($open);

}

​

echo "<pre>";

print_r($students);

​

If you print the above output, you should like this –

Let’s save this array of data into database.

Here, is the complete code of index.php

index.php

<?php

/*

 @Author: Sanjay Kumar

 @Project: PHP & MySQLi How To Save CSV Data To Database Tutorial

 @Email: [e-mail]

 @Website: https://onlinewebtutorblog.com/

*/

​

// Include the database file

require 'dbconfig.php';

​

$students = [];

​

if (($open = fopen("students.csv", "r")) !== FALSE) {

​

    while (($data = fgetcsv($open, 1000, ",")) !== FALSE) {

        $students[] = $data;

    }

​

    fclose($open);

}

​

// echo "<pre>";

// print_r($students);

​

if (!empty($students) && count($students) > 0) {

​

    foreach ($students as $index => $student) {

​

        if ($index == 0) {

            continue;

        }

​

        $stmt = $conn->prepare("INSERT INTO students (name, email, gender) VALUES(?, ?, ?)");

        $stmt->bind_param("sss", $student[0], $student[1], $student[2]);

        $stmt->execute();

    }

}

​

Application Testing

Now,

URL: http://localhost/php-mysqli-csv/index.php

Download Complete Source Code

Download Source Code

We hope this article helped you to PHP MySQLi How To Save CSV Data To Database Table Tutorial in a very detailed way.

Buy Me a Coffee

Online Web Tutor invites you to try Skillshare free for 1 month! Learn CakePHP 4, Laravel APIs Development, CodeIgniter 4, Node Js, etc into a depth level. Master the Coding Skills to Become an Expert in Web Development. So, Search your favourite course and enroll now. Click here to join.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.