countdown days

[gmol]

hey, i need some help, i want to make a field that countdown along with the days, someone know's how to do this?. Thanxs

[Old Pedant]

You want to do this in MySQL???

MySQL is a database system. How would the user ever see the data, even if we could do it in MySQL?

I suspect this should be in the JavaScript forums, but can't be sure.

[gmol]

I'm going to explain better. I'm trying to do a suscription system that a person will pay a mount of money monthly, so the thing is that i'm trying to find the way to countdown the days. Thanxs

[Fumigator]

Explain again.

You want to know how many days until a subscriber's fee is due?

You want to know how many days ... what?

[Old Pedant]

And do you want the countdown to appear on some page (every page?) that the user visits? Or you just want the information for your own use?

[gmol]

Ok i'm gonna explain much better. I'm making a website where the people will register and pay a X mount of money for 1 or more months to play in a game server, so the months will be in a field and it should look like this:

Name gmol
ID XXXXX
subscription 3(3 months)

So the thing is that i don't know how to make the field subscription countdown automaticly.

Thanxs.

[Fumigator]

OK I give up-- why do you need the subscription field to countdown automatically?

Here's what you do: Store the date each subscription is going to end. Then, every day, check the entire table to see if any subscriptions are ending today, and take appropriate action.

With this end date, you can at any time calculate the number of days left on the subscription:


SELECT end_date - CURDATE()
FROM table1

[gmol]

Well, i almost get, this is the solution:

SELECT ID, DATE_FORMAT(subs_date,'%d/%m/%Y') AS subs_date, DATE_FORMAT(CURDATE(),'%d/%m/%Y') AS today, DATE_FORMAT(ADDDATE(subs_days, INTERVAL 90 DAY),'%d/%m/%Y') AS final_date, TO_DAYS(ADDDATE(subs_days, INTERVAL 90 DAY))-TO_DAYS(CURDATE()) AS remai_days FROM players WHERE id = 1

But i have another issue, when i execute the sentence everything appears to work fine:

+----+----------------+------------+-------------+----------------+
| ID | subs_date | today | final_date | remain_days |
+----+----------------+------------+-------------+----------------+
| 1 | 04/05/2009 | 14/05/2009 | 02/08/2009 | 80 |
+----+----------------+------------+-------------+----------------+
1 row in set (0.00 sec)

But when i check the table nothing changes:

mysql> select * from players;
+----+-------+------+------------+----------------+---------------+
| ID | NAME | today | subs_date | final_date | remain_days |
+----+--------------------+------+------------+-------------------+
| 1 | gmol | 2009-05-14 | 2009-05-04 | 0000-00-00 | 0 |
+----+-------+------------+------------+------------+-------------+
1 row in set (0.00 sec)

Some idea?
Thanks.

[Fumigator]

I messed up my query-- you need to use datediff().


SELECT datediff(final_date, CURDATE())
FROM players


Don't store today's date in the table-- what are you going to do, update that every day?? Today's date is available as CURDATE().

Don't store remain_days in the table either-- when you need to calculate remain_days, you can do so simply using the query above. If you store remain_days in the table, then every day you'll have to run an UPDATE on every row in the table to update that column. Bad idea.

And yes, your table didn't change, because a SELECT query will never change anything in a table; you need to run an UPDATE query to change the data.

[Old Pedant]

So to summarize:

Your table could have these fields:

Table : players
id : int
name : varchar( )
subs_date : datetime
subs_number_of_days : int

You could use final_date in place of subs_number_of_days, up to you. But I think it's easier to understand this way: A user subscribes on 20 May 2009 and pays for 90 days. Why bother to figure out the "final date" and store that in the DB?

So all you need to do to find out how many days remain is

SELECT id, name, datediff( dateadd( subs_date, subs_number_of_days ), curdate() ) AS days_remaining
FROM players
ORDER BY days_remaining
HAVING days_remaining <= 10

that will give you a list of all subscriptions expiring in the next 10 days, with names, in order of those closest to expiration.

Oh...and if the days_remaining is negative, then the subscription has already expired.

But even if you decide to store final_date in the DB, listen to Fumigator: DO NOT store days_remaining in the DB! It's useless information and it's wrong tomorrow.

[gmol]

Thanks a lot Fumigator and Old Pedant the sentence works almost perfectly and i forgot 2 things to say.

1) I don't have much experience in mysql.

2) I make a C++ plugin for the game server that check every user and if he can join in the server, so the plugin check the days remaings of the user and if days_remaings >= 1 the user can play in the server else he will be kicked.

But i still confused and if someone knows a better way to do this i'll apreciate it.

Thanxs.

[Old Pedant]

Looks to me like you have it right, now. You shouldn't really need a C++ plugin, as you could easily handle all this in your PHP code, but having the plugin is okay.