Referral Program for Website

influx

Hey all,

I am currently in the works of making a 'referral opportunity' program for my website. I have about 800 users signed up and am offering them premium features for six months when they refer four or more people to my website.

My question is this: I have never worked with dates too in depth with PHP, but I was wondering, once I log four referrals, I will record the date for the user, something like:

date(m.d.Y);

Let's say, they finish four referrals on 05.20.2005...what would the code look like to compute the date six months from then (11.20.2005)? Probably a stupid question but I have no experience with this...sorry =(

Thanks,
influx

LoganK

Isn't it date("m.d.y"); ?

In any case - you might to look at strtotime() and mktime().

drew010

you would be best off using timestamps. that way everything is dealt with as one number. Six months may be looked at as 15552000 seconds and you can do simple subtraction to determine if the value is less..

<?php

$six_months = 15552000;

$signup = strtotime("May 4th 2005 12:33:24 GMT");

if (time() - $signup <= $six_months) {
  //the time in question was less than six months ago
} else {
  //not quite
}

and in mysql it makes it very easy to query like

SELECT * FROM referrals WHERE unix_timestamp() - signuptime <= $six_months WHERE username = 'someuser';

with that you can grab all the details on referals for someuser that are less than six months old.

influx

Sounds great. Simple enough.

Okay, so the function time(); returns the number of seconds since the 'unix epoch' on January 1st, 1970.

and the function strtotime("May 4th 2005 12:33:24 GMT"); will return the number of seconds since the unix epoch to may 4th 2k5.

Since I have that straight, I am not able to do the following:

$signup = strtotime(date("m.d.Y"));

That returns -1.

How would I have to save the unix_timestamps in my database (i.e: when someone reaches the four referral mark and I record the date, how should I record it to be able to convert it using strtotime later)?

I honestly don't care about accuracy to the second, I want it to be within a day or two...So, what is the simplest way to save it into the database as a date?

Thanks! I really like your approach.
-influx

influx

I think I got it...I am using:

$signup = strtotime(date("d F Y"));

The only thing that is bugging me right now is that I have so many different date formats in my database (meaning, for 'register_date' I have m.d.Y, for 'last_login' I have d F Y, etc...I was hoping I could use one of the old ones for this as well, just for the sake of consistency. But, whatever)

So, the strtotime(d F Y) approach won't be as accurate as what you had, right? It will give me the unix_timestamp to the first second of the day, right?

Thanks...really easy approach =)

Weedpacket

Try using the international standard date format: Y-m-d (e.g., 2005-07-23). strtotime() recognises it, it's what any halfway decent DBMS uses (including MySQL) internally to represent dates, and it also sorts in the natural way. (And if you're storing dates in a database, it's probably a good idea to use a date type for the column).

And since not all months are 15552000 seconds long (in fact most aren't) you'd probably get more accurate results from

strtotime('+6 months', $signup);

influx

What is the difference between storing it as a DATE type in the database as opposed to a varchar(10)?

If so, should I set the default date to 0000-00-00 and have it a max 10 characters?

influx

I should probably leave it as NULL

drew010

if you are using timestamps, dont use a date type, just use int type.

influx

but im going to be saving it as a date (i.e: 2005-07-25) and will later be using

$expire = strtotime('+6 months', $date);

to figure out whether or not it has been six months.

drew010

strtotime takes a timestamp as the optional second argument.

influx

=)...now that I realize it that's probably better.

But answer this for me, what is the advantage of using DATE instead of VARCHAR when storing dates? How does it benefit?

thanks.

drew010

using DATE you are able to use mysql built in date functions, and also since the different DATE formats in mysql exist, they will automatically convert between them for you as needed. for example your date column is set up as YYYY-MM-DD and you insert in the form of MM-DD-YY mysql will reformat it.
you can see all the functions available to you by using a date or time type at http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

influx

Thanks a lot.

But how does MySQL know I want it formatted in YYYY-MM-DD? If I have no default entry and choose 10 characters...how would it differentiate?

drew010

its default behaviour...

mysql.com wrote:
The DATE type is used when you need only a date value, without a time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.