question re: comparing dates

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wanger220
Forum Newbie
Posts: 19
Joined: Tue Feb 02, 2010 8:44 pm

question re: comparing dates

Post by wanger220 »

This feels like an amateur question ... can't quite figure it out though ... appreciate any help from experienced folks out there.


I'm working with an existing real estate database that currently stores "Date Available" in three fields (month / day / year) as varchars.

I need to produce a table that: 1. displays only if there are any current vacancies ("Date Available" < today); and then 2. lists all available properties.

I wrote a dateDiff function to evaluate whether a property is vacant, and I used that to set up the if loop for displaying each row of data ( if (dateDiff() > -1) { echo ... } ).

However, I need to determine first whether there are any vacancies and the table should be displayed at all. For the sake of quickly producing an example, I wrote a query where month<=$thismonth and day<=$thisday, but of course this will not work in Jan or Feb, when there are sometimes still vacancies from November and December.


Any suggestions on how I can work around this? Is it possible to do a count() on an array after the query has been executed and using the dateDiff function as a condition? Or will my existing query actually work, i.e. does php understand that December comes before January?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: question re: comparing dates

Post by mikosiko »

convert the "Date Available" fields in a real date with format yyyy/mm/dd and after that just make any comparison or math with today date (in the same format)
wanger220
Forum Newbie
Posts: 19
Joined: Tue Feb 02, 2010 8:44 pm

Re: question re: comparing dates

Post by wanger220 »

That's what my dateDiff function does, but I can only run that after I've selected data from the database (right?).


What I need to do first is determine if there are any vacancies at the moment. If there are, the script will run the loop I've already written; if not, it will skip the table entirely.

I guess what I'm asking is, is it possible to count the number of corresponding rows in an array based on a condition (much like COUNTIF in Excel) - but after the data has been selected? Something like:

Code: Select all

 
$result = mysql_query("SELECT * FROM table1 WHERE status='active'");
 
$array = array( 
   while ($row = mysql_fetch_array($result)) {
      $date_available = $row['month'] . ", " . $row['day'] . ", " . $row['year'];
      if (dateDiff($today,$date_available) > -1) { echo "'" . $id . "', "; }
   }
);
 
if (count($array) != 0) { echo "There are vacancies."; }
 

I'm now thinking the cleanest fix will be to add a new field to the db and revise all records so the Available Date is recorded both as varchars and in a real date format. Come to think of it, that's a much better solution - I guess sometimes I just need to hear/read myself think until it makes sense..
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: question re: comparing dates

Post by pickle »

You may be able to do it all in the query:

Code: Select all

SELECT
  *
FROM
  `table1`
WHERE
  `year` >= YEAR(NOW()) AND
  `month` >= MONTH(NOW()) AND
  `day` >= DAY(NOW())
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
wanger220
Forum Newbie
Posts: 19
Joined: Tue Feb 02, 2010 8:44 pm

Re: question re: comparing dates

Post by wanger220 »

Thanks for the suggestion.


As I mentioned, I did initially set up the query like this (for the purpose of creating an example page).

The problem I ran into, however, is that the date for each record is entered and recorded as a varchar - i.e. 03 for March. While `month` <= month(now()) works for most months, it will not work in January (01), as some records will still have dates from December (12). Thus `month`<= month(now()) won't pick up those rows.

Unless I'm wrong and php will correctly interrupt this. Anyone?
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: question re: comparing dates

Post by mikosiko »

well.. I have other suggestion..... look my first suggestion 8)
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Re: question re: comparing dates

Post by Luke »

:banghead:

Who designed the table? Man when I come across stuff like this I always feel like strangling the "previous developer". I mean, hey, there might be a reason they designed the DB like that, but it sounds to me like they just had no idea what they were doing. I used to store date/time information in the DB as a unix timestamp until I figured out that was really stupid. The best way to store dates/times in a database is with its native datetime or date data types. That way you can get whatever information you want from it. You can do math on it (DATEADD), you can group by days, months, you can sort in any way you want, you can select records from a certain date or date range... the list goes on. So if you ever have the privilege of designing your own table, make sure to use the database's native date/time data types. Believe me, it makes life soooo much easier.

/rant
M2tM
Forum Commoner
Posts: 41
Joined: Sat Feb 27, 2010 12:35 pm

Re: question re: comparing dates

Post by M2tM »

Honestly when I encounter crap like that I fix it. Depending on how much code there is that touches on those fields it may be tough, but it will be worth it. Convert it to datetime and your world will be a much nicer one.
wanger220
Forum Newbie
Posts: 19
Joined: Tue Feb 02, 2010 8:44 pm

Re: question re: comparing dates

Post by wanger220 »

I took the first suggestion and converted the date fields to a readable format in another field. I don't know why I didn't think of doing that in the first place. 8O

I'd love to redo this entire db, but ... eh just not worth it. Site owner doesn't really care and just wants something functional.

Thanks all, appreciate the help.
Post Reply