1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. [SOLVED] Export users and subscription info

[SOLVED] Export users and subscription info

6 years 1 week ago - 6 years 6 days ago #303816 by marse7
[SOLVED] Export users and subscription info was created by marse7
I have been using CB Subs for 5 years on several websites. My non-profit clients ALWAYS need subscription information. The issue isn't that CB Subs doesn't provide the information in the backend but that these orgs need to give volunteers an Excel sheet with that information on it so they don't have to have people who don't understand website backends access those backends.

I have never seen a suitable solution to this issue. CB Juic isn't that solution AFAIK because it doesn't provide subscription info. That doesn't mean it doesn't exist - but every time I go looking I can't find it.

I've done the set debug on and create a query and copy it. Again, that doesn't cross tables. Subscriptions (I think) doesn't have all the user fields and CB doesn't have subscriptions and expiry dates. Right? Those tables have to be cross-indexed to get the info out (see below info I need.)

How in the world can such a remarkable product like CB and CB Subs NOT provide a simple solution to having certain fields (in different tables I'm assuming) outputted. Why is there not a plugin, addon - anything - to allow us to output specific fields to a CSV file crossing tables? Am I nuts? How hard could it be for the mad geniuses at Joomlapolis to design something like this.

If there is such a beast, PLEASE let me know. If not, can someone please help me design a MySQL query to output the following information?

For current and past due members:

- Name
- Address
- Phone
- Email
- Renewal date or just subscription info

HELP!

Many thanks to everyone at Joomlapolis!
Mark

Please Log in to join the conversation.

6 years 1 week ago #303829 by krileon
Replied by krileon on topic Export users and subscription info
phpmyadmin gives you the tools to do this already with the specific information you need. We can't know what each sites export needs are. We've no idea you need XYZ field. So it can't just be a simple "export this table" because it's likely going to have too much or too little information. This means we need options to customize what you can export. On top of that we've zero exporting API. So we'd need a new API with some sort of GUI functionality to select what to export more specifically.

We've plans for export API, but it's basically just going to be an "export what you see" type situation for the tables you can browse in backend. For deeper exporting/reporting usage you may want to look into Joomla extensions that specifically handle such things by supplying them custom database queries to get exactly the information you need.

Below is an example SQL query to grab subscriber information.

SELECT j.`id`, j.`name`, j.`email`, p.`name` as subscription, s.`status`, s.`subscription_date`, s.`expiry_date`
FROM `jos_cbsubs_subscriptions` AS s
LEFT JOIN `jos_cbsubs_plans` AS p
ON p.`id` = s.`plan_id`
LEFT JOIN `jos_comprofiler` AS c
ON c.`id` = s.`user_id`
LEFT JOIN `jos_users` AS j
ON j.`id` = c.`id`

Replace "jos_" as needed based off your database table prefix. The above provides user id, name, email, subscription, status to subscription (e.g. A = active), subscription date, and expiration date.

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.

6 years 1 week ago #303883 by marse7
Replied by marse7 on topic Export users and subscription info
Thank you for the incredibly helpful response. Also, for the speed of the response. Respect.

This helps me resolve my issue perfectly.

But - the other issue is this: ask any user of CB Subs if they should be able to output user names, phone, email, etc. AND expiration dates, register dates, etc. and I cannot imagine a more universal 'yes.' When I tell my clients there is no easy way to do this, they simply do not believe me.

This is a membership plugin/component. Outputting certain crucial fields is well...crucial. I understand that you have API issues with this but honestly, I am still incredulous that we can't just do this. I wish had the time to do my own CB plugin. It would be #1 with a bullet as they say.

Again, thank you. I am a huge joomlapolis fan and the (always) excellent support is certainly one of the major reasons.

mark
The following user(s) said Thank You: beat, krileon

Please Log in to join the conversation.

5 years 11 months ago #304244 by AdvisorProducts
Replied by AdvisorProducts on topic Export users and subscription info
I couldn't agree more. I myself still have problems believing there is no way to take a screen of filtered results under subscriptions and outputting what you have to a CSV file. Sounds like that much is in the works at least. Would be nice if you could also choose fields you want since you can't see user email addresses under subscriptions. I think most users would agree generating results to CSV is a reasonable expectation for such software without knowing how to code/script to get the info.

Please Log in to join the conversation.

5 years 11 months ago #304252 by krileon
Replied by krileon on topic Export users and subscription info

I think most users would agree generating results to CSV is a reasonable expectation for such software without knowing how to code/script to get the info.

But it's not. Core Joomla doesn't even provide CSV export or import. The vast majority of extensions do not. If we allow exporting everyone will also expect us to allow importing (which we plan to also implement). It's more work than everyone here is thinking. On top of that we've no clue what information you specifically need so it'd be a generic export, but we'll get complaints it can't export someones needs and we're back to square one. It's a massive undertaking that we need to be sure is done right, can be customized, and will work in all our backend table views (so not just CBSubs specific code).

In the mean time there's extensions that can help with this available on JED.

extensions.joomla.org/category/migration-a-conversion/data-import-a-export/

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.

Moderators: beat, nant, krileon
  1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. [SOLVED] Export users and subscription info
Time to create page: 0.236 seconds

Facebook Twitter LinkedIn

    You are here:  
  1. Home
  2. Forums
  3. Support and Presales
  4. Professional Members Support
  5. [SOLVED] Export users and subscription info

Search

Login / Logout

User Menu