PHP MySQLi How To Save CSV Data To Database Table Tutorial

Share this Article
Reading Time: 6 minutes
203 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 –

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.

Table 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.

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.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP & MySQLi How To Save CSV Data To Database Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @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.

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP & MySQLi How To Save CSV Data To Database Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @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

<?php
/*
 @Author: Sanjay Kumar
 @Project: PHP & MySQLi How To Save CSV Data To Database Tutorial
 @Email: onlinewebtutorhub@gmail.com
 @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

Back to database table, you should see inside students table –

Download Complete 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.

Was this post helpful?

Learn Web Development Courses Risk Free @ $5 only.
Web Development Courses @ $5