Radicore Forum: Bug Reports » MySQL: Updating SET data type fields

Home » RADICORE development » Bug Reports » MySQL: Updating SET data type fields

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Code: [Select all] [Show/ hide]

    $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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

#1265 - Data truncated for column 'participant_approval_status' at row 1

The correct SQL when clearing all the settings should be:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: MySQL: Updating SET data type fields [message #2544 is a reply to message #2543]

Sun, 30 May 2010 06:06

AJM
Messages: 2382
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:

Code: [Select all] [Show/ hide]

        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:

Code: [Select all] [Show/ hide]

        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

Report message to a moderator

Re: MySQL: Updating SET data type fields [message #2545 is a reply to message #2543]

Sun, 30 May 2010 11:31

ljkbrost
Messages: 59
Registered: April 2006

Member

Hi Tony,

Thanks for the fix. It worked.

Cheers,

Kyle Brost
----

Report message to a moderator

Previous Topic:	Quick Reply not working
Next Topic:	Date conversion error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Fri Jun 20 19:11:45 EDT 2025

Total time taken to generate the page: 0.07730 seconds