Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Bug Reports » dml_updateSelection
dml_updateSelection [message #621] Sat, 10 February 2007 07:30 Go to next message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
Not sure if this is a bug.

I have some business logic in _cm_pre_insertRecord ($rowdata)

Tried to do update statement like
'UPDATE '. $table .' SET rgt=rgt+2 WHERE rgt>='. $parent_rgt;

So, I did:
$where_parent_rgt = 'rgt>='. $parent_rgt;
$replace_parent_rgt = 'rgt=rgt+2';
_dml_updateSelection ($where_parent_rgt, $replace_parent_rgt);

Apparently, the method keeps updating rgt to 0 for the affected records.

When I tested with
$replace_parent_rgt = 'rgt=12';

all affected records were updated correctly with rgt = 12.

Seems like _dml_updateSelection is not able to deal with expressions with operators.

Is this a bug? If it is not, do you currenly have a function that allows me to do the above in the framework?

Re: dml_updateSelection [message #622 is a reply to message #621] Sat, 10 February 2007 10:54 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2347
Registered: April 2006
Location: Surrey, UK
Senior Member
The reason why this is failing is that when I construct the sql UPDATE statement I put quotes around all the values, so instead of producing "rgt=rgt+2" I get "rgt='rgt+2'", and when you try to put a string into a number MySQL replaces it with zero.

The solution is relatively simple. Go to the updateRecord() method in file 'dml.mysql.class.inc' and where you see
// build update string from non-pkey fields
$update = '';
foreach ($fieldarray as $item => $value) {
    // use this item if it IS NOT part of primary key
    if (!in_array($item, $this->primary_key)) {
        if (is_null($value) or strtoupper(trim($value)) == 'NULL') {
            // null entries are set to NULL, not '' (there is a difference!)
            $update .= "$item=NULL,";
        } else {
            // change to the new value
            $update .= "$item='" .mysql_real_escape_string($value, $this->dbconnect) ."', ";
        } // if
    } // if
} // foreach

you should change it to the following:
// build update string from non-pkey fields
$update = '';
$pattern = '/(integer|decimal|numeric|float|real)/i'; <*** NEW
foreach ($fieldarray as $item => $value) {
    // use this item if it IS NOT part of primary key
    if (!in_array($item, $this->primary_key)) {
        if (is_null($value) or strtoupper(trim($value)) == 'NULL') {
           // null entries are set to NULL, not '' (there is a difference!)
            $update .= "$item=NULL,";
new**>  } elseif (preg_match($pattern, $fieldspec[$item]['type'], $match)) {
new**>      // do not enclose numbers in quotes (this also allows 'value=value+1'
new**>      $update .= "$item=$value,";
        } else {
            // change to the new value
            $update .= "$item='" .mysql_real_escape_string($value, $this->dbconnect) ."', ";
        } // if
    } // if
} // foreach

Check if this works OK for you and I'll include this change in the next release.


Re: dml_updateSelection [message #623 is a reply to message #622] Sun, 11 February 2007 15:47 Go to previous message
stephenboey is currently offline  stephenboey
Messages: 54
Registered: January 2007
Member
alrite, tested and it is working
Previous Topic: Task, Role - Field Access functionality not working
Next Topic: different results in queries
Goto Forum:
  


Current Time: Thu Mar 28 13:33:57 EDT 2024

Total time taken to generate the page: 0.00951 seconds