SQL to return a list of Community Builder Fields

  • reeg99
  • Topic Author
  • Offline
  • New Member
  • New Member
More
5 years 11 months ago #111507 by reeg99
I'm struggling with the correct SQL to use in a custom field.
In my set up we have the Community Builder (CB) integration enabled. In the users CB profile we have added fields where they record their children's names (our site is for booking nursery places).
So in the comprofiler table there are the fields cb_child1, cb_child2 etc.

I am looking to have a field on the Event Booking registration form where the [logged in] user picks from a drop down list, the name of the child that they are booking a session (event) for.

So far I have the following



This query works fine in phpmyadmin and returns two columns of values.

However when I put this code in the custom field 'Default Value', on the registration form you get an empty list.

I'm also not sure how you would pass the logged in users' user id to the query.

A steer in the right direction would be appreciated.

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #111515 by Tuan Pham Ngoc
Replied by Tuan Pham Ngoc on topic SQL to return a list of Community Builder Fields
Hi

We don't support passing USER_ID tag to the query. I just modified code a bit to support it. Please get this file, unzip it, upload the received file to administrator/components/com_eventbooking/libraries/rad/form/field folder, then try again

You can use [USER_ID] tag in the QUERY and it will be replaced with ID of current logged in user

Regards,

Tuan
Attachments:
The following user(s) said Thank You: Patrick

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #111516 by Tuan Pham Ngoc
Replied by Tuan Pham Ngoc on topic SQL to return a list of Community Builder Fields
However, I am afraid of that still doesn't work. When administrator edit the record from administrator area of your site, for example, it will be replaced with ID of that administrator, so the list will be different and the data which registrant selected could be lost

So I guess you will have to find a different way

Tuan
The following user(s) said Thank You: reeg99

Please Log in or Create an account to join the conversation.

  • reeg99
  • Topic Author
  • Offline
  • New Member
  • New Member
More
5 years 11 months ago #111542 by reeg99
Thanks for the updated file enabling support for passing user_id. I think we can live with the limitation you warn about.

By the way I've figured out why my code was failing. I didn't need the final UNI0N ALL statement (in fact they could be just UNI0N statements). Also the 'value' and 'text' are case sensitive and need to be lowercase.

Please Log in or Create an account to join the conversation.

More
5 years 11 months ago #111553 by Tuan Pham Ngoc
Replied by Tuan Pham Ngoc on topic SQL to return a list of Community Builder Fields
OK, great, then

Tuan

Please Log in or Create an account to join the conversation.

More
5 years 6 months ago #117585 by Patrick
Hi Tuan

I was looking to use user id in SQL as well.

This solution is great - it works.

Patrick

Please Log in or Create an account to join the conversation.

Moderators: Tuan Pham Ngoc