Page 1 of 1

question re: comparing dates

Posted: Thu Mar 18, 2010 8:59 am
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?

Re: question re: comparing dates

Posted: Thu Mar 18, 2010 10:10 am
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)

Re: question re: comparing dates

Posted: Thu Mar 18, 2010 1:18 pm
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..

Re: question re: comparing dates

Posted: Thu Mar 18, 2010 1:56 pm
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())

Re: question re: comparing dates

Posted: Thu Mar 18, 2010 4:29 pm
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?

Re: question re: comparing dates

Posted: Thu Mar 18, 2010 7:22 pm
by mikosiko
well.. I have other suggestion..... look my first suggestion 8)

Re: question re: comparing dates

Posted: Thu Mar 18, 2010 10:13 pm
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

Re: question re: comparing dates

Posted: Thu Mar 18, 2010 10:42 pm
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.

Re: question re: comparing dates

Posted: Fri Mar 19, 2010 8:05 am
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.