SQL改造excel日期

Question

我真的陷入了导入excel表格数据到database.In我的excel表格的角落我有三个日期字段:道布，注册和EXPIRE.now当我输入这些字段的日期并将其导入mysql数据库时，它要么显示0000-00-00，要么随机选择字段中的任何日期，而excel表格将日期格式化为星期一-年。

请大家帮帮忙。

下面是我导入code.dont，知道我是否需要在这里用php格式化日期。

<?php
/************************ YOUR DATABASE CONNECTION START HERE   ****************************/

define ("DB_HOST", "localhost"); // set database host
define ("DB_USER", "root"); // set database user
define ("DB_PASS",""); // set database password
define ("DB_NAME","oysg"); // set database name

// $link = mysql_connect(DB_HOST, DB_USER, DB_PASS) or die("Couldn't make connection.");
// $db = mysql_select_db(DB_NAME, $link) or die("Couldn't select database");

$databasetable = "applicant";

$con = new mysqli(DB_HOST, DB_USER,DB_PASS,DB_NAME);
/************************ YOUR DATABASE CONNECTION END HERE  ****************************/


set_include_path(get_include_path() . PATH_SEPARATOR . 'Classes/');
// @include('PHPExcel/IOFactory.php');

// This is the file path to be uploaded.
 $inputFileName = public_path().'/xls/'.$filename; 

try {
    $objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch(Exception $e) {
    die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}


$allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
$arrayCount = count($allDataInSheet);  // Here get total count of row in that Excel sheet


for($i=2;$i<=$arrayCount;$i++)
{
$surname    = trim(strtoupper($allDataInSheet[$i]["A"]));
$othernames = trim(strtoupper($allDataInSheet[$i]["B"]));
$address    = strtoupper($allDataInSheet[$i]["C"]);
$lga        = trim(strtoupper($allDataInSheet[$i]["D"]));
$sex        = trim(strtoupper($allDataInSheet[$i]["E"]));
$dob        = trim(strtoupper($allDataInSheet[$i]["F"]));
$genotype   = trim(strtoupper($allDataInSheet[$i]["G"]));
$blood_grp  = trim(strtoupper($allDataInSheet[$i]["H"]));
$phone      = trim(strtoupper($allDataInSheet[$i]["I"]));
$email      = trim(strtoupper($allDataInSheet[$i]["J"]));
$occupation = trim(strtoupper($allDataInSheet[$i]["K"]));
$place_emp  = trim(strtoupper($allDataInSheet[$i]["L"]));
$facility   = trim(strtoupper($allDataInSheet[$i]["M"]));
$medical_his = trim(strtoupper($allDataInSheet[$i]["N"]));
$allergy    = trim(strtoupper($allDataInSheet[$i]["O"]));
$duration   = trim(strtoupper($allDataInSheet[$i]["P"]));
$registered     = trim(strtoupper($allDataInSheet[$i]["Q"]));
$expires    = trim(strtoupper($allDataInSheet[$i]["R"]));
$collector  = trim(strtoupper($allDataInSheet[$i]["S"]));
$form_no    = trim(strtoupper($allDataInSheet[$i]["T"]));
$tell_no    = trim(strtoupper($allDataInSheet[$i]["U"]));
$amt_paid   = trim(strtoupper($allDataInSheet[$i]["V"]));

$query = "SELECT surname FROM `applicant` WHERE `surname` = '$surname' and `othernames` = '$othernames'";
$sql = $con->query($query);
$recResult = mysqli_fetch_array($sql);
$existName = $recResult["surname"];
if($existName=="") {
$insertTable= $con->query("insert into `applicant` (surname, othernames,address,lga,sex,dob,genotype,blood_grp,
    phone,email,occupation,place_emp,facility,medical_his,allergy,duration,registered,expires,collector,form_no,tell_no,amt_paid) 
    values('".$surname."', '".$othernames."','".$address."','".$lga."','".$sex."','".$dob."',
        '".$genotype."','".$blood_grp."','".$phone."','".$email."','".$occupation."',
        '".$place_emp."','".$facility."','".$medical_his."','".$allergy."','".$duration."','".$registered."',
        '".$expires."','".$collector."','".$form_no."','".$tell_no."','".$amt_paid."');");


$msg = 'Record has been added';
}
else 
{
$msg = 'Record already exist';
}
}
Xls::where('name',$filename)->delete();
unlink(public_path().'/xls/'.$filename); 
echo "<div class='alert alert-info'>".$msg."</div>";


?>

Answer 1

格式化的Excel日期不一定是插入到SQL数据库中的正确格式，您可能需要将日期作为原始Excel序列化时间戳读取，使用PHPExcel_Shared_Date::ExcelToPHP()或PHPExcel_Shared_Date::ExcelToPHPObject()将其转换为unix时间戳或PHP对象，然后使用date()或DateTime对象的format()方法将其转换为适用于MySQL的年-月-日格式

您可以将调用修改为

toArray(null,true,true,true);

方法将所有值作为原始(未格式化)值读取

toArray(null,true,false,true);

标志

Answer 2

我认为在插入之前需要设置正确的格式。因此，对于插入中的$expires，请使用mysql函数DATE_FORMAT(日期，格式) ...

更多信息请点击此处：http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

Answer 3

我只是通过将日期字段设置为文本并将日期写为: 2014-02-09，这是mysql正确的日期format.and，对php代码中的日期调用->format方法，从而解决了这个问题。