php - excel date format not inserting in mysql -
in project inserted data using excel sheet can't inserted date format data mysql. here's code. have given full code better understanding. dob , join_date in date formate.
<?php session_start(); if(!isset($_session['userid'])) header('location:../../'); else { if((!isset($_post['streamid']) || $_post['streamid'] =='') || (!isset($_post['branchid']) || $_post['branchid'] =='') || (!isset($_post['batchid']) || $_post['batchid'] =='') || (!isset($_post['divisionid']) || $_post['divisionid'] =='') || (!isset($_post['semid']) || $_post['semid'] =='')) { header('location:../../views/student/list.php'); } $tmp = explode(".", $_files["studnetexcel"]["name"]); if((strcmp($tmp[1], 'xls')!=0) && (strcmp($tmp[1], 'xlsx')!=0)) { echo "string"; } else { require_once('../../config/connection.php'); $streamresult = mysql_query("select * streams streams_id='".$_post['streamid']."' "); $branchresult = mysql_query("select * branches streams_id='".$_post['streamid']."' , branches_id='".$_post['branchid']."' "); $batchresult = mysql_query("select * batches streams_id='".$_post['streamid']."' , branches_id='".$_post['branchid']."' , batches_id='".$_post['batchid']."'"); $divisionresult = mysql_query("select * division division_id='".$_post['divisionid']."' "); $semresult = mysql_query("select * edu_system streams_id='".$_post['streamid']."' , edu_sys_id='".$_post['semid']."' "); if((mysql_num_rows($streamresult) == 1) && (mysql_num_rows($branchresult) == 1) && (mysql_num_rows($batchresult) == 1) && (mysql_num_rows($divisionresult) == 1) && (mysql_num_rows($semresult) == 1)) { require_once '../../classes/phpexcel/iofactory.php'; $objphpexcel = phpexcel_iofactory::load($_files["studnetexcel"]["tmp_name"]); $value=''; foreach ($objphpexcel->getworksheetiterator() $worksheet) { $worksheettitle = $worksheet->gettitle(); $highestrow = $worksheet->gethighestrow(); $highestcolumn = $worksheet->gethighestcolumn(); $highestcolumnindex = phpexcel_cell::columnindexfromstring($highestcolumn); $nrcolumns = ord($highestcolumn) - 64; ($row = 4; $row <= $highestrow; ++ $row) { $value.="(null,".$_post['streamid'].",".$_post['branchid'].",".$_post['batchid'].",".$_post['divisionid'].",".$_post['semid'].","; ($col = 0; $col < $highestcolumnindex; ++ $col) { $cell = $worksheet->getcellbycolumnandrow($col, $row); if($col==$highestcolumnindex-1) { $value .= "'".$cell->getvalue()."',"; $value .= $_session['role']['id'].","."'active'"; } else { if($row>2 && str_replace(' ', '', $worksheet->getcellbycolumnandrow($col, 1)->getvalue())=="joindate") $value .= "'".date('y-m-d',strtotime($cell->getvalue()))."',"; else $value .= "'".$cell->getvalue()."',"; } } $value .= '),'; } } if(mysql_query("insert ignore students(students_id,streams_id,branches_id,batches_id,division_id,edu_sys_id,annrl_no,first_name,middle_name,last_name,address1,city,state,pincode,address2,gender,cast,blood_group,contact,dob,email,join_date,password,p_contact,p_email,added_by,status) values".substr($value, 0,-1))) { $_session['message'] = "student successfuly added"; header("location:../../views/student/add.php?streamid=".$_post['streamid']."&branchid=".$_post['branchid']."&batchid=".$_post['batchid']."&divisionid=".$_post['divisionid']."&semid=".$_post['semid']); } else { if(mysql_errno() == 1062) { $inc=-1; if(preg_match('/email/', mysql_error())) { $inc++;$_session['notify'][$inc] = "student/parents email existed"; header("location:../../views/student/add.php?streamid=".$_post['streamid']."&branchid=".$_post['branchid']."&batchid=".$_post['batchid']."&divisionid=".$_post['divisionid']."&semid=".$_post['semid']); } else if(preg_match('/annrl_no/', mysql_error())) { $inc++;$_session['notify'][$inc] = "enrolment numbers repeated"; header("location:../../views/student/add.php?streamid=".$_post['streamid']."&branchid=".$_post['branchid']."&batchid=".$_post['batchid']."&divisionid=".$_post['divisionid']."&semid=".$_post['semid']); } } else { echo mysql_error(); echo mysql_errno(); } } } else{ header("location:../../views/student/add.php"); } } } ?> and excel sheet is. 
i getting 0000-00-00 record in dob , join_date.
normally, excel "date" ms serialized timestamp, measure of number of days since 1st january 1900 (or 1st january 1904 if created on apple mac version of ms excel).... not unix timestamp, nor formatted date string.
phpexcel provides number of methods converting date either unix timestamp, or php datetime object can format entry sql statement.
assuming case, columns n , p, you'd need use
$dto = phpexcel_shared_date::exceltophpobject($cell->getvalue()); and can use datetime object's format() method convert y-m-d formatted string
or
$dto = phpexcel_shared_date::exceltophp($cell->getvalue()); to convert unix timestamp, can formatted using date()
Comments
Post a Comment