How to Import CSV Into MySQL Database Using PHP

Being a PHP developer you would have been consulted Data entry person for manual uploading big CSV files into your MySQL database, he may take 2-3 days to entry the data, to be a developer it’s your responsibility to reduce the man work, fortunately PHP support easy way to upload your big excel files into your database within one click .Here am going to share you the code.

Let’s Get Started

1) Copy below class and save as parseCSV.php

This is main function to parse csv into PHP array format

<?php
class parseCSV {
 
 var $heading = true;
 var $fields = array();
 var $sort_by = null;
 var $sort_reverse = false;
 var $sort_type = null;
 var $delimiter = ',';
 var $enclosure = '"';
 var $conditions = null;
 var $offset = null;
 var $limit = null;
 var $auto_depth = 15;
 var $auto_non_chars = "a-zA-Z0-9\n\r";
 var $auto_preferred = ",;\t.:|";
 var $convert_encoding = false;
 var $input_encoding = 'ISO-8859-1';
 var $output_encoding = 'ISO-8859-1';
 var $linefeed = "\r\n";
 var $output_delimiter = ',';
 var $output_filename = 'data.csv';
 var $keep_file_data = false;
 var $file;
 var $file_data;
 var $error = 0;
 var $error_info = array();
 var $titles = array();
 var $data = array();
 
 function parseCSV ($input = null, $offset = null, $limit = null, $conditions = null) {
 if ( $offset !== null ) $this->offset = $offset;
 if ( $limit !== null ) $this->limit = $limit;
 if ( count($conditions) > 0 ) $this->conditions = $conditions;
 if ( !empty($input) ) $this->parse($input);
 }
 

 function parse ($input = null, $offset = null, $limit = null, $conditions = null) {
 if ( $input === null ) $input = $this->file;
 if ( !empty($input) ) {
 if ( $offset !== null ) $this->offset = $offset;
 if ( $limit !== null ) $this->limit = $limit;
 if ( count($conditions) > 0 ) $this->conditions = $conditions;
 if ( is_readable($input) ) {
 $this->data = $this->parse_file($input);
 } else {
 $this->file_data = &$input;
 $this->data = $this->parse_string();
 }
 if ( $this->data === false ) return false;
 }
 return true;
 }
 
 function save ($file = null, $data = array(), $append = false, $fields = array()) {
 if ( empty($file) ) $file = &$this->file;
 $mode = ( $append ) ? 'at' : 'wt' ;
 $is_php = ( preg_match('/\.php$/i', $file) ) ? true : false ;
 return $this->_wfile($file, $this->unparse($data, $fields, $append, $is_php), $mode);
 }

 function output ($filename = null, $data = array(), $fields = array(), $delimiter = null) {
 if ( empty($filename) ) $filename = $this->output_filename;
 if ( $delimiter === null ) $delimiter = $this->output_delimiter;
 $data = $this->unparse($data, $fields, null, null, $delimiter);
 if ( $filename !== null ) {
 header('Content-type: application/csv');
 header('Content-Disposition: attachment; filename="'.$filename.'"');
 echo $data;
 }
 return $data;
 }

 function encoding ($input = null, $output = null) {
 $this->convert_encoding = true;
 if ( $input !== null ) $this->input_encoding = $input;
 if ( $output !== null ) $this->output_encoding = $output;
 }
 
