xoops forums

luciorota

Module Developer
Posted on: 12/12 15:43
luciorota
luciorota (Show more)
Module Developer
Posts: 197
Since: 2007/4/20
#1

Database: How do I set a column value to NULL

I have a question.

I have to set a database column value to NULL but ...
My object is an extension of XoopsPersistableObject class.
Var 'date' data type is XOBJ_DTYPE_DATE
Database field is defines as
date DATE NULL DEFAULT NULL

Any suggestion?

Mamba

Moderator
Posted on: 12/14 5:05
Mamba
Mamba (Show more)
Moderator
Posts: 10798
Since: 2004/4/23
#2

Re: Database: How do I set a column value to NULL

In NewBB's table "newbb_stats" you have:
`time_update`  DATE                          DEFAULT NULL,


Is this what you were looking for?
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs

luciorota

Module Developer
Posted on: 12/14 22:15
luciorota
luciorota (Show more)
Module Developer
Posts: 197
Since: 2007/4/20
#3

Re: Database: How do I set a column value to NULL

Thanks Mamba
I would like to reset time_update value to NULL
When i use an expression like this:

$itemObj->setVar('myDate'NULL);


does note set 'MyDate' field to NULL but to '0000-00-00'
are there some tips to solve my question?

Thanks again

Mamba

Moderator
Posted on: 12/15 2:44
Mamba
Mamba (Show more)
Moderator
Posts: 10798
Since: 2004/4/23
#4

Re: Database: How do I set a column value to NULL

Are you testing it on Windows? Maybe you need to turn off the "STRICT_MODE" in the MySQL configuration
You might also use "datetime" instead of "date"
See:
https://stackoverflow.com/questions/16 ... -datetime-fields-in-mysql
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs

luciorota

Module Developer
Posted on: 12/15 11:37
luciorota
luciorota (Show more)
Module Developer
Posts: 197
Since: 2007/4/20
#5

Re: Database: How do I set a column value to NULL

Hi Mamba
Everything checked

I think it is a Xoops limit...

geekwright

Quite a regular
Posted on: 12/16 17:54
geekwright
geekwright (Show more)
Quite a regular
Posts: 274
Since: 2010/10/15
#6

Re: Database: How do I set a column value to NULL

Quote:
luciorota wrote:
...
I think it is a Xoops limit...

Sadly, you are correct.

I did a dive into the code when I saw your question, and the XoopsObject family seems to have no direct support for NULLs. Updating a column to NULL presently would require building the update query manually and running it with the database query() method.

Likewise the handling of a NULL value in selects seems to be datatype dependent, more like a side effect.

luciorota

Module Developer
Posted on: 8/21 12:49
luciorota
luciorota (Show more)
Module Developer
Posts: 197
Since: 2007/4/20
#7

Re: Database: How do I set a column value to NULL

This is not a solution but a temporary patch.
Define your objects a subclass of ModuleXoopsObject class... this way it is possible to set a column value to NULL...

abstract class ModuleXoopsObject extends XoopsObject {
    
/**
     * @var moduleHelper
     * @access private
     */
    
private $moduleHelper null;

    
/**
     * constructor
     */
    
public function __construct()
    {
        
$this->moduleHelper XmfModuleHelper::getHelper('module');
        
$this->db XoopsDatabaseFactory::getDatabaseConnection();
        
//
        
parent::__construct();
    }
    
    
/**
     * assign a value to a variable (also null is allowed)
     *
     * @access public
     * @param string $key   name of the variable to assign
     * @param mixed  $value value to assign
     * @param bool   $not_gpc
     */
    
public function setVar($key$value$not_gpc false)
    {
        if (!empty(
$key) && isset($value) && isset($this->vars[$key])) {
            
$this->vars[$key]['value']   =& $value;
            
$this->vars[$key]['not_gpc'] = $not_gpc;
            
$this->vars[$key]['changed'] = true;
            
$this->setDirty();
        }
        if (!empty(
$key) && is_null($value) && isset($this->vars[$key])) {
            
$this->vars[$key]['value']   = null;
            
$this->vars[$key]['not_gpc'] = $not_gpc;
            
$this->vars[$key]['changed'] = false;
            
$this->setDirty();
        }
    }
}



abstract class 
ModuleXoopsObjectHandler extends XoopsPersistableObjectHandler
{
    
/**
     * @var moduleHelper
     * @access private
     */
    
private $moduleHelper null;

    
/**
     * @param null|object   $db
     */
    
public function __construct($db null$table ''$className ''$keyName ''$identifierName '')
    {
        
parent::__construct($db$table$className$keyName$identifierName);
        
$this->moduleHelper XmfModuleHelper::getHelper('module');
    }
    
    
/**
     * insert an object into the database
     *
     * @param  XoopsObject $object {@link XoopsObject} reference to object
     * @param  bool        $force  flag to force the query execution despite security settings
     * @return mixed       object ID
     */
    
public function insert(XoopsObject $object$force true)
    {
        
$ret parent::insert($object$force);
        
// handle null values
        
$queryFunc = empty($force) ? 'query' 'queryF';
        
$vars $object->getVars();
        foreach (
$vars as $key => $value) { 
            if (
is_null($value['value'])) {
                
$sql "UPDATE `{$this->table}` SET `{$key}` = NULL WHERE `{$this->keyName}` = {$this->db->quote($object->getVar($this->keyName))}";
                if (!
$result $this->db->{$queryFunc}($sql)) {
                    
//return false;
                

            }
        }
        return 
$ret;
    }
}