Retrieve a list of users who haven’t made any activity in the last x months

I just got an interesting question from an AppGini user:

Is there an SQL query that I can run manually in PhpMyAdmin that will show me a list of all users that haven’t logged into AppGini for the last 6 months please? Even if it is using one of the date fields in the membership_users table to show me a list of all users that haven’t added or changed a record for the last 6 months, that would suffice.

Well, AppGini doesn’t currently (until AppGini 5.72) store the login date/time of users (but we plan to add this in future releases). So, what we can do is run a query to retrieve non-active users (with no insert/edit activity in the last 6 months).

First, we need to create a new view in MySQL to list users’ last activity:

Now, you can use the view ‘members_activity’ that we defined above like you use any table (except it’s not actually a table, but it makes our life easier when performing complex queries). So, to retrieve all users who have no insert/update activity or whose last insert/update activity is more than 6 months old:

But the above query might retrieve new users who joined less than 6 months ago … to exclude those, we should add another condition:

Prepare a LAMP web development server on Amazon EC2

Here is a quick checklist for setting up LAMP development server on Amazon EC2. I used a standard Amazon Linux AMI on a T2.micro instance, which costs about $9/month at the time of writing this.

  1. Edit the DNS zone for your domain to add an A record for a sub-domain to point to the public IP of the instance. I recommend setting the TTL to 60 for fastest updates of IP later if necessary.
  2. Use putty or ssh to log in to the instance.
  3. Follow the instructions at https://gist.github.com/aronwoost/1105007
  4. Test the server by visiting it on your browser using either the public IP or the url.
  5. Prepare web directory: A useful guide for permissions. The Apache user is “apache”, member of group “apache”.
  6. Prepare HTTPS: for Amazon Linux, this is already set up … but for more info, see http://wiki.centos.org/HowTos/Https
  7. Install Codiad (an online code editor/IDE — this enables you to write your code anywhere rather than being bound to your workstation and your desk):
  8. Browse to servername/edit to set up Codiad. Once done, it’s now ready to use. from that same location.

Tip: need to set up multiple virtual hosts on the server? Amazon Linux is based on CentOS. Here is a step-by-step guide on setting up virtual hosts on CentOS. A similar guide for SSL virtual hosts.