 function auto ($file = null, $parse = true, $search_depth = null, $preferred = null, $enclosure = null) {
 
 if ( $file === null ) $file = $this->file;
 if ( empty($search_depth) ) $search_depth = $this->auto_depth;
 if ( $enclosure === null ) $enclosure = $this->enclosure;
 
 if ( $preferred === null ) $preferred = $this->auto_preferred;
 
 if ( empty($this->file_data) ) {
 if ( $this->_check_data($file) ) {
 $data = &$this->file_data;
 } else return false;
 } else {
 $data = &$this->file_data;
 }
 
 $chars = array();
 $strlen = strlen($data);
 $enclosed = false;
 $n = 1;
 $to_end = true;
 
 
 for ( $i=0; $i < $strlen; $i++ ) {
 $ch = $data{$i};
 $nch = ( isset($data{$i+1}) ) ? $data{$i+1} : false ;
 $pch = ( isset($data{$i-1}) ) ? $data{$i-1} : false ;
 
 
 if ( $ch == $enclosure ) {
 if ( !$enclosed || $nch != $enclosure ) {
 $enclosed = ( $enclosed ) ? false : true ;
 } elseif ( $enclosed ) {
 $i++;
 }
 
 
 } elseif ( ($ch == "\n" && $pch != "\r" || $ch == "\r") && !$enclosed ) {
 if ( $n >= $search_depth ) {
 $strlen = 0;
 $to_end = false;
 } else {
 $n++;
 }
 
 
 } elseif (!$enclosed) {
 if ( !preg_match('/['.preg_quote($this->auto_non_chars, '/').']/i', $ch) ) {
 if ( !isset($chars[$ch][$n]) ) {
 $chars[$ch][$n] = 1;
 } else {
 $chars[$ch][$n]++;
 }
 }
 }
 }
 
 
 $depth = ( $to_end ) ? $n-1 : $n ;
 $filtered = array();
 foreach( $chars as $char => $value ) {
 if ( $match = $this->_check_count($char, $value, $depth, $preferred) ) {
 $filtered[$match] = $char;
 }
 }
 
 
 ksort($filtered);
 $this->delimiter = reset($filtered);
 
 
 if ( $parse ) $this->data = $this->parse_string();
 
 return $this->delimiter;
 
 }
 

