PHP GuruPHP, MySQL, JavaScript, AJAX, MVC, Codeigniter
  • Technology
    • PHP
    • PHP Frameworks
    • MySql
    • Javascript
    • AJAX
    • React Js
    • Node JS
    • ChatGPT
  • JSON Viewer
  • XML Viewer

Import CSV data to MySQL Using PHP

Date July 27, 2013 Author By PravinS Category MySql, PHP

Just like my previous post on Export MySQL data to CSV file Using PHP, it is vice versa process i.e. to Import CSV data to MySQL Using PHP

So here is simple CSV to MySQL import class written in PHP.

First create "csvimport" class and define required variables
class csvimport
{
	var $separator = ',';
	var $field_names = false;
	var $dbhost = '';	// mysql database server
	var $dbuser = '';		// database username
	var $dbpass = '';		// database password
	var $dbname = '';		// database name
	var $data = '';
	var $error = '';
	var $result = false;
}

Next add constructor function in class with two parameters database table name and CSV filename and in constructor function write MySQL database connection. Define database information in above variable list.

function __construct($tablename,$filename)
{
	$Conn = mysql_connect($this->dbhost, $this->dbuser, $this->dbpass) 
                 or $this->error_msg("Error: Invalid MySQL Server Information");
	if (!$Conn)
		$this->error_msg("Error: Invalid MySQL Server Information");
	$DB_select = mysql_select_db($this->dbname, $Conn);
	if (!$DB_select)
		$this->error_msg("Error: Invalid MySQL Database");
	if ($this->temp_path == '')
		$this->temp_path = sys_get_temp_dir(); 
	$this->filename = $filename;
	$this->sql = $sql;			
	$this->import();
}

Here are some required functions/methods which are used while importing CSV data in MySQL. Add this functions to above class.

//sql execution function which will return resource id
function execute($sql)
{
	if ($sql!="")
	{
		$result = mysql_query($sql) or $this->error_msg("Error: Check MySQL Query($sql)");
		if ($result)
			return $result;
		else
			return false;
	}
}

//function to check number of record of resource id
function count_check($result)
{
	if ($result)
	{
		if (mysql_num_rows($result) > 0)
			return true;
		else
			return false;					
	}
}		

//function to fetch mysql data from resource id in associative array 
function recordset($result)
{
	if ($result)
	{
		while($row = mysql_fetch_assoc($result))
			$data[] = $row;
	}
	return $data;
}

//function to display error message
function error_msg($msg)
{
	if ($msg != '')
		die($msg);
	else
		return true;
}

And finally the import function to get CSV content and insert it into mysql table

function import()
{
	if (file_exists($this->filename))
	{
		$this->data = file_get_contents($this->filename);
		if ($this->data != '')
		{
			$lines = explode("\n", $this->data);
			if (is_array($lines) && count($lines) > 0)
			{
				$insert_string = '';
				$data_array = array();
				if ($this->field_names == true)
					$start_ind = 0;
				else
					$start_ind = 1;	
				
				for($i=$start_ind;$i<count($lines);$i++)
				{
					if ($lines[$i] != '')
					{
						$data_array = explode($this->separator, $lines[$i]);
						if (is_array($data_array) && count($data_array) > 0)
						{
							$t_str = '';
							foreach($data_array as $data_row)
							{
								$t_str .= "'".$data_row."',";
							}
						}
						$insert_string .= '('.trim($t_str,",").'),';
					}
				}
				$insert_string = trim($insert_string,",");
				if ($insert_string != '')
				{
					$insert_query = "INSERT INTO ".$this->tablename." VALUES $insert_string";
					$ins = $this->execute($insert_query);
					if ($ins)
						$this->error_msg("Data Inserted Successfully...");
					else
						$this->error_msg("Problem While Inserting Data...");	
				}
			}					
		}
		else
		{
			$this->error_msg("Error: No Data In CSV file");
		}
		
	}
	else
	{
		$this->error_msg("Error: Cannot open file(".$this->filename.")");
	}
}

As we have created csvimport class to import CSV data in MySQL table, now below is the code by which we read csv file and insert data in MySQL table.

$filename = 'test.csv'; // CSV file to be imported to MySQL
$tablename = ""; // MySQL table name in which data to be inserted
new csvimport($tablename,$filename);

Tags: csv to mysql, import csv file in mysql, import csv file into mysql using php, import csv to mysql

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Comparison between CodeIgniter and Laravel
  • SOAP Client In CodeIgniter using NuSOAP PHP Toolkit
  • Pagination using PHP and MySQL – 1
  • Pagination using PHP and MySQL – 2
  • Upload Files using PHP CURL
  • Search Value in Multidimensional Array Using PHP
  • SOAP Server In CodeIgniter using NuSOAP PHP Toolkit
  • Simple JavaScript/CSS Lightbox
  • Import CSV data to MySQL Using PHP
  • Export MySQL data to CSV file Using PHP

Pages

  • ChatGPT
  • Node JS
  • PHP
  • React Js

Tag Cloud

A-Z alphabets in php AJAX alphabets using php array search in php Asynchronous JavaScript and XML CI NUSOAP library Client Side codeigniter codeigniter nusoap library coparison chart Database file upload using curl html to pdf import csv to mysql Javascript javascript css lightbox laravel lightbox MySQL nusoap client in codeigniter nusoap in codeigniter nusoap integration with codeigniter Open Source pagination in php and mysql pagination in php mysql pasination using php pdf pdf generation in codeigniter PHP php and mysql pagination php curl file upload php file upload using curl php mysql pagination php pagination php pagination function RDBMS Scripting Language search in multidimensional array Server Side language soap client in codeigniter soap in codeigniter soap server in codeigniter soap server using nusoap in codeigniter SQL TCPDF with CodeIgniter

Categories

  • AJAX
  • Javascript
  • MySql
  • PHP
    • PHP Frameworks

Recent Posts

  • Comparison between CodeIgniter and Laravel
  • SOAP Client In CodeIgniter using NuSOAP PHP Toolkit
  • Pagination using PHP and MySQL – 1
  • Pagination using PHP and MySQL – 2
  • Upload Files using PHP CURL
  • Search Value in Multidimensional Array Using PHP
  • SOAP Server In CodeIgniter using NuSOAP PHP Toolkit
  • Simple JavaScript/CSS Lightbox
  • Import CSV data to MySQL Using PHP
  • Export MySQL data to CSV file Using PHP

Tag Cloud

A-Z alphabets in php AJAX alphabets using php array search in php Asynchronous JavaScript and XML CI NUSOAP library Client Side codeigniter codeigniter nusoap library coparison chart Database file upload using curl html to pdf import csv to mysql Javascript javascript css lightbox laravel lightbox MySQL nusoap client in codeigniter nusoap in codeigniter nusoap integration with codeigniter Open Source pagination in php and mysql pagination in php mysql pasination using php pdf pdf generation in codeigniter PHP php and mysql pagination php curl file upload php file upload using curl php mysql pagination php pagination php pagination function RDBMS Scripting Language search in multidimensional array Server Side language soap client in codeigniter soap in codeigniter soap server in codeigniter soap server using nusoap in codeigniter SQL TCPDF with CodeIgniter

Advertisements

Categories

  • AJAX
  • Javascript
  • MySql
  • PHP
    • PHP Frameworks
  • Theme created by PWT. Powered by WordPress.org

Copyright © 2014 PHP Guru