re: automation, yes, I have thought about it, but that will take time to program. Until I (or someone else) gets around to doing that, it will have to be manual, with the added benefit that it will probably be nicer to read ;-) Doing it manually at first will also help us figure out how it should be structured.
Well, I already have some code ready. If you know in which tables of your database to get the relevant information, I can adapt it. Alternatively, I can post the raw script somewhere for your convenience and you adapt it.
Entities I manipulate in my script = users, countries, zones (topics), questions, answers, points (you don't have points in here).
Users have a status (in here, it's the same, like "waiting approval", "member", "revoked" etc)
Users have a "last login datetime" attribute
Users have a rank (in here, it's the same, like "site admin","mentor","site builder","member" etc)
Questions have a status (asked=Open, answered=Closed to be short) - like in here
So the real issue is whether phpBB has a clear database layout that can be mapped to simple SQL queries that match the needs for the newsletter. The rest is only beautifying overall text and email format.
My script is for PHP+mysql, 8KB, 184 lines, commented and readable.
One year ago, it produced reports like this :
EEE.org Aperiodic Account Status Report for 'Bernard'
Account Status :
Login : Bernard
Password : ******** (1)
Created on : 2004-09-17 16:38:34
Last Visit on : 2005-07-20 09:01:25 (3)
You have earned 880 Expert Points by answering Questions
You have thus 240 Question Points available for Asking Questions (2)
Website :
http://www.europeanexperts.org(1) If you forgot your password, there is a mechanism on the website to have it sent to you by email. Click on "forgotten password" when logging in.
(2) Remember you gain 20 Question Points each day you log in. You also get Question Points when answering Questions.
(3) This is shown as the creation date minus one month if you never logged in since you created the account. (this is to initially show the last month's questions as new ones)
You asked 23 Questions, out of which are still Open :
- 'problem in a perfectly valid ALTER TABLE "table must be empty to add mandatory (not null) columns"' ('Oracle' zone, value=30 QPts) asked on 2005-07-20 08:54:59 that received 1 answer on 2005-07-20 09:17:38
- 'Oracle problem with standard CREATE TABLE syntax with INDEX' ('Oracle' zone, value=20 QPts) asked on 2005-07-20 09:01:23 that received 1 answer on 2005-07-20 09:09:02
There were 1 Questions Asked and Answered since your last visit.
Server Status :
Our international community is composed of 44 Experts
from 14 countries around the world
and which were asked 1348 Questions
There are currenttly 19 Open Questions awaiting an Answer from an Expert like You :
- 'SSL certificates : how to know who issued it ?' ('On standby of sorting' zone, value=20 QPts)
- 'oracle table lock mechanism with SELECT ... FOR UPDATE' ('Oracle' zone, value=20 QPts)
- 'Oracle problem with standard CREATE TABLE syntax with INDEX' ('Oracle' zone, value=20 QPts)
- 'problem in a perfectly valid ALTER TABLE "table must be empty to add mandatory (not null) columns"' ('Oracle' zone, value=30 QPts)
- 'Oracle problem between empty string and space' ('Oracle' zone, value=100 QPts)
- 'Setting File Name for the Downloaded Content' ('Java' zone, value=20 QPts)
- 'Survey : Internet web servers (IIS, Apache...), platforms, OS, language used (PHP, ASP...)' ('0_General' zone, value=0 QPts)
- 'What's the minimum PSu power required for an Athlon 64 configuration ?' ('Desktop PC' zone, value=100 QPts)
- 'proposed feature : private messages' ('Feedback' zone, value=0 QPts)
- 'proposed feature : confirmation emails' ('Feedback' zone, value=0 QPts)
- 'proposed feature : translation on-the-fly' ('Feedback' zone, value=0 QPts)
- 'new features' ('Feedback' zone, value=0 QPts)
- 'changing the domain in linux' ('Unix' zone, value=20 QPts)
- 'Best Practices for Form Events' ('VBA (Office)' zone, value=20 QPts)
- 'problem' ('0_General' zone, value=20 QPts)
- 'I would like to see your Opera 7, Safari and IE 6 screenshots' ('Feedback' zone, value=500 QPts)
- 'missing MSI file : how to uninstall anyway ?' ('Windows' zone, value=500 QPts)
- 'get files' disk space data on FreeBSD or regular Unix filesystem' ('Unix' zone, value=300 QPts)
- 'freeBSD journal' ('Unix' zone, value=20 QPts)
This was a message sent by European Experts Exchange (
http://www.europeanexperts.org)
If this is an acceptable basis for an automated newsletter, you know what to do.
Hope This Helps Somehow
