1. Forums
  3. Support and Presales
  5. Professional Members Support
  7. sql query

sql query

12 years 8 months ago #172085 by bcs_group
sql query was created by bcs_group
Hi,

I am trying to run an sql query on information in CB and CB Subs. This is what I have so far (below) but it is missing the different subscriptions ie plans. I can see this information in CB under the field "Subscriptions" but I can locate what table it would be in.

Can you help me craft the sql query that will pull this info.

Thanks
Dita

SELECT first_name,last_name,payer_email,contact_phone,item_name
FROM `nozx_cbsubs_payment_baskets`
LIMIT 0 , 30
I need to create a cvs report to download into excel

CB Version: 1.4
CBSubs Version: 1.1.2
CMS Version: Joomla 1.5.x
PHP Version: 5.2.17

Complete Site URL (don't put http:// in front please): www.cafaa.org

Please Log in to join the conversation.

12 years 8 months ago #172194 by krileon
Replied by krileon on topic Re: sql query
What exactly are you wanting in your reporting? Please be very specific as the query would need to be designed based off your needs. Plan ID, Plan Name, User ID, etc... Once details provided would be glad to suggest a query.

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.

12 years 8 months ago #172337 by bcs_group
Replied by bcs_group on topic Re: sql query
Thanks for getting back to me.

I need from CB:
First Name
Last Name
Professional Title
Address
Address 2
City
State
Zip Code
Phone #
Email
Agency/Institution

From CB Subs:
Plan Name

Thanks again

Please Log in to join the conversation.

12 years 8 months ago #172352 by nant
Replied by nant on topic Re: sql query
no subscription start date or end date?
--
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.

12 years 8 months ago - 12 years 8 months ago #172426 by bcs_group
Replied by bcs_group on topic Re: sql query
Yes - subscription begin date 1-1-11 and end date 1-1-12

Please Log in to join the conversation.

12 years 8 months ago #172449 by krileon
Replied by krileon on topic Re: sql query
Am sorry, but your information is not specific enough; I need database column names from your _comprofiler table. For example you requested "Professional Title", but I have no idea what column this is stored in. At any rate have provided the below which will obtain basic information.
SELECT c.`id` AS 'User ID'
, c.`firstname` AS 'First Name'
, c.`lastname` AS 'Last Name'
, u.`email` AS 'Email'
, p.`name` AS 'Subscription'
, s.`subscription_date` AS 'Start Date'
, s.`expiry_date` AS 'End Date'
FROM `jos_cbsubs_subscriptions` AS s
LEFT JOIN `jos_cbsubs_plans` AS p
ON s.`plan_id` = p.`id`
INNER JOIN `jos_comprofiler` AS c
ON s.`user_id` = c.`id`
INNER JOIN `jos_users` AS u
ON c.`id` = u.`id`
WHERE s.`status` = 'A'

The above will only obtain active subscriptions. Please make changes wherever necessary.

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. sql query
Time to create page: 0.215 seconds

Search

Login / Logout

User Menu