 function parse_file ($file = null) {
 if ( $file === null ) $file = $this->file;
 if ( empty($this->file_data) ) $this->load_data($file);
 return ( !empty($this->file_data) ) ? $this->parse_string() : false ;
 }
 
 
 function parse_string ($data = null) {
 if ( empty($data) ) {
 if ( $this->_check_data() ) {
 $data = &$this->file_data;
 } else return false;
 }
 
 $white_spaces = str_replace($this->delimiter, '', " \t\x0B\0");
 
 $rows = array();
 $row = array();
 $row_count = 0;
 $current = '';
 $head = ( !empty($this->fields) ) ? $this->fields : array() ;
 $col = 0;
 $enclosed = false;
 $was_enclosed = false;
 $strlen = strlen($data);
 
 
 for ( $i=0; $i < $strlen; $i++ ) {
 $ch = $data{$i};
 $nch = ( isset($data{$i+1}) ) ? $data{$i+1} : false ;
 $pch = ( isset($data{$i-1}) ) ? $data{$i-1} : false ;
 
 
 if ( $ch == $this->enclosure ) {
 if ( !$enclosed ) {
 if ( ltrim($current, $white_spaces) == '' ) {
 $enclosed = true;
 $was_enclosed = true;
 } else {
 $this->error = 2;
 $error_row = count($rows) + 1;
 $error_col = $col + 1;
 if ( !isset($this->error_info[$error_row.'-'.$error_col]) ) {
 $this->error_info[$error_row.'-'.$error_col] = array(
 'type' => 2,
 'info' => 'Syntax error found on row '.$error_row.'. Non-enclosed fields can not contain double-quotes.',
 'row' => $error_row,
 'field' => $error_col,
 'field_name' => (!empty($head[$col])) ? $head[$col] : null,
 );
 }
 $current .= $ch;
 }
 } elseif ($nch == $this->enclosure) {
 $current .= $ch;
 $i++;
 } elseif ( $nch != $this->delimiter && $nch != "\r" && $nch != "\n" ) {
 for ( $x=($i+1); isset($data{$x}) && ltrim($data{$x}, $white_spaces) == ''; $x++ ) {}
 if ( $data{$x} == $this->delimiter ) {
 $enclosed = false;
 $i = $x;
 } else {
 if ( $this->error < 1 ) {
 $this->error = 1;
 }
 $error_row = count($rows) + 1;
 $error_col = $col + 1;
 if ( !isset($this->error_info[$error_row.'-'.$error_col]) ) {
 $this->error_info[$error_row.'-'.$error_col] = array(
 'type' => 1,
 'info' =>
 'Syntax error found on row '.(count($rows) + 1).'. '.
 'A single double-quote was found within an enclosed string. '.
 'Enclosed double-quotes must be escaped with a second double-quote.',
 'row' => count($rows) + 1,
 'field' => $col + 1,
 'field_name' => (!empty($head[$col])) ? $head[$col] : null,
 );
 }
 $current .= $ch;
 $enclosed = false;
 }
 } else {
 $enclosed = false;
 }
 
 
 } elseif ( ($ch == $this->delimiter || $ch == "\n" || $ch == "\r") && !$enclosed ) {
 $key = ( !empty($head[$col]) ) ? $head[$col] : $col ;
 $row[$key] = ( $was_enclosed ) ? $current : trim($current) ;
 $current = '';
 $was_enclosed = false;
 $col++;
 
 
 if ( $ch == "\n" || $ch == "\r" ) {
 if ( $this->_validate_offset($row_count) && $this->_validate_row_conditions($row, $this->conditions) ) {
 if ( $this->heading && empty($head) ) {
 $head = $row;
 } elseif ( empty($this->fields) || (!empty($this->fields) && (($this->heading && $row_count > 0) || !$this->heading)) ) {
 if ( !empty($this->sort_by) && !empty($row[$this->sort_by]) ) {
 if ( isset($rows[$row[$this->sort_by]]) ) {
 $rows[$row[$this->sort_by].'_0'] = &$rows[$row[$this->sort_by]];
 unset($rows[$row[$this->sort_by]]);
 for ( $sn=1; isset($rows[$row[$this->sort_by].'_'.$sn]); $sn++ ) {}
 $rows[$row[$this->sort_by].'_'.$sn] = $row;
 } else $rows[$row[$this->sort_by]] = $row;
 } else $rows[] = $row;
 }
 }
 $row = array();
 $col = 0;
 $row_count++;
 if ( $this->sort_by === null && $this->limit !== null && count($rows) == $this->limit ) {
 $i = $strlen;
 }
 if ( $ch == "\r" && $nch == "\n" ) $i++;
 }
 
 
 } else {
 $current .= $ch;
 }
 }
 $this->titles = $head;
 if ( !empty($this->sort_by) ) {
 $sort_type = SORT_REGULAR;
 if ( $this->sort_type == 'numeric' ) {
 $sort_type = SORT_NUMERIC;
 } elseif ( $this->sort_type == 'string' ) {
 $sort_type = SORT_STRING;
 }
 ( $this->sort_reverse ) ? krsort($rows, $sort_type) : ksort($rows, $sort_type) ;
 if ( $this->offset !== null || $this->limit !== null ) {
 $rows = array_slice($rows, ($this->offset === null ? 0 : $this->offset) , $this->limit, true);
 }
 }
 if ( !$this->keep_file_data ) {
 $this->file_data = null;
 }
 return $rows;
 }
 

 function unparse ( $data = array(), $fields = array(), $append = false , $is_php = false, $delimiter = null) {
 if ( !is_array($data) || empty($data) ) $data = &$this->data;
 if ( !is_array($fields) || empty($fields) ) $fields = &$this->titles;
 if ( $delimiter === null ) $delimiter = $this->delimiter;
 
 $string = ( $is_php ) ? "<?php header('Status: 403'); die(' '); ?>".$this->linefeed : '' ;
 $entry = array();
 
 
 if ( $this->heading && !$append && !empty($fields) ) {
 foreach( $fields as $key => $value ) {
 $entry[] = $this->_enclose_value($value);
 }
 $string .= implode($delimiter, $entry).$this->linefeed;
 $entry = array();
 }
 
 
 foreach( $data as $key => $row ) {
 foreach( $row as $field => $value ) {
 $entry[] = $this->_enclose_value($value);
 }
 $string .= implode($delimiter, $entry).$this->linefeed;
 $entry = array();
 }
 
 return $string;
 }
 

