Forums

New block will not store dates

Permalink
I am creating a block and it seems to be working well at first. It INSERTs and UPDATEs the the character strings without problem.

However, the dates seem to be INSERTed as their default not the ones given by the query. The UPDATEs seem to also ignore the given date info.

I am new to using ADODB and AXMLS, so I thought the error was in the save function of my block's controller.php. However, that code is small and pretty straight forward.

Just to be sure I activated the general query log in MySQL to see what was being passed . It all looked the way it should, but the NOW() function was not being executed and those values continued unchanged( including the field with the 'ON UPDATE current_timestamp' which was even more perplexing).

Just to be sure I took the query that came from C5 in the general log and executed it in the command line and this time it worked properly.

This means that, somehow, the same query has different results when executed by C5 and through the command line.

In the end I am not sure if this is a C5 problem or a MySQL problem.

Here are some more details:
MySQL 5.0
C5 5.2.1
my table:
CREATE TABLE `btLMyBlock` (
  `bID` int(10) unsigned NOT NULL,
  `title` varchar(255) default NULL,
  `subtitle` varchar(255) default NULL,
  `dateCreated` datetime NOT NULL default '0000-00-00 00:00:00',
  `dateModified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `datePublished` date NOT NULL default '0000-00-00',
  `text` longtext,
  PRIMARY KEY  (`bID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

controller.php:save()
function save( $data=array() ) {
         if( !$data || count($data)==0 ) $data=$_POST;
         $db = Loader::db();
         if(intval($this->bID)>0){    
            $q = "select count(*) as total from {$this->btTable} where bID = ".intval($this->bID);
            $total = $db->getOne($q);
         }else $total = 0;
         $q = '';
         $v = '';
         if( $total > 0 ){
            $v = array( $data['title'], $data['subtitle'], $data['datePublished'], $data['text'], intval($this->bID) );
            $q = "update {$this->btTable} set title=?, subtitle=?, dateModified=NULL, datePublished=?, text=? where bID=?";
         }else{
            $v = array( $data['title'], $data['subtitle'], $data['datePublished'], $data['text'], intval($this->bID) );
            $q = "insert into {$this->btTable} (title, subtitle, dateCreated, dateModified, datePublished, text, bID) values (?, ?, NOW(), NOW(), ?, ?, ?)";
Viewing 15 lines of 20 lines. View entire code block.


Example of UPDATE query in general.log:
update btLMyBlock set title='title#7', subtitle='subtitle#7', dateModified=NULL, datePublished=NOW(), text='text for the SEVENTH article71234' where bID=45;


I hope I have given enough information to diagnose the problem, however, if you need more info, don't hesitate to ask.

Thanks in advance.