datetime+ 6months

[edwoodt]

hi, i'm using phpmyadmin & would like to create an additional field in the following table.

Field // Type Collation Attributes Null Default Extra Action
user_id // int(10)
first_name //varchar(20)
last_name // varchar(40)
email // varchar(80)
pass // char(40)
user_level //tinyint(1)
active // char(32)
registration_date// datetime

the field would be

subscription_end //datetime + 6 months

I would like this date to equal (registration_date + 6 months)

my phpadmin shows this formula at the bottom of the table...
Add 1 field(s) At End of Table After registration_date
before i click "GO" it would be nice if i knew how to configure the "datetime + 6 months" Does this exist? thanx for your help.

[edwoodt]

i have been looking here
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add
....

[smantscheff]

Storing computable values is never a good idea, except for caching and performance reasons.
You could define a query or a view instead like as

select *, date_add(registration_date, interval 6 month) as subscription_end from mytable;

[varmadba]

why don't you try inserting subscription_end by computing value before firing insert
as you will have registration_date value you can compute subscription_end by
DATE_ADD(registration_date, INTERVAL 6 MONTH)

[edwoodt]

first of all, i would like to thank smantscheff (an intelligent person working in a restaurant owned by a sibling of one of his or her parants) :icon_wink:, and varmadba http://www.sqllibrarian.info/

I suprised myself (copied and edited smantscheff's suggestion) by making my first successful query,

SELECT *, DATE_ADD(registration_date, INTERVAL 170 day) as subscription_reminder FROM user;
and then added that field to the database, updated the sole user (me) into the database. in case you're interested my subscription reminder is on 2011-03-17.
oh i also changed datetime to date :cool:

presently, the database prints out (Last Name First Name Date Registered) to a web page, so i guess i'll try to add "subscription_reminder" to that.

but, since i have your attention, could a (database and php?) auto-remind (with an e-mail) either me or the 'user' that his or her subscription is about to end?

in case any of you are wondering, as of today, my website has generated $0.00 which comes to a grand total of 0 euros et 00 centimes at today's exchange rate.

[smantscheff]

As I said before, it's not a good idea to store easily computable values in a database. What for do you need a computed subscription date if the subscription period is stable? It only makes sense if you allow manual changing of the subscription end.

Apart from that, for a subscription reminder you have to set up a periodical process which does the checking. Such a mechanism is neither built into the database nor in PHP. Under linux you can set up a cronjob which regularly executes a script which does the checking. (For Windows, google "cronjob windows"). The script would run along the lines:

$q = myqsl('select * from customers where date_add(now(),interval 1 week) > subscription_end_date');
while ($record = mysql_fetch_array($q))
  mail($record['email'],'Subscription about to end', 'bla bla');

[By the way, are you working for my brother or what do you mean?]

[edwoodt]

wow you work fast

well, i won't be using an automatic mail reminder, i just wanted to know if it could be done. i'm not getting into anymore web development languages. i'm already confused enough. i've been on a crash course trying to learn html, css, and now php, and mangaging all the crap on my web hosting service...i'm up to my eyeballs with all of it. my site is almost ready & i need to concentrate on content & client mailings. as for working for your brother, no: i took your screen name and broke it into syllables. sm=smart ants= aunt's cheff= chef ;o)

[smantscheff]

Off topic: Re "Smart Aunt's Chef": We have a german author, Arno Schmidt, who dealt with this kind of spelling association quite excessively, developing what he called the "Etym theory", an Etym being the core of a word which subconsciously can shift and swap its meaning with similar sounding words. It amounted to that in the end you can suppone any meaning to any text.
The famous comic writer Walt Kelly also practised this a lot, so that when I first read the name "Seminole Sam" of one of his characters I assumed it would have to be the "Same an' Ol' Sam" (not knowing that there is a tribe of that name).
And of course James Joyce with Finnegans Wake. But that is another story...
So your screen name would mean - Ed Wood, tea? A hommage to the infamous movie director?
Good luck with your project...