Expired users?

How do i see the expired users in an overview?

 you mean suspended users ?

I mean , i see once in a while expired mebers in an email overview.

When i count the active members i have a difference of 20 members less , so there are 20 expired mebers but i can't see them in a list or something?

www.community4business.com

Not sure if I understand correctly but could it be that 20 people have no membership at all.

The following query gives you an overview of all profiles statuses:

SELECT `Status` , count( `Status` )
FROM `Profiles`
GROUP BY `Status`

If you want to know which users are not linked to a membership level run the following query:

SELECT *
FROM `Profiles`
WHERE `ID` NOT IN (
    SELECT `IDMember`
    FROM `sys_acl_levels_members`
)

According to the docs a expired member goes back to standard member but i don't see any standard members

Only the ones in my attachment

In the sys_acl_levels_members table are only members that have some sort of subscription. If they do not belong to any of those groups, I don't believe they are counted.

Did you run the two queries?

What was the outcome?

i'm now on the job and can't execute the queries, tonight when i'm home i can.

SELECT `Status` , count( `Status` )
FROM `Profiles`
GROUP BY `Status`

Outcome : Active 261

-------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT *
FROM `Profiles`
WHERE `ID` NOT IN (
    SELECT `IDMember`
    FROM `sys_acl_levels_members`
)

Outcome : see Query2 picture added

In the overview you see :

206 basislid

26 Coach

4 produktpartner

5 premiumlid

Where is that noted in the tables? I dont see a column in profiles where they put that in?

It comes from sys_acl_levels_members.

but I assume you found your 20 missing members?

Ps. you might want to remove that screenshot ;)

No i didn't find them yet , i think they set to standard members but you can't see in the tables what kind of type you are :

standard, basis etc...

in the acl is only a declaration of the levels but connected with the members or am i wrong about this?

the screenshot of Query2 is the list of people who are registered to your site, but do not have a membership type assigned. Those should be the profiles you're missing.

I don't seems that way , let me rephrase my question , where can i see in the table what the status is of one member.

So when i query to see member status (Standard,basic,premium,Productpartner of Kennispartner)

The following query will show all defined memberships and the no of people who are assigned to that membership:

SELECT `al`.`Name`, count(`IDMember`) FROM `sys_acl_levels_members` `lm`
RIGHT JOIN `sys_acl_levels` `al`
    ON `lm`.`IDLevel` = `al`.`ID`
GROUP BY `IDLevel`

The total no of people assigned to these groups is the same as the outcome from the following query:

SELECT count(`IDMember`) FROM `sys_acl_levels_members`

The total no of people registered at your site is:

SELECT count(`ID`) FROM `Profiles`

The difference in numbers between the last two queries is the total no of people registered to the site, who don't have a membership assigned to them.

To answer your question, the table where memberships are stored is the `sys_acl_levels_members`. The field IDLevel represents the type of membership and the membership types are stored in the table sys_acl_levels

Great now i have all members including Level , thnx thnx thnx