日期不保存在mysql数据库中 [英] date not saving in mysql database
问题描述
我在MySQL数据库中保存日期时遇到问题. 要测试所有内容:
I have a problem with saving a date in my MySQL database. To test everything:
我正在尝试将2010-01-01(例如)保存在MySQL数据库中. 首先，我将MySQL字段设置为最新.这没用.但是，当我将字段设置为字符串类型时，它的确在数据库中保存了日期.
I am trying to save 2010-01-01 (for example) in a MySQL database. First I set my MySQL field to date. This didn't work. But when I set the field to a string type it does save date in the database.
为什么要将日期保存到日期字段中时不起作用?
Why doesn't it work when I want to save a date into a date field?
尽管我认为代码在这里并不重要，但我会根据要求将其发布.
Although I think code isn't important here I will post it since it's requested.
我使用zf 1.9.6
I use zf 1.9.6
class JS_Form_EventForm extends ZendX_JQuery_Form{
public function init($options=null){
//parent::__construct($options);
$this->setName("newEvent");
$this->addElementPrefixPath('JS_Validate','JS/Validate/','validate');
//naam
$evtName = new Zend_Form_Element_Text("evt_name");
$evtName->setLabel("Evenement Naam: ")
->setRequired(true);
// omschrijving
$evtDescription = new Zend_Form_Element_Textarea("evt_description",array("rows"=>6,"cols"=>25));
$evtDescription->setLabel("Evenement omschrijving: ");
// locatie
$evtAdr = new Zend_Form_Element_Select("adr_id");
$evtAdr->setLabel("Locatie: ");
$locaties = $this->getLocations();
$evtAdr->setMultiOptions($locaties);
$newAdr = new Zend_Form_Element_Button("new_adr");
$newAdr->setLabel("+");
// begin datum
$evtStartDate = new ZendX_JQuery_Form_Element_DatePicker("evt_startdate",array("label"=> "Begin Datum"));
$evtStartDate->setJQueryParam('dateFormat', 'dd-mm-yy');
$evtStartDate->addValidator(new Zend_Validate_Date('dd-mm-YYYY'));
// eind datum
$evtEndDate = new ZendX_JQuery_Form_Element_DatePicker("evt_enddate",array("label"=> "Eind Datum"));
$evtEndDate->setJQueryParam('dateFormat', 'dd-mm-yy');
$evtEndDate->addValidator(new Zend_Validate_Date('dd-mm-YYYY'));
$evtEndDate->addValidator('CompareDates',false,array('evt_startdate'));
// begin tijd
$evtStartTime = new Zend_Form_Element_Text("evt_starttime");
$evtStartTime->setLabel("Begin Tijd");
$evtStartTime->addValidator(new Zend_Validate_Date('hh:mm',new Zend_Locale('auto')));
// eind tijd
$evtEndTime = new Zend_Form_Element_Text("evt_endtime");
$evtEndTime->setLabel("Eind tijd");
$evtEndTime->addValidator(new Zend_Validate_Date('hh:mm'));
// aantal personen
$amountPersons = new Zend_Form_Element_Text("evt_amtpersons");
$amountPersons->setLabel("Aantal personen nodig ");
$save = new Zend_Form_Element_Submit("save");
$save->setLabel("Opslaan");
$this->addElements(array($evtName,$evtDescription,$evtAdr,$newAdr,$evtStartDate,$evtEndDate,$evtStartTime,$evtEndTime,$amountPersons,$save));
$this->setMethod('post');
$this->setAction(Zend_Controller_Front::getInstance()->getBaseUrl().'/events/add');
}
保存数据:
public function addAction()
{
// action body
$form = new JS_Form_EventForm();
if(!$this->getRequest()->isPost()){
$this->view->form = $form;
}else{
$formdata = $this->_request->getPost();
if(!$form->isValid($formdata)){
$this->view->form = $form;
}else{
//http://zendgeek.blogspot.com/2009/07/zend-framework-building-complete.html
unset($formdata['save']);
$formdata['evt_name'] = ucfirst($formdata['evt_name']);
$e = new JS_Model_events();
$e->insert($formdata);
if($e ==true){
$this->_redirect('events/list');
}else{
echo 'Iets gaat niet goed';
}
/*
$formdata['sts_id'] = 1;
$eventsTable = new JS_Model_DbTable_events();
$eventsTable->insert($formdata);
*/
die();
// form valid process data
}
}
}
class JS_Model_DBTable_Events extends Zend_Db_Table_Abstract{
protected $_name = 'events';
public function remove($id){
if(isset($id)){
$where = $this->getAdapter()->quoteInto('evt_id = ?', $id);
return($this->delete($where));
}else{
return false;
}
}
/*
* function selectOne
* @param int id the id of the event to select
* @return resultset.
*/
public function selectOneRow($id){
//$where = $this->getAdapter()->quoteInto('evt_id = ?', $id);
//$query = $this->select($where);
//$result= $this->fetchRow($query);
$select = $this->select();
$select->where('evt_id = ?', $id);
$rows = $this->fetchAll($select);
return($rows);
}
}
推荐答案
我想这是造成您问题的原因:
I would guess this is the cause of your problem:
$evtStartDate->addValidator(new Zend_Validate_Date('dd-mm-YYYY'));
MySQL无法理解格式为dd-mm-YYYY的日期文字. MySQL理解YYYY-MM-DD或YY-MM-DD以及其他一些变体.
MySQL doesn't understand date literals in the format dd-mm-YYYY. MySQL understands YYYY-MM-DD or YY-MM-DD, and a few other variations.
请参见 http://dev.mysql.com/doc/refman/5.1/en/datetime.html 获取有关接受日期文字格式的官方文档.
See http://dev.mysql.com/doc/refman/5.1/en/datetime.html for official documentation on accepted date literal formats.
Re comment: You can either convert the date in your PHP code, or else you can insert an expressing using the STR_TO_DATE() MySQL function. Pass a Zend_Db_Expr object in place of the literal value.
$startdate_expr = $this->getAdapter()->quoteInto("STR_TO_DATE(?, '%d-%m-%Y')",
$formdata["evt_startdate"]);
$formdata["evt_startdate"] = new Zend_Db_Expr($startdate_expr);
$enddate_expr = $this->getAdapter()->quoteInto("STR_TO_DATE(?, '%d-%m-%Y')",
$formdata["evt_enddate"]);
$formdata["evt_enddate"] = new Zend_Db_Expr($enddate_expr);
$e->insert($formdata);
或者您可以更改Web应用程序的格式，以要求日期采用YYYY-MM-DD格式，这样就不必进行任何转换.
Or you can change your web application's form to require dates to be in YYYY-MM-DD format, and then you don't have to convert anything.
这篇关于日期不保存在mysql数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
