1
luciorota
Database: How do I set a column value to NULL
  • 2018/12/12 15:43

  • luciorota

  • Module Developer

  • Posts: 216

  • Since: 2007/4/20


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?

2
Mamba
Re: Database: How do I set a column value to NULL
  • 2018/12/14 5:05

  • Mamba

  • Moderator

  • Posts: 11366

  • Since: 2004/4/23


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

3
luciorota
Re: Database: How do I set a column value to NULL
  • 2018/12/14 22:15

  • luciorota

  • Module Developer

  • Posts: 216

  • Since: 2007/4/20


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

4
Mamba
Re: Database: How do I set a column value to NULL
  • 2018/12/15 2:44

  • Mamba

  • Moderator

  • Posts: 11366

  • Since: 2004/4/23


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/1691117/how-to-store-null-values-in-datetime-fields-in-mysql
Support XOOPS => DONATE
Use 2.5.10 | Docs | Modules | Bugs

5
luciorota
Re: Database: How do I set a column value to NULL
  • 2018/12/15 11:37

  • luciorota

  • Module Developer

  • Posts: 216

  • Since: 2007/4/20


Hi Mamba
Everything checked

I think it is a Xoops limit...

6
geekwright
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.

7
luciorota
Re: Database: How do I set a column value to NULL
  • 2019/8/21 12:49

  • luciorota

  • Module Developer

  • Posts: 216

  • Since: 2007/4/20


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

Login

Who's Online

215 user(s) are online (67 user(s) are browsing Support Forums)


Members: 0


Guests: 215


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits