Can I be cheeky? Is it possible to get the subscription plan name in the results, in addition to the plan's ID#?
Certainly, please see the below query.
Only active subscriptions:
[code:1]
SELECT a.`id` AS ID
, a.`name` AS Name
, a.`username` AS Username
, a.`email` AS Email
, c.`plan_id` AS Subscription
, d.`name` AS Plan
, c.`subscription_date` AS Subscribed
, c.`last_renewed_date` AS Renewed
, c.`expiry_date` AS Expires
FROM `jos_users` AS a
INNER JOIN `jos_comprofiler` AS b
ON b.`user_id` = a.`id`
INNER JOIN `jos_cbsubs_subscriptions` AS c
ON c.`user_id` = b.`user_id`
INNER JOIN `jos_cbsubs_plans` AS d
ON d.`id` = c.`plan_id`
WHERE a.`block` = 0
AND b.`confirmed` = 1
AND b.`approved` = 1
AND b.`banned` = 0
AND c.`status` = 'A'
[/code:1]
All subscriptions with status as text:
[code:1]
SELECT a.`id` AS ID
, a.`name` AS Name
, a.`username` AS Username
, a.`email` AS Email
, ( CASE c.`status` WHEN 'A' THEN 'Active' WHEN 'R' THEN 'Unpaid' WHEN 'X' THEN 'Expired' WHEN 'C' THEN 'Unsubscribed' WHEN 'U' THEN 'Upgrade' WHEN 'I' THEN 'Invalid' ELSE 'Unknown' END ) AS Status
, c.`plan_id` AS Subscription
, d.`name` AS Plan
, c.`subscription_date` AS Subscribed
, c.`last_renewed_date` AS Renewed
, c.`expiry_date` AS Expires
FROM `jos_users` AS a
INNER JOIN `jos_comprofiler` AS b
ON b.`user_id` = a.`id`
INNER JOIN `jos_cbsubs_subscriptions` AS c
ON c.`user_id` = b.`user_id`
INNER JOIN `jos_cbsubs_plans` AS d
ON d.`id` = c.`plan_id`
WHERE a.`block` = 0
AND b.`confirmed` = 1
AND b.`approved` = 1
AND b.`banned` = 0
[/code:1]