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

Home » RADICORE development » Bug Reports » MySQL: Updating SET data type fields
MySQL: Updating SET data type fields [message #2543] Sun, 30 May 2010 01:48 Go to next message
ljkbrost is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
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.


Re: MySQL: Updating SET data type fields [message #2545 is a reply to message #2543] Sun, 30 May 2010 11:31 Go to previous message
ljkbrost is currently offline  ljkbrost
Messages: 59
Registered: April 2006
Member
Hi Tony,

Thanks for the fix. It worked.

Cheers,


Kyle Brost
----
Previous Topic: Quick Reply not working
Next Topic: Date conversion error
Goto Forum:
  


Current Time: Sun Nov 28 15:01:37 EST 2021

Total time taken to generate the page: 0.00987 seconds