Programmer often need to insert data from CSV file to Mysql Database. This can be simply done using the below sample code. To import CSV File to MYSQL Database, first we will connect to database, open up a csv file, get data from each row and insert them to mysql database.
In first stage, we will only connect to the database. In second stage we will insert the Excel data to database. Let look at my database connection file named as dbconnection.php
Database Connection
The above code is just a connection to database. The actual insertion code lies in below code. For simplicity, I have commented main lines of codes. Here is my index.php file for inserting data from CSV file to Mysl Database.
CSV to MYSQL
Lines number 4, 5 and 6 is for enabling the error message if any. Line 9 calls the dbconnection.php file for database connection. Line 12 with fopen(countries.csv) opens a csv file with read access only. You can find more about the fopen at php.net.
$flag is used to escape the first row of CSV file because it contains title but not the data. fgetcsv is used to fetch the data from csv file. It will return an array. So, we are accessing each column data with array as $data[0] and $data[1]. Line 29 and 33 makes query to insert to database and execute simultaneously. IGNORE in sql statement is for preventing duplicate entry of data. During the insertion process, we are echoing messages. You can download the CSV file by clicking here
Strong Recomendation: Please use either PDO or MYSQLI. You can find resources at www.php.net
