PHP User expiration database

[SamDOD]

i want to login, me only. enter a user name their password and press activate.

 

activate would set a timer for a year. a week before a year is up i want it to send an automated email to me and that person saying that their time is almost done.

 

eventually i would like it to get the names from my database and further automate this.

 

Am using PHPBB but it does not have a built in expiration for users in a group.

 

Is any of this possible?

 

Thank you for your time.

[allworknoplay]

You're going to need something like a cron job that will run a PHP script to check for expirations, and then send notificiations if it's queries are successful....

[SamDOD]

can the cron job run with PHPBB or does it need to be a separate site? Do you have or know where i can find example of how to do this?

[allworknoplay]

No, cron you will need access to your server to run.

 

There is phpcron, but that only runs when someone loads a page on your website, you can't count on it

to run all the time. If no one goes to your site, it doesn't run....

[SamDOD]

I have access to my server.

 

Can the cron job effect the PHPBB forum?

 

as in change someone out of a group after a time?

 

 

 

[allworknoplay]

  Quote

I have access to my server.

 

Can the cron job effect the PHPBB forum?

 

as in change someone out of a group after a time?

 

 

 

 

Well yes you can do that, but it has nothing to do with PHPBB.

 

You write your PHP program to access your DB, and based on whatever conditions you want, you update the DB...

 

I'd probably make the cron run once an hour...depending on how often you need updating..

 

[SamDOD]

i dont suppose there are easy ways to make them work. I have never used one and i don't do allot of coding myself.

[allworknoplay]

  Quote

i dont suppose there are easy ways to make them work. I have never used one and i don't do allot of coding myself.

 

It's pretty easy, just do a little research on setting up cron jobs on our linux server. I assume you are using linux?

 

Then the cron will kick off your PHP program. You are going to need to know how your DB is designed in order to make the correct adjustments to your users.....

[gizmola]

If you do a forum search I recently answered a question that involved cron that has some information in it.  Just to be clear, your cron job would be a daily job, so you'd set it up to run once a day. 

 

It would run a script that would need to query your phpBB database and use the joined date, and send the email.  It might also require some other query criteria.  You would write a CLI php script that would connnect to your mysql database and query the ppBB user table using the date criteria you need.  There's also an article on my blog about doing date arithmetic that will probably help with the aging. 

 

http://www.gizmola.com/blog/archives/51-Exploring-Mysql-CURDATE-and-NOW.-The-same-but-different..html

 

Off the top of my head the table is named phpbb_users (the phpbb_ prefix is something you set when you install but most people don't change the default).

 

From there you should be able to get a result set, and cycle through the list using the php mail() command.  One thing I will warn you about -- make sure that you have your email server stuff in order and it is probably best if you have the mail sent from that server.  What i mean is that, these days unless your mail server is "legitimate" which means at very least that it has a valid reverse dns lookup, an mx record, and an SPF entry, you will find that your attempts to mail from your server directly will result in a lot of bounces if not outright refusals.  Also for efficiency sake it's best if you send the mail in an MTA and have that handle the sending, since they all handle resending and retrying mail that can't initially be delivered.

 

Otherwise, what you want to do is a relatively simple job.  The only thing I didn't address was the question of how individual rows would be "marked" as you indicated. 

 

[SamDOD]

I definitely appreciated what you have posted. I wish it was as simple to me as it seem to be for you.

 

The hard part seems to be in the fact that i dont want to start the time until they decide and/or are able to join a certain group. What i mean is when I add them to a members only group is when i want the time to start not when they join the forum. Though i don't know how to do that or do to what you have stated above either. will keep trying.

 

Any help and/or resources would be extremely appreciated.

 

Thank you.

[allworknoplay]

  Quote

I definitely appreciated what you have posted. I wish it was as simple to me as it seem to be for you.

 

The hard part seems to be in the fact that i dont want to start the time until they decide and/or are able to join a certain group. What i mean is when I add them to a members only group is when i want the time to start not when they join the forum. Though i don't know how to do that or do to what you have stated above either. will keep trying.

 

Any help and/or resources would be extremely appreciated.

 

Thank you.

 

Easy, you just need to have a column in the user table or in another table that shows a timestamp of when they became part of the "members only" group...

 

So really, instead of looking at when they JOINED the forum,  you just simply look at when they became part of the Members group....

 

[gizmola]

allworknoplay's answer is correct if we assume that phpBB gives us all the tables needed.  I don't have a version of phpbb3 to look at at the moment, but in phpbb2, there's a key ingredient missing.  phpbb2 does have a table that could be joined to _users called _user_group that has a row created whenever a user is added to a group, however it does not have a datetime column.

 

You could probably safely modify this table to add a timestamp column (this assumes you're using mysql database).  In mysql when you have a timestamp column, it will be set to the current date/time when that row is created.  Theoretically adding a column called "created" of type "timestamp" using the Alter table syntax will not have any effect on the functionality of phpbb, and would then give you what you needed.  Your SQL statement would then need to join the two tables together, but everything else will work.  Of course since none of your existing members will have those rows, you will need to fill those with a value to start with, however new people that are added to the group should automagically get the timestamp value set when the row is added.

 

 

[allworknoplay]

  Quote

allworknoplay's answer is correct if we assume that phpBB gives us all the tables needed.  I don't have a version of phpbb3 to look at at the moment, but in phpbb2, there's a key ingredient missing.  phpbb2 does have a table that could be joined to _users called _user_group that has a row created whenever a user is added to a group, however it does not have a datetime column.

 

You could probably safely modify this table to add a timestamp column (this assumes you're using mysql database).  In mysql when you have a timestamp column, it will be set to the current date/time when that row is created.  Theoretically adding a column called "created" of type "timestamp" using the Alter table syntax will not have any effect on the functionality of phpbb, and would then give you what you needed.  Your SQL statement would then need to join the two tables together, but everything else will work.  Of course since none of your existing members will have those rows, you will need to fill those with a value to start with, however new people that are added to the group should automagically get the timestamp value set when the row is added.

 

 

 

Yup and since we're dealing with unix timestamp, you can use PHP's time function.

 

$now_time = time();

 

 

Put the $now_time into the table when the user joins a certain group.

 

 

 

[gizmola]

Actually, that would not be needed, because the property of a mysql timestamp column is that when the row is created, mysql sets the value to be the same as the server time ie. the unix time.  So you don't need to write a single line of code --- mysql will do this for you.  You can think if it as a built in insert/update trigger that runs inside mysql.

[SamDOD]

All of this is great. the information given seems great. Is there a tutorial or an example of this that i can play with?

 

I am not well versed in PHP or MySQL. have done work in both but not extensively. I mean i understand exactly what you are saying about how it should and can work just not exactly how to get it done just yet.

 

I do appreciate all the help and information given. Thank you all.