1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. [SOLVED] Display names of users who have used a family plan coupon?

[SOLVED] Display names of users who have used a family plan coupon?

9 years 7 months ago - 9 years 6 months ago #249514 by peterbatchelor
[SOLVED] Display names of users who have used a family plan coupon? was created by peterbatchelor
I'm implementing the "Coupon Generation for Family Plan Subscription" described in www.joomlapolis.com/support/tutorials/107-use-cases/18407-purchasing-discount-coupons

This is being done for a teaching association, where the "parent" subscriber wants to know who has used the coupons.

The code below lets them see the user IDs of people who have used the coupons, but not their names.

SELECT p.`coupon_code`, p.`max_uses_total`, ( SELECT COUNT(*) FROM `#__cbsubs_promotions_uses` AS c WHERE c.`promotion_id` = p.`id` ) AS count, ( SELECT group_concat(d.`user_id` separator ', ') FROM `#__cbsubs_promotions_uses` AS d WHERE d.`promotion_id` = p.`id` ) AS idlist FROM `#__cbsubs_promotions` AS p WHERE p.`coupon_code` REGEXP '^[user_id]-'

How can I change this code to get the table to show either the coupon user's name (I guess from users) or the "name" info from cbsubs_promotions (which is probably better as it includes their name, email address and subscription date)?

Thanks, Peter

(Community Builder 1.9.1, CBSubs 3.0.0, Joomla 2.5.25)

Please Log in to join the conversation.

9 years 6 months ago #249733 by nant
Replied by nant on topic Display names of users who have used a family plan coupon?

peterbatchelor wrote: I'm implementing the "Coupon Generation for Family Plan Subscription" described in www.joomlapolis.com/support/tutorials/107-use-cases/18407-purchasing-discount-coupons

This is being done for a teaching association, where the "parent" subscriber wants to know who has used the coupons.

The code below lets them see the user IDs of people who have used the coupons, but not their names.

SELECT p.`coupon_code`, p.`max_uses_total`, ( SELECT COUNT(*) FROM `#__cbsubs_promotions_uses` AS c WHERE c.`promotion_id` = p.`id` ) AS count, ( SELECT group_concat(d.`user_id` separator ', ') FROM `#__cbsubs_promotions_uses` AS d WHERE d.`promotion_id` = p.`id` ) AS idlist FROM `#__cbsubs_promotions` AS p WHERE p.`coupon_code` REGEXP '^[user_id]-'

How can I change this code to get the table to show either the coupon user's name (I guess from users) or the "name" info from cbsubs_promotions (which is probably better as it includes their name, email address and subscription date)?

Thanks, Peter

(Community Builder 1.9.1, CBSubs 3.0.0, Joomla 2.5.25)


Sorry for the delay.

Yes you would need to basically alter the query to do a join with another table in order to grab the username.

I am not that hot SQL wise, so I have referred this to Kyle or Beat to assist when they get a breather.
--
Nick (nant)
Community Builder Team Member
Before posting on forums: Read FAQ thoroughly + Read our Tutorials + Search the forums
For more add-ons and support: Upgrade your membership
Links: Community Builder - Languages - Adv/Pro/Dev membership - CBSubs Paid Subscriptions - GPL Templates - Hosting

Visit my CB Profile - Send me a Private Message (PM)

Please Log in to join the conversation.

9 years 6 months ago #249807 by peterbatchelor
Replied by peterbatchelor on topic Display names of users who have used a family plan coupon?
Thanks Nick,
I look forward to hearing from them...
Peter

Please Log in to join the conversation.

9 years 6 months ago #249905 by krileon
Replied by krileon on topic Display names of users who have used a family plan coupon?
The below sub-query of that usage would need to be adjusted.

( SELECT group_concat(d.`user_id` separator ', ') FROM `#__cbsubs_promotions_uses` AS d WHERE d.`promotion_id` = p.`id` ) AS idlist

The below for example should work.

( SELECT group_concat(u.`username` separator ', ') FROM `#__cbsubs_promotions_uses` AS d LEFT JOIN `#__users` AS u ON u.`id` = d.`user_id` WHERE d.`promotion_id` = p.`id` ) AS idlist

The above should output a comma separated list of usernames instead of user ids for example. You can change u.`username` to u.`name` for example if you'd like name. It can also be changed to u.`email` for the email address.

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.

9 years 6 months ago #249917 by peterbatchelor
Replied by peterbatchelor on topic Display names of users who have used a family plan coupon?
Thanks Kyle,
that's perfect for what they need.
Peter
The following user(s) said Thank You: nant

Please Log in to join the conversation.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Developer Members Support
  7. [SOLVED] Display names of users who have used a family plan coupon?
Time to create page: 0.171 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Developer Members Support
  5. [SOLVED] Display names of users who have used a family plan coupon?

Search

Login / Logout

User Menu