[RESOLVED] Date help

chinclub

I am trying to figure out how to only let members who have been there at least 8 weeks go to a certain page otherwise it gives an error. The date a member joined is saved in the database like this:

03/26/07

I have been looking through tutorials for a way to code it but everything I find about PHP and dates just tells me how to get the current date.

I know I need to somehow add 8 weeks to the "born" date and then make sure that is > or == todays date. Can anyone help?

hacku69

u can use date('d m y',time())

it will echo datenow

Piranha

You need to use a date filed in the database instead. And it will probably be better to do this in the query. Look in the manual for MySQL and PHP to see how date is working.

chinclub

I don't think I explained my question well, sorry.

Each member already has the date they joined in the database its $borndate. And I know how to get the present date.

What I want the script to do is get the member's $borndate (the date they became a member which is saved in the database in this format 03/26/07)

I want it to compare that date to the current date. If they have been a member for 8 weeks or more it forwards them to the next page. If not it gives an error.

I just don't know how to have the script figure out how old the account is from the $borndate in the database.

Piranha

To do that you need to convert the date in the database from a string to a date field. Then it is easy to solve the problem. If you don't convert it you have trouble.

chinclub

I finally found a way that worked! I discovered this code for telling future dates and changes it so that it would give the date of 2 months ago like this:

$age = mktime(0, 0, 0, date("m")-2, date("d"), date("y"));

I sent that date as a hidden value of a form along with the date the membership started. From there I was able to compare the two dates with a simple

if($date > $age)

That was the piece of code I was missing!! Thanks anyway. 🙂

Piranha

So which is latest according to your code, the 5th of April 2007 or the 27th of March 2007? How about the 5th of March 2007 and 27th of April 2007? I am asking because string comparison probably won't work, but then I don't know if you do a string comparison.

chinclub

It is comparing in month/day/year format so

3/30/07 < 5/30/07

Hopefully it will work. I tested a few different dates and it worked. Its the best I could come up with.

Piranha

Oh, it's month/day/year. I thought it was day/month/year. Then try this and see what is first:

01/30/06 or 12/30/05

chinclub

Yep, there is the error. Well, Maybe this one is just a little over my head in my limited PHP knowledge.

Piranha

As I tried to say at once, it is because you use a string and not a date. The best solution is to handle data of the type "date", then you won't have this problem.

chinclub

I don't understand the difference.

bradgrafelman

Piranha is suggesting that you change the column in your DB to be a DATE column, not just a VARCHAR column with some string data. The DATE column specifically holds dates in the YYYY-MM-DD form.

Why does the form matter? Because a) you're dealing with a date, so why wouldn't you use a DATE column type?, and b) comparing the YYYY-MM-DD form of dates as simple strings will work as expected.

Piranha

When comparing a string it start with the first sign and compare those, then go on to the next sign. It will mean trouble. Plus that it is possible to insert dates that don't exist (say 01/31/07 or 44/44/44).

When using the date format you get a check that the date is valid. But it also comes with all kinds of functions that can count days, weeks, months or whatever time interval you need. It can also take a date and say what week day it was. But above all, when comparing two dates it will always be right.

To get a little more information look here and here.

chinclub

Oh, I see!! That would have been great to know earlier. :p Unfortunately, I am building onto an existing site and I already have 872 members in the database.

I'm sure it is too late to make that change now.

bradgrafelman

It's never too late to do the right thing! :p You can convert your DB:

Create a new "test" column.

Run a query such as this one:

UPDATE myTable SET borndate_test = STR_TO_DATE(borndate, '%m/%d/%y')

Drop the old column.
Rename the new "test" column to have the same name as the old column.
Update your SQL queries/etc. in your script(s).

I would of course recommend testing these changes on a "test" server (or at least copy the data to a new DB to test with), backing up your data, etc. To smooth out the process even further, you could make copies of your script(s) and edit the SQL statements to reflect the new column type. That way, as soon as you make the DB change, you simply upload the new file(s) and everything's up and running.

chinclub

I can't believe how easy that was. Thank you so much for taking the time to explain this to me. I still have so much to learn about SQL!