 function load_data ($input = null) {
 $data = null;
 $file = null;
 if ( $input === null ) {
 $file = $this->file;
 } elseif ( file_exists($input) ) {
 $file = $input;
 } else {
 $data = $input;
 }
 if ( !empty($data) || $data = $this->_rfile($file) ) {
 if ( $this->file != $file ) $this->file = $file;
 if ( preg_match('/\.php$/i', $file) && preg_match('/<\?.*?\?>(.*)/ims', $data, $strip) ) {
 $data = ltrim($strip[1]);
 }
 if ( $this->convert_encoding ) $data = iconv($this->input_encoding, $this->output_encoding, $data);
 if ( substr($data, -1) != "\n" ) $data .= "\n";
 $this->file_data = &$data;
 return true;
 }
 return false;
 }
 
 
 

 function _validate_row_conditions ($row = array(), $conditions = null) {
 if ( !empty($row) ) {
 if ( !empty($conditions) ) {
 $conditions = (strpos($conditions, ' OR ') !== false) ? explode(' OR ', $conditions) : array($conditions) ;
 $or = '';
 foreach( $conditions as $key => $value ) {
 if ( strpos($value, ' AND ') !== false ) {
 $value = explode(' AND ', $value);
 $and = '';
 foreach( $value as $k => $v ) {
 $and .= $this->_validate_row_condition($row, $v);
 }
 $or .= (strpos($and, '0') !== false) ? '0' : '1' ;
 } else {
 $or .= $this->_validate_row_condition($row, $value);
 }
 }
 return (strpos($or, '1') !== false) ? true : false ;
 }
 return true;
 }
 return false;
 }
 
 
 * Validate a row against a single condition
 * @param row array with values from a row
 * @param condition specified condition that the row must match 
 * @return true of false
 */
 function _validate_row_condition ($row, $condition) {
 $operators = array(
 '=', 'equals', 'is',
 '!=', 'is not',
 '<', 'is less than',
 '>', 'is greater than',
 '<=', 'is less than or equals',
 '>=', 'is greater than or equals',
 'contains',
 'does not contain',
 );
 $operators_regex = array();
 foreach( $operators as $value ) {
 $operators_regex[] = preg_quote($value, '/');
 }
 $operators_regex = implode('|', $operators_regex);
 if ( preg_match('/^(.+) ('.$operators_regex.') (.+)$/i', trim($condition), $capture) ) {
 $field = $capture[1];
 $op = $capture[2];
 $value = $capture[3];
 if ( preg_match('/^([\'\"]{1})(.*)([\'\"]{1})$/i', $value, $capture) ) {
 if ( $capture[1] == $capture[3] ) {
 $value = $capture[2];
 $value = str_replace("\\n", "\n", $value);
 $value = str_replace("\\r", "\r", $value);
 $value = str_replace("\\t", "\t", $value);
 $value = stripslashes($value);
 }
 }
 if ( array_key_exists($field, $row) ) {
 if ( ($op == '=' || $op == 'equals' || $op == 'is') && $row[$field] == $value ) {
 return '1';
 } elseif ( ($op == '!=' || $op == 'is not') && $row[$field] != $value ) {
 return '1';
 } elseif ( ($op == '<' || $op == 'is less than' ) && $row[$field] < $value ) {
 return '1';
 } elseif ( ($op == '>' || $op == 'is greater than') && $row[$field] > $value ) {
 return '1';
 } elseif ( ($op == '<=' || $op == 'is less than or equals' ) && $row[$field] <= $value ) {
 return '1';
 } elseif ( ($op == '>=' || $op == 'is greater than or equals') && $row[$field] >= $value ) {
 return '1';
 } elseif ( $op == 'contains' && preg_match('/'.preg_quote($value, '/').'/i', $row[$field]) ) {
 return '1';
 } elseif ( $op == 'does not contain' && !preg_match('/'.preg_quote($value, '/').'/i', $row[$field]) ) {
 return '1';
 } else {
 return '0';
 }
 }
 }
 return '1';
 }
 
 
 function _validate_offset ($current_row) {
 if ( $this->sort_by === null && $this->offset !== null && $current_row < $this->offset ) return false;
 return true;
 }
 

