2. PHPRunner General questions
  3. Date Search Problems.....
This topic is locked

Date Search Problems.....

 		12/6/2006 8:10:30 PM
PHPRunner General questions
J
 Jkelleyus author

I'm a new user of phpRunner... and am very happy so far with everything except the date search issue that appears on the forum from time-to-time.
I posted this in another thread but it seems to be side-tracked... so I'm asking with an appropriate title this time (hope this isn't inappropriate).
The date search thing still does not work correctly. As other posters have said, if you type in the SQL date... say as 2006-12-05 for today... it works. If you use the datepicker and the 'Between' dates option it does not.
Where is the "Edit as" setting dialog on the Visual Editor? I couldn't find it. The MySQL datatype is set to DATE, and the phpRunner properties for the date are set to SHORT DATE. I've updated the php.ini for our regional settings.
If I'm looking for today's tranactions as a search term... and I type in 12/05/2006 (or 12/05/06) the records are not returned. If I type in 2006-12-05 they are.
Any help appreciated....
Jake
L
 		larsonsc 12/7/2006

Jake,
The reason your date searches are failing seems to have to do with the formatting. MySQL defaults to YYYY-MM-DD to store actual date values. So, that being said, if you search for DD/MM/YYYY, the character string does not result in a match.
So far, I haven't had an issue with doing date based searches with my PHPR generated pages since when the dates get stored, they get converted to the proper format. However, if you are running a search from a string input field instead of a date field, this could lead to an issue with the comparison.
Hope that helps, if not, hit me up again and I'lls ee if I can help you further. I have been doing a lot of stuff with dates lately, so I kind of feel like I have a few of the comparison methods and mathematics processes figured out.
--Shawn
J
 		Jane 12/7/2006

Jake,
it seems this error occurs because you selected wrong date format on the "Edit as" settings dialog.
To change date format proceed to the Visual Editor tab, choose EDIT page, double click on your date field and select your date format.
J
 		Jkelleyus author 12/7/2006

Jake,

it seems this error occurs because you selected wrong date format on the "Edit as" settings dialog.
To change date format proceed to the Visual Editor tab, choose EDIT page, double click on your date field and select your date format.


This data is all imported... which may be the problem. As you suggested, I changed the date format to one that would reflect YYYY-MM-DD. The effect of this was to return all the records in the database during a search.
What I would like to do is be able to enter the date in US format and have php convert it before the query is executed.
Is this possible within phpRunner... or am I overlooking something here?
Thanks again,
Jake
D
 		drh 12/7/2006

Jake,

Just curious, when you enter your date to search for, what are you selecting in the middle box? Contains, equals, starts with, more than, etc.
I found "contains" does not work for dates (which is the default). However, equals works. For instance, I have a field named Date. In the mysql db it is defined as a date type field. In PHPR it is a short data field.
So I have 3 records with the date field being

10/13/2006 (record 1)

10/13/2006 (record 2)

10/21/2006 (record 3)
When I search on the Date field and select "equals" and the search criteria as 10/13 I get 2 records returned.

If I search on the Date field and select "contains" and the search criteria as 10/13 I get 0 records returned.
Not sure, is this the problem you are having?
Dave
J
 		Jkelleyus author 12/7/2006

Actually, most the of the trouble centered around the date ~between~ search.
Strangely enough, when I changed the date format as Jane suggested... and then changed it back to US format... the problem went away.
Perhaps another off-the-wall php problem?
Thanks,
Jake