Mysql – date not saving in thesql database

MySQLzend-framework

I have a problem with saving a date in my MySQL database.
To test everything:

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.

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');


    }

Saving the data:

 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);

    }

}

Best Answer

I would guess this is the cause of your problem:

$evtStartDate->addValidator(new Zend_Validate_Date('dd-mm-YYYY'));

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.

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);

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.

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

Notes:

There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.
If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

mysqldump old_db | mysql new_db
If you have stored procedures, you can copy them afterwards:

mysqldump -R old_db | mysql new_db

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Best Answer

Related Solutions

Mysql – How to quickly rename a MySQL database (change schema name)

Mysql – concatenate multiple MySQL rows into one field

Related Topic