PHP MySQL Date Sorting and More

jaZon

Ok, I've never used any of the date functions of MySQL or PHP. What I am currently doing is storing records in a database and tacking on a date with it. In MySQL I have the date column setup as an INT.

$date = date("Ymd");
$query = "insert into trouble_ticket(username, req_location, req_subject, req_body, req_date) values ('$username', '$location', '$subject', '$body', '$date')";

As you can see I have the date being inserted as YearMonthDate.

Ok, what I want to do is pull out all the records. BUT, All of the records with req_id = 1 I want to check and see if they are a month old or during the current month. Basically any of the records with req_id = 1 that are a month old I do not want to display, but I want to display records with req_id = 1 that are less than a month old and ALL of the req_id = 0 records.

I'm not sure if I explained that properly, but hopefully you guys can help me out.

LordShryku

First....why? Why not use an actual date field? Bygons...

You can use

$dateTime = strtotime($date);
//gets time stamp for the date
$now = time();
//compare these to get your difference and see if it's a month old or not.

jaZon

I didn't initially use it because I was confused as to what to do. Use the date, timestamp, etc. Anyway, I am using the date field now.

jaZon

ok, I am still clueless how to go about this as I have no exp with dates. What, instead of doing it for the past month, if I allowed the user to select a date range. How would I go about doing that?

LordShryku

Million different things you could do to let them select a date. Here's a cure for your orignal date check though

<?
/* This calculates exactly one month from the current
   date. Then we'll calculate the timestamp of the date
   pull from the DB. Then compare them to see how old
   it is. 
*/

//assume the date from the db is 20030501(May 1st 2003)
//lets check to see if it's a month old
$date = "20030501";
$dateTime = strtotime($date); //gets time stamp for the date 

$monthAgoTime = mktime(0,0,0, date(m)-1, date(d), date(Y)); //get time of one month ago

if($monthAgoTime > $dateTime) {
    echo "Over a month old";
}
else {
    echo "Not over a month old";
}

?>

jaZon

Thanks, that works awesome for my original question.

Regarding allowing the user to specify a date range, I currently have a form that allows them to enter dates. I'm using 3 different boxes per date, so I have to concatenate like:

$date_one = $year_one . $month_one . $day_one;
$date_two = $year_two . $month_two . $day_two;

and my query is as follows:

$query = "SELECT * FROM trouble_ticket WHERE req_location = 'HAND $i' AND req_completed = '1' and req_date_completed BETWEEN $date_one AND $date_two";

Of course that doesn't work the way I want it to for several reasons. I'm thinking I could format the date the user selects and somehow format the db date, but I haven't the slightest idea and also think there has to be a better method. Any suggestions?

jaZon

anyone?