hello everyone, i want to create a member list with the following info: %user.user_id% %user.name_f% the products purchased, expired yes / no (per product) is there a way to create a query like this and have the info for all members listed on a separate page so admins can quickly see if a client is expired or not w/o having to consult the amember backend? i attached a sample we are using right now with a different tool thanks, phil
You can use this query to get such results: Code: select u.user_id, u.name_f, u.name_l, a.product_id, sum(if(a.begin_date<now() and a.expire_date>now(), 1, 0)) as active, sum(if(a.begin_date>now() or a.expire_date<now(), 1, 0)) as expired from am_user u left join am_access a using (user_id) group by u.login, product_id Here is example of result: USERID, USER FIRST NAME, USER LAST NAME, PRODUCT_ID, NUMACTIVE, NUMEXPIRED NUMACTIVE - number of active subscription for this product. NUMEXPIRED - number of expired subscription for this product.