1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. Using update to insert multicheckbox values

Using update to insert multicheckbox values

12 years 7 months ago #176886 by houfton
Using update to insert multicheckbox values was created by houfton
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?
"UPDATE #__comprofiler SET cb_textfield = '".$old->cb_textfield."', cb_checkboxfield = '".$old->cb_checkboxfield."' WHERE..."

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.

Please Log in to join the conversation.

12 years 7 months ago #176889 by krileon
Replied by krileon on topic Re: Using update to insert multicheckbox values
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..)?

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

12 years 7 months ago #176908 by houfton
Replied by houfton on topic Re: Using update to insert multicheckbox values
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.

Please Log in to join the conversation.

12 years 7 months ago #176915 by houfton
Replied by houfton on topic Re: Using update to insert multicheckbox values
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
UPDATE jos_comprofiler SET ... cb_ccprevious = 'CC33|*|CC36' … WHERE user_id = 853

Please Log in to join the conversation.

12 years 7 months ago #176980 by houfton
Replied by houfton on topic Re: Using update to insert multicheckbox values
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!

Please Log in to join the conversation.

12 years 7 months ago #177067 by krileon
Replied by krileon on topic Re: Using update to insert multicheckbox values

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.
INSERT INTO `#__comprofiler` ( `id`, ``, `cb_ccprevious` ) VALUES ( [user_id], '[cb_ccprevious]' )
ON DUPLICATE KEY UPDATE `cb_ccprevious` = '[cb_ccprevious]';

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.
IF EXISTS ( SELECT QUERY HERE )
    UPDATE QUERY HERE
ELSE
    INSERT QUERY HERE

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

Kyle (Krileon)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Documentation + Search the forums
CB links: Documentation - Localization - CB Quickstart - CB Paid Subscriptions - Add-Ons - Forge
--
If you are a Professional, Developer, or CB Paid Subscriptions subscriber and have a support issue please always post in your respective support forums for best results!
--
If I've missed your support post with a delay of 3 days or greater and are a Professional, Developer, or CBSubs subscriber please send me a private message with your thread and will reply when possible!
--
Please note I am available Monday - Friday from 8:00 AM CST to 4:00 PM CST. I am away on weekends (Saturday and Sunday) and if I've missed your post on or before a weekend after business hours please wait for the next following business day (Monday) and will get to your issue as soon as possible, thank you.
--
My role here is to provide guidance and assistance. I cannot provide custom code for each custom requirement. Please do not inquire me about custom development.

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. Using update to insert multicheckbox values
Time to create page: 0.271 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Professional Members Support
  5. Using update to insert multicheckbox values

Search

Login / Logout

User Menu