Events Booking Custom Field - Assigning using SQL

David Unwin
Topic Author
Offline
Platinum Member

More

2 years 4 days ago - 2 years 4 days ago #157690 by David Unwin

Events Booking Custom Field - Assigning using SQL was created by David Unwin

I would like to populate a custom field when someone makes a booking
All of our registered users have a Membership Plan (from Membership Pro).
We would like to capture their Plan id when they register for an event.
I want to select 'plan_id' from prefix_osmembers_subcribers table from the user who is logged in.
Reading the documentation eventbookingdoc.joomservices.com/basic-s...m-fields/field-types
it is no clear how I can achieve this.
Can someone give me some information on how to achieve this? also how do I handle users who are not logged in?

Attachments:

Last edit: 2 years 4 days ago by David Unwin.

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

Tuan Pham Ngoc
Offline
Administrator

More

2 years 4 days ago #157691 by Tuan Pham Ngoc

Replied by Tuan Pham Ngoc on topic Events Booking Custom Field - Assigning using SQL

Hi David

You can use this SQL custom field

SELECT a.id, a.title FROM jos_osmembership_plans AS a INNER JOIN jos_osmembership_subscribers AS b ON a.id = b.plan_id
WHERE b.user_id = [USER_ID]

However, there is not a way to handle it for not logged in user because in that case, there is no real User ID to get plans for them

Tuan

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

David Unwin
Topic Author
Offline
Platinum Member

More

2 years 4 days ago #157692 by David Unwin

Replied by David Unwin on topic Events Booking Custom Field - Assigning using SQL

Hi Tuan,
Thank you so much for the code. I don't think I could have worked that out for myself
I have attached a screen shot of the code substituting your prefix for ours.
I assume for non-registered users, my custom field will be blank, is this correct? If so that is fine.
I have also set access to Super User so that our users will not see that on registration forms. Is that the correct way to stop the field being displayed?
Kind regards
David

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

Tuan Pham Ngoc
Offline
Administrator

More

2 years 4 days ago #157693 by Tuan Pham Ngoc

Replied by Tuan Pham Ngoc on topic Events Booking Custom Field - Assigning using SQL

You would need to try it yourself to see if it works for your need

If users don't see the field on subscription form, the data for that field won't be recorded for the subscription until Super users edit the subscription record, select the value and save the subscription again

The SQL code could be improved a little :

Code:

SELECT DISTINCT a.id, a.title FROM jos_osmembership_plans AS a INNER JOIN jos_osmembership_subscribers AS b ON a.id = b.plan_id
WHERE b.user_id = [USER_ID] AND b.user_id > 0

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

David Unwin
Topic Author
Offline
Platinum Member

More

2 years 4 days ago #157695 by David Unwin

Replied by David Unwin on topic Events Booking Custom Field - Assigning using SQL

Hi Tuan,
I have tried your code, but does not appear to work.
Is there a problem with the 'a' in this part of the code (jos_osmembership_plans AS a INNER JOIN jos_osmembership_subscribers) ' Should it be a. ??

Kind regards
David

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

Tuan Pham Ngoc
Offline
Administrator

More

2 years 3 days ago #157698 by Tuan Pham Ngoc

Replied by Tuan Pham Ngoc on topic Events Booking Custom Field - Assigning using SQL

Sorry David. The SQL command needs to be adjusted a litle

Code:

SELECT DISTINCT a.id AS value, a.title AS text FROM jos_osmembership_plans AS a INNER JOIN jos_osmembership_subscribers AS b ON a.id = b.plan_id
WHERE b.user_id = 413 AND b.user_id > 0

Tuan

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

David Unwin
Topic Author
Offline
Platinum Member

More

2 years 3 days ago #157702 by David Unwin

Replied by David Unwin on topic Events Booking Custom Field - Assigning using SQL

Hi Tuan,
No need to apologise....you are helping me beyond normal support.
I tried your your new code but this creates an error "0 - Call to a member function getName() on null" (see my screen shots)
Your latest code has "WHERE b.user_id = 413" . This looks like it is looking for a particular user id??
Kind regards
David

Attachments:

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

Tuan Pham Ngoc
Offline
Administrator

More

2 years 3 days ago #157704 by Tuan Pham Ngoc

Replied by Tuan Pham Ngoc on topic Events Booking Custom Field - Assigning using SQL

I have no clue about the error you are having. For this, please submit a support ticket sending me super admin account of the site and link to the page which I can see the issue. I will check to see why it happens and get it sorted

Tuan

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

David Unwin
Topic Author
Offline
Platinum Member

More

2 years 3 days ago #157710 by David Unwin

Replied by David Unwin on topic Events Booking Custom Field - Assigning using SQL

Thanks Tuan,
Support ticket submitted #128021
Kind regards
David

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

Tuan Pham Ngoc
Offline
Administrator

More

2 years 3 days ago #157714 by Tuan Pham Ngoc

Replied by Tuan Pham Ngoc on topic Events Booking Custom Field - Assigning using SQL

OK David. Replied you via support ticket. Please continue communicating from there.

Regards,

Tuan

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

Moderators: Tuan Pham Ngoc

Powered by Kunena Forum

Events Booking Custom Field - Assigning using SQL

Support

Documentation

Information

Connect Us