 function _enclose_value ($value = null) {
 if ( $value !== null && $value != '' ) {
 $delimiter = preg_quote($this->delimiter, '/');
 $enclosure = preg_quote($this->enclosure, '/');
 if ( preg_match("/".$delimiter."|".$enclosure."|\n|\r/i", $value) || ($value{0} == ' ' || substr($value, -1) == ' ') ) {
 $value = str_replace($this->enclosure, $this->enclosure.$this->enclosure, $value);
 $value = $this->enclosure.$value.$this->enclosure;
 }
 }
 return $value;
 }
 

 function _check_data ($file = null) {
 if ( empty($this->file_data) ) {
 if ( $file === null ) $file = $this->file;
 return $this->load_data($file);
 }
 return true;
 }
 
 
 function _check_count ($char, $array, $depth, $preferred) {
 if ( $depth == count($array) ) {
 $first = null;
 $equal = null;
 $almost = false;
 foreach( $array as $key => $value ) {
 if ( $first == null ) {
 $first = $value;
 } elseif ( $value == $first && $equal !== false) {
 $equal = true;
 } elseif ( $value == $first+1 && $equal !== false ) {
 $equal = true;
 $almost = true;
 } else {
 $equal = false;
 }
 }
 if ( $equal ) {
 $match = ( $almost ) ? 2 : 1 ;
 $pref = strpos($preferred, $char);
 $pref = ( $pref !== false ) ? str_pad($pref, 3, '0', STR_PAD_LEFT) : '999' ;
 return $pref.$match.'.'.(99999 - str_pad($first, 5, '0', STR_PAD_LEFT));
 } else return false;
 }
 }
 
 
 function _rfile ($file = null) {
 if ( is_readable($file) ) {
 if ( !($fh = fopen($file, 'r')) ) return false;
 $data = fread($fh, filesize($file));
 fclose($fh);
 return $data;
 }
 return false;
 }


 function _wfile ($file, $string = '', $mode = 'wb', $lock = 2) {
 if ( $fp = fopen($file, $mode) ) {
 flock($fp, $lock);
 $re = fwrite($fp, $string);
 $re2 = fclose($fp);
 if ( $re != false && $re2 != false ) return true;
 }
 return false;
 }
 
}
?>

2) Create a Page Name csv_upload.php

include("parseCSV.php");
if($_FILES['customer_details']['name']!='') {
	
	
	$csv_report	= $_FILES['customer_details']['name'];
	$delimiter = ',';
	
		if (preg_match('/\\.(csv)$/i', $csv_report) != 1)

		{

			header("Location: csv_upload.php?err=Please upload a CSV-report with the extension.csv");
			exit;

		}
		else { 
			$csv = new parseCSV();
			$csv->delimiter = $delimiter;
			$separator = $csv->delimiter;
			$csv->parse($_FILES['customer_details']['tmp_name']);
			
			if (!isset($separator) || $separator == "")

				{

				echo "error";
				exit();

				}
			
			
			foreach ($csv->data as $key => $row) {
				print_r($row);
                                // Here You can change accordingly
                               /*
                                $event_name=$row['event_name'];
				
				$name=$row['name'];

				$email=$row['email'];

				$mobile=$row['mobile'];
				$location=$row['location'];
				$birthday=$row['birthday'];
			
				$anniversary=$row['anniversary'];
				$customer_id = '';
				$event_id = '';
			        mysql_query("INSERT INTO customers(name,email,mobile,location,birthday,anniversary) VALUES ('".$name."', '".$email."','".$mobile."','".$location."','".$birthday."','".$anniversary."') ");
					$customer_id = mysql_insert_id();
                             */
				
				
			
			}
				
			
			
		}
	
}

<form enctype="multipart/form-data" method="post"><input name="customer_details" type="file" />
<input name="import_csv" type="submit" value="Upload Customer CSV File" /></form>

3) Open csv_upload.php and upload your files

How to add google map in your website without paid API key ?

To add google map in your website without paid API key

1 Open google Map in your chrome browser

Type your location And Search as shown in figure

google-map

 

2) Click The Share Button
google-map

 

3) Click Embed optiongoogle-map

4) Paste the code in your page

That’s all 😛 you can add Google map without the Billing google API Key

How to check Client browser using PHP

Here is the simple function to display working browser in PHP

$user_agent = $_SERVER['HTTP_USER_AGENT'];

function getBrowser() {
global $user_agent;
$browser = "Unknown Browser";
$browser_array = array(
 '/msie/i' => 'Internet Explorer',
 '/firefox/i' => 'Firefox',
 '/safari/i' => 'Safari',
 '/chrome/i' => 'Chrome',
 '/edge/i' => 'Edge',
 '/opera/i' => 'Opera',
 '/netscape/i' => 'Netscape',
 '/maxthon/i' => 'Maxthon',
 '/konqueror/i' => 'Konqueror',
 '/mobile/i' => 'Handheld Browser'
 );
foreach ($browser_array as $regex => $value)
 if (preg_match($regex, $user_agent))
 $browser = $value;
return $browser;
}

$user_browser   = getBrowser();
if($user_browser == 'Safari')
{
echo "Safari";
}

How to check browser system OS using PHP ?

We have lot of script to checking browser system OS using PHP , here am going write one function using PHP

$user_agent = $_SERVER['HTTP_USER_AGENT'];
function getOS() { 
global $user_agent;
$os_platform = "Unknown OS Platform";
$os_array = array(
 '/windows nt 10/i' => 'Windows 10',
 '/windows nt 6.3/i' => 'Windows 8.1',
 '/windows nt 6.2/i' => 'Windows 8',
 '/windows nt 6.1/i' => 'Windows 7',
 '/windows nt 6.0/i' => 'Windows Vista',
 '/windows nt 5.2/i' => 'Windows Server 2003/XP x64',
 '/windows nt 5.1/i' => 'Windows XP',
 '/windows xp/i' => 'Windows XP',
 '/windows nt 5.0/i' => 'Windows 2000',
 '/windows me/i' => 'Windows ME',
 '/win98/i' => 'Windows 98',
 '/win95/i' => 'Windows 95',
 '/win16/i' => 'Windows 3.11',
 '/macintosh|mac os x/i' => 'Mac OS X',
 '/mac_powerpc/i' => 'Mac OS 9',
 '/linux/i' => 'Linux',
 '/ubuntu/i' => 'Ubuntu',
 '/iphone/i' => 'iPhone',
 '/ipod/i' => 'iPod',
 '/ipad/i' => 'iPad',
 '/android/i' => 'Android',
 '/blackberry/i' => 'BlackBerry',
 '/webos/i' => 'Mobile'
 );
foreach ($os_array as $regex => $value)
 if (preg_match($regex, $user_agent))
 $os_platform = $value;
return $os_platform;
}
// Get the system OS using PHP
$user_os = getOS();

if($user_os == 'Windows 8')
{
echo "Windows 8"
}