User date input

[dmccabe]

I have a field in my database where the user will put a date in a form in the following format:

 

DD/MM/YY

 

I will then want to perform calculations on this field against today's date and output the number of days past the date entered.

 

What field type should I choose? and what would be the default value?

 

Also, when wanting to store a large amount of text in a db, again what type should be used?

[ngreenwood6]

I would not store it like that I would store it as an int(11) timestamp in the database. Then when you call it out of the database convert it to the dd/mm/yy.

[dmccabe]

Thanks for the quick reply (god I love this forum).

 

Ok so if I store it as an INT, when the user enters DD/MM/YY how would I then convert to it's INT value?

[ngreenwood6]

Ill give you a basic example. For this example we will assume you have an int(11) field in the database named date_entered.

 

Where you will enter the data.

<?php

$date = time();

$query = "INSERT INTO table (date_entered) VALUES ('$date')";

mysql_query($query);

 

Basically that will put something like "123092434" in the database. That is actually the number of seconds since 1900 or something like that (irrelevant).

 

Then when you pull the data you will do it like this:

<?php
//obviously this is after you have queried for the results
$date_entered = date("m/d/y", $row['date_enterd'])  //this would format it like 12/18/08

 

Hopefully that is understandable

[ngreenwood6]

If you wanted to get the last days results you would do something like this:

 

<?php
$one_day = 60*60*24; //60 seconds times 60 minutes times 24 hours 1 day if you needed more days you would just multiply that times the number of days

$results = $row['date_entered'] - $one_day;

$query = "SELECT * FROM table WHERE date_entered >= '$results'";

 

Again this is just for demonstration purposes.

[Maq]

$date_entered = date("m/d/y", $row['date_entered']);
$today = date("m/d/y");

if(strtotime($date_entered) == strtotime($today))  {
   echo $date_entered . " IS today...";
} else {
   echo $date_entered . " IS NOT today...";
}

[fenway]

I would not store it like that I would store it as an int(11) timestamp in the database. Then when you call it out of the database convert it to the dd/mm/yy.

Or, store it like a date, since it ACTUALLY IS A DATE.