[SOLVED] Display names of users who have used a family plan coupon?
- peterbatchelor
- OFFLINE
- Posts: 22
- Thanks: 4
- Karma: 0
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 or Create an account to join the conversation.
- nant
- OFFLINE
- Posts: 25532
- Thanks: 1834
- Karma: 877
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 or Create an account to join the conversation.
- peterbatchelor
- OFFLINE
- Posts: 22
- Thanks: 4
- Karma: 0
I look forward to hearing from them...
Peter
Please Log in or Create an account to join the conversation.
- krileon
- ONLINE
- Posts: 68607
- Thanks: 9108
- Karma: 1434
The below for example should work.
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 or Create an account to join the conversation.
- peterbatchelor
- OFFLINE
- Posts: 22
- Thanks: 4
- Karma: 0
that's perfect for what they need.
Peter
Please Log in or Create an account to join the conversation.