| dml_updateSelection [message #621] |
Sat, 10 February 2007 07:30  |
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  |
AJM
Messages: 2369
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.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
