Tutorialshore
We have to add different languages like Hindi,Marathi, Tamil etc inside text filed as per the required. Normally if you insert Hindi it will come ?????????? instead of characters in DB.To avoid this situation we have to set the DB to utf8 COLLATE utf8_unicode_ci.It support other character set also.Here am going to explain how to set it ,to insert Hindi language in MySQL we have to do below action
ALTER DATABASE CHARACTER SET utf8 COLLATE utf8_unicode_ci;
Run below SQL query to check default character set
SHOW VARIABLES LIKE ‘character\_set\_%’;
Variable_name Value character_set_client utf8mb4 character_set_connection utf8mb4 character_set_database latin1 character_set_filesystem binary character_set_results utf8mb4 character_set_server latin1 character_set_system utf8
here the character_set_database value is latin2 To insert Hindi in MySQL database using PHP, we need to update character_set_database value to utf8 ,ALTER DATABASE and change the DB character and set it to utf8 COLLATE utf8_bin
ALTER DATABASE DB_NAME CHARACTER SET utf8 COLLATE utf8_bin
To select all event from MySQL varchar date, that start from tomorrow onwards, for that we have a easy method via using STR_TO_DATE function.It convert stored date and compare with CURDATE() function
Here in this example I have an even table, in that table event start date is stored in the filed named event_start_time, my requirement is to fetch all event that is greater than today date and ineed in the event date ascending order.
See MySQL query
SELECT * FROM event WHERE STR_TO_DATE(event_start_time, '%d-%m-%Y') > CURDATE() ORDER BY STR_TO_DATE(event_start_time,'%d-%m-%Y') Asc
To Fetch coming 7 days data event
SELECT * FROM event WHERE STR_TO_DATE(event_start_time, '%d-%m-%Y') > CURDATE() AND STR_TO_DATE(event_start_time, '%d-%m-%Y') <= CURDATE() + INTERVAL 7 DAY
To Fetch coming one month data event
SELECT * FROM event WHERE STR_TO_DATE(event_start_time, '%d-%m-%Y') > CURDATE() AND STR_TO_DATE(event_start_time, '%d-%m-%Y') <= CURDATE() + INTERVAL 30 DAY
To fetch Today’s Event
SELECT * FROM event WHERE STR_TO_DATE(event_start_time, '%d-%m-%Y') = CURDATE()
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