How to Import CSV File in MySQL PHP
In this tutorial, we are going to see how to import a CSV file into MySQL database using PHP. CSV stands for Comma Separated Values and contains all the data separated by commas.
We will use three files:
- The index.php file which will contain part of the user interface.
- The import_csv.php file to connect MySQL and parse the CSV data and store it in a MySQL table.
- The db_connect.php file to connect to the database.
Create a database
Create a MySQL database named ‘test’ for example. If you already have a test database, run the code below in the SQL query window of the database. As shown in the screenshot below:
CREATE TABLE IF NOT EXISTS `product` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(256) NOT NULL, `description` text NOT NULL, `price` int(255) NOT NULL, primary key (id) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Connection to the MySQL database with PHP
After creating the “product” table, we will create the file db_connect.php to establish a connection with the MySQL database.
<?php $server = "localhost"; $username = "root"; $password = ""; $db = "test"; $conn = mysqli_connect($server, $username, $password, $db); ?>
Steps to import a CSV file into a MySql database using PHP
Step 1: Create the import_csv.php file and add the below code into this file.
<?php
// Connect to database
include("db_connect.php");
if (isset($_POST["import"])) {
$fileName = $_FILES["file"]["tmp_name"];
if ($_FILES["file"]["size"] > 0) {
$file = fopen($fileName, "r");
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
$sql = "INSERT into product (id,name,description,price)
values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "','" . $column[3] . "')";
$result = mysqli_query($conn, $sql);
if (! empty($result)) {
$type = "success";
$message = "Data is imported into the database";
} else {
$type = "error";
$message = "Problem importing CSV data";
}
}
}
}
//Return to the index page
header('Location: index.php');
exit;
?>
The code below is used to create an interface to upload a CSV file.
Step 2: Create index.php file to display the CSV file. You need to add the code below to the index.php file.
<!DOCTYPE html>
<html>
<head>
<title>Import CSV File in MySQL PHP</title>
</head>
<body>
<h3>Read the article on : <a href="https://stackhowto.com/how-to-import-csv-file-in-mysql-php/" target="_blank">How to Import CSV File in MySQL PHP</a></h3>
<form enctype="multipart/form-data" action="import_csv.php" method="post">
<div class="input-row">
<label class="col-md-4 control-label">Choose a CSV file</label>
<input type="file" name="file" id="file" accept=".csv">
<br />
<br />
<button type="submit" id="submit" name="import" class="btn-submit">Import</button>
<br />
</div>
</form>
<?php
// Connect to database
include("db_connect.php");
$sql = "SELECT * FROM product";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
?>
<table>
<thead>
<tr>
<th>Product ID</th>
<th>Product Name</th>
<th>Description</th>
<th>Price</th>
</tr>
</thead>
<?php while ($row = mysqli_fetch_array($result)) { ?>
<tbody>
<tr>
<td> <?php echo $row['id']; ?> </td>
<td> <?php echo $row['name']; ?> </td>
<td> <?php echo $row['description']; ?> </td>
<td> <?php echo $row['price']; ?> </td>
</tr>
<?php } ?>
</tbody>
</table>
<?php } ?>
</body>
</html>
- Factorial Program in PHP Using Recursive Function
- Factorial in PHP Using For Loop
- How to Generate Random String in PHP
- Warning: Cannot modify header information – headers already sent
- How to send mail from localhost in PHP using WAMP server
- How to send mail from localhost in PHP using XAMPP
- How to Send an Email in PHP
- How to Validate an Email Address in PHP
- How to Check Format of Date in PHP
- How to check if a URL is valid in PHP
- How to Check Website Availability with PHP
- How to Generate Random Numbers in PHP
- How to Calculate Age from Date of Birth in PHP
- How to Extract Content Between HTML Tags in PHP
- How to check if file exists on remote server PHP
- How to get the client IP address in PHP
- How to Get IP Address of Server in PHP
- How to declare a global variable in PHP
- How to refresh a page with PHP
- How to get first day of week in PHP
- How to Change Max Size of File Upload in PHP
- How to compare two dates in PHP
- How to open a PDF file in browser with PHP
- How to Download file from URL using PHP
- How to remove an array element based on key in PHP
- How to get random value from an array in PHP
- How to calculate sum of an array in PHP
- How to remove null or empty values from an array in PHP
- How to Populate Dropdown List using Array in PHP
- How to get all the keys of an associative array in PHP
- How to get a value from an array in PHP
- How to remove a specific element from an array in PHP
- How to sort an associative array by value in PHP
- How to sort an associative array by key in PHP
- How to Check If a String Contains a Specific Word in PHP
- How to get the current date and time in PHP
- How to redirect to another page in PHP
- How to remove all spaces from a string in PHP
- Convert a date DD-MM-YYYY to YYYY-MM-DD in PHP
- Convert a date YYYY-MM-DD to DD-MM-YYYY in PHP
- How to get the current year in PHP
- How to convert a string to an integer in PHP
- How to get the first element of an array in PHP
- How to convert date to timestamp in PHP
- PHP: Compare two arrays and get the difference
- How to remove duplicates from an array in PHP
- How to sort an array in alphabetical order in PHP
- How to merge multiple arrays into one array in PHP
- How to add an element to the end of an array in PHP
- How to add an element to the beginning of an array in PHP
- How to remove the last element from an array in PHP
- How to remove the first element from an array in PHP
- How to replace part of a string in PHP
- How to count repeated words in a string in PHP
- PHP – Count Array Elements
- How to Reverse an Array in PHP
- How to check if a value exists in an array in PHP
- How to check if a key exists in an array in PHP
- How to reverse a string in PHP
- How to check if a variable is null in PHP
- How to check if a variable is empty in PHP
- How to check if a variable is undefined in PHP
- Convert HTML entities back to characters – PHP
- How to find the length of a string in PHP
- How to capitalize the first letter of a string in PHP
- PHP – Uppercase the first letter of each word in a string
- How to convert a string to lowercase in PHP
- How to compare two strings in PHP
- How to get the URL of the current page in PHP
- How to convert a string into an array in PHP
- How to extract a substring from a string in PHP
- How to count number of words in a string in PHP
- How to remove special characters from a string in PHP
- How to replace a word in a string in PHP
- How to Concatenate Two Strings in PHP
- How to count the number of characters in a string in PHP
- PHP – Remove spaces at the beginning and end of a string
- How to filter an array by value in PHP
- PHP – Get Domain Name from URL
- How to create a log file in PHP
- How to add days to a date in PHP
- PHP – Get file extension
- How to save an image from a URL in PHP
- How to Edit PHP ini File in Ubuntu Terminal
- How to increase the execution time of a PHP script
- How to enable cURL in PHP WAMP, XAMPP, and Ubuntu
- Convert Array to JSON in PHP
- PHP – Read JSON file
- How to display HTML tags as plain text using PHP
- PHP – Create JSON file
- How to convert result of MySQL query in JSON format
- PHP – Read CSV File into an Array
- How to convert an object to associative array in PHP
- How to extract or unzip a folder on FTP server in PHP
- How to Import CSV File in MySQL PHP
- Get Data From a Database Without Refreshing the Browser Using Ajax
- How to connect to Oracle database with PHP
- Connect to a PostgreSQL database with PHP PDO
- PHP – Convert Multidimensional Array to XML file
- What is PHP and how can I learn it?
- Hello World in PHP
- Comments in PHP
- How to Define a Variable in PHP
- PHP global variable with Example
- PHP Variable Scope
- PHP – Static Variables
- Type casting in PHP
- PHP Type Juggling
- Variable variables in PHP
- How to echo an array in PHP?
- How to Convert Decimal to Binary in PHP?
- How to Convert Binary to Decimal in PHP?
- How to assign multiple variables at once in PHP
- How to extract $_GET / $_POST parameters in PHP
- How to use $_GET in PHP?
- How to get a variable name as a string in PHP?
- How to print all defined variables and values in PHP
- PHP References: When to Use Them, and How They Work
- List of Superglobal Variables in PHP
- How to return a variable by name from a function in PHP
- How to delete a variable in PHP
- What is the difference between == and === in PHP
- How to check if a variable is NULL in PHP?
- How to check if an object is an instance of a specific class in PHP?
- How to check if a variable is a Boolean in PHP?
- How to check if a variable is a String in PHP?
- How to check if a variable is an Integer in PHP?
- How to check if a variable is a float in PHP?
- How to check if a variable is an array in PHP?
- How to check if a variable is undefined in PHP?
- How do you determine the data type of a variable in PHP?
- Difference between Include and Require in PHP
- How to combine HTML and PHP?
- How to add a new line within echo in PHP?
- How to display PHP variable values with echo, print_r, and var_dump
- The 6 best PHP frameworks in 2021
- PHP MCQ – Multiple Choice Questions and Answers – Basics – Part 1
- PHP MCQ – Multiple Choice Questions and Answers – Basics – Part 2
- PHP MCQ – Multiple Choice Questions and Answers – Basics – Part 3
- PHP Questions and Answers – Functions – Part 1
- PHP Questions and Answers – Functions – Part 2
- PHP Questions and Answers – Arrays – Part 1
- PHP Questions and Answers – Arrays – Part 2
- PHP Questions and Answers – Arrays – Part 3
- PHP Questions and Answers – Arrays – Part 4
- PHP Questions and Answers – Object-Oriented OOP – Part 1
- PHP Questions and Answers – Object-Oriented OOP – Part 2
- PHP Questions and Answers – Object-Oriented OOP – Part 3
- PHP Questions and Answers – String – Part 1
- PHP Questions and Answers – String – Part 2
- PHP Questions and Answers – Regular Expressions
- PHP Questions and Answers – Exception Handling
