| MySQL: Updating SET data type fields [message #2543] |
Sun, 30 May 2010 01:48  |
ljkbrost
Messages: 59
Registered: April 2006
|
Member |
|
|
Hello,
When using a field that uses a SET data-type or an 'array' type within Radicore
$fieldspec['participant_approval_status']= array('type' => 'array',
'size' => 255,
'values' => array(1 => 'CONTRACT', 'TRANSFER', 'IDENTIFICATION', 'WIRE'),
'control' => 'm_checkbox',
'optionlist' => 'opt_list_approval_status',
'align_hv' => 'V');
If you update a field and leave all the options unchecked the resulting SQL looks something like this:
UPDATE part_participant SET participant_approval_status='0,0,0,0'
Things work great if your MySQL sql-mode does not have 'STRICT_TRANS_TABLES' set. If that option is set you get the following error message:
#1265 - Data truncated for column 'participant_approval_status' at row 1
The correct SQL when clearing all the settings should be:
UPDATE part_participant SET participant_approval_status=''
Essentially the ',0' is invalid SQL and when that happens it should be replaced with ''. So '0,WIRE,0,0' should be 'WIRE'.
I've looked through the code to see where to fix this, but I'm not sure how this data is manipulated.
|
|
|
|
| Re: MySQL: Updating SET data type fields [message #2544 is a reply to message #2543] |
Sun, 30 May 2010 06:06  |
AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK
|
Senior Member |
|
|
This problem only happens when the control is set to 'm_checkbox' as an unselected value is returned as '0' in the $_POST array. With a control of 'multidrop' any unselected values do not appear in the $_POST array.
The fix is quite simple. Edit file 'std.validation.class.inc' and at line 123 you should see the following:
if (preg_match('/(set|array|varray)/i',$fieldspec['type'])) {
// convert $fieldvalue array into a string with comma separator
if (is_array($fieldvalue)) {
$fieldvalue = implode($fieldvalue, ',');
} // if
} // if
Simply change it to the following:
if (preg_match('/(set|array|varray)/i',$fieldspec['type'])) {
// convert $fieldvalue array into a string with comma separator
if (is_array($fieldvalue)) {
foreach ($fieldvalue as $key => $value) {
if (empty($value)) {
unset($fieldvalue[$key]);
} // if
} // foreach
$fieldvalue = implode($fieldvalue, ',');
} // if
} // if
I shall include this fix in the next release.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|
|
|
Radicore Forum
Search
Help
Members
Register
Login
Home
