[SOLVED] Updating fields in database row

4 years 3 months ago #315840 by jimstrong
Replied by jimstrong on topic Updating fields in database row
OK, understood - thanks a lot!

Please Log in to join the conversation.

4 years 3 months ago #315895 by jimstrong
Replied by jimstrong on topic Updating fields in database row
Hi again. I've been struggling with this since my last reply - I've replaced the DATE_FIELD_NAME and CARER_FIELD_NAME with the correct field names, but the Query Field is not showing in the tab at all unless I remove the WHERE FIND_IN_SET condition.

In the sql:- WHERE FIND_IN_SET( `id`, REPLACE( '[CARER_FIELD_NAME]', '|*|', ',' ) ) > 0 can you tell me what id the `id` is expecting to find as I have a feeling that I might not be capturing the id correctly?

Thanks for any help you can bring, as I can understand this is really outside of basic CB support.

Please Log in to join the conversation.

4 years 3 months ago #315904 by krileon
Replied by krileon on topic Updating fields in database row
Strongly suggest using the Code field usage. The SQL usage isn't efficient SQL (there's no way to make it efficient due to how multi-selects are stored). Code usage is in my below reply.

www.joomlapolis.com/forum/153-professional-member-support/241916-updating-fields-in-database-row?limitstart=0#315832


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.

4 years 3 months ago #315908 by jimstrong
Replied by jimstrong on topic Updating fields in database row
Thanks. I should have said that I also tried the code usage, but it comes up with a blank result as well (just a "-").

I am allowing the selection of the Carer name by using this query - could that be causing a problem?

SELECT CONCAT (firstname,' ',lastname) FROM `#__comprofiler` WHERE `cb_shac_role` LIKE '%Befriender%' OR '%Office|*|Befriender%'

Please Log in to join the conversation.

4 years 3 months ago #315910 by krileon
Replied by krileon on topic Updating fields in database row
Yes, that'd be a problem as you're doing selection based off first and last name. The code examples I've provided are using their user id. You should not be storing the selection in the database by first and last name as name is not unique so you could have 5 people with the same name for example. I suggest changing your query select to use the following.

Query:
SELECT `id`, CONCAT( `firstname`, ' ', `lastname` ) AS name
 FROM `#__comprofiler`
 WHERE `cb_shac_role` LIKE '%Office%' OR `cb_shac_role` LIKE '%Befriender%'
Value Column: id
Label Column: name

Note all existing selections for that field will lose their values and need to be re-selected. This will then store by id and allow the code examples I've provided you to work fine.


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.

4 years 3 months ago #315912 by jimstrong
Replied by jimstrong on topic Updating fields in database row
Oh, thanks so much again - will give that a try. Guidance much appreciated!!

Please Log in to join the conversation.

Moderators: beatnantkrileon
Time to create page: 0.179 seconds

Facebook Twitter LinkedIn