Using update to insert multicheckbox values

Hopefully you can help me with this one. I am trying to use an UPDATE query to insert some values (copied from one user to another) in multicheckbox fields but it is not working. The fields remain blank even when values are single - eg "value" - rather than multiple - eg "value1|*|value2". Text fields in the same query are being updated no problem so the query itself seems OK.

What is the correct way to do this?

The strange thing is I am using an external php script to do this and if I just call the script in the browser it works. Calling it through the CBSubs URL plugin it does not.

Query seams correct. Why is this being done from external script instead of CBSubs SQL Actions? What method are you using in CBSubs URL (GET, POST, ETC..)?

The script tests for a couple of things - mainly whether a registrant is already registered - and updates depending on the result so it seemed better to have one external script. Could be wrong though!

It uses GET for the user_id which works OK.

I have tried putting in wrong but valid values for the checkboxes after the initial registration but before the script is called and they are not being overwritten or blanked out, the script just seems to ignore them unless I call it directly.

I am using print_r - and getErrorMsg() - in the script to check for problems and all seems well when it is called directly eg

I have spent several hours looking at this and trying all sorts of things. I have finally found a way to make it work by changing the settings in the database for the multicheckboxes which are MEDIUMTEXT from Allow NULL "No" to "Yes".

I don't understand why it should be or if it is a bug in CB or Joomla itself but these values were not being updated by a php script called from a CBSubs URL even though the script itself seemed to function perfectly outside CBSubs.

I guess these fields are automatically set the way they are by CB for a reason which makes me wonder if changing it will have any bad effects elsewhere. But at least I can stop testing this script - for now!

The script tests for a couple of things - mainly whether a registrant is already registered - and updates depending on the result so it seemed better to have one external script. Could be wrong though!

You don't need PHP for this, if you're using MYSQL 5 (believe everyone is at this point) then you just use "ON DUPLICATE KEY UPDATE". Below is example of its usage.

More on this function can be found below. Please note the above is untested.

dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Another simply approach is to just perform a select query then do insert/update based off it. It's slower, but simpler to use. Example as follows.

Either way any of the above approaches would remove the PHP element and give you a more reliable and secure method for insert/updating users. The second method being a little slower as it performs 2 index scans, but that's not a problem as you're basically already doing that with your PHP.

There's currently a bug regarding select fieldtypes and substitutions if you're using CB 1.7, the bug is as follows where fix is provided. You may want to apply the fix as this could simply be a side affect of the bug.

forge.joomlapolis.com/issues/2713