Enhance Find options on date field

[jvkranenburg]

Hi Steve,

When a table has a date field it is not possible to search between two dates in the "find" tab of the table. All fields are searchable but not the date field.
Is this yet possible in dataface? And if it is, what am I doing wrong?

Thanks,
Jerry

[shannah]

Hi Jerry,

Sadly the current "Find Form" is pretty weak on Dataface. I was going to try to do a quick hack just now to get you to be able to do range searches on date fields, but it will require me to make some changes to the QueryBuilder class, so I would prefer to make the changes more carefully and include them with the 0.6.0 release (early May).

In general, you can do range searches using the '..' notation. I.e., if you want to search for products with Prices between 200 dollars and 500 dollars you could put "200 .. 500" in the 'Price' field on the find form. Unfortunately, even if I get the Date field to show up on the find form (which is a simple change), this won't work because MySQL doesn't seem to support searches of the form :
WHERE `DateField` < '2004-04-05' and `DateField` > '2003-04-05' (which is how a query for DateField='2003-04-05 .. 2004-04-05' would be rendered).

As I'll be working on the find form in the next couple of weeks, I'm open to suggestions on how it should work (i.e., what kind of interface would work the best).

-Steve

[amwassil]

>>this won't work because MySQL doesn't seem to support searches of the form : WHERE `DateField` < '2004-04-05' and `DateField` > '2003-04-05' (which is how a query for DateField='2003-04-05 .. 2004-04-

Steve,

Try this instead:

$query = "select * FROM < table > WHERE < DateField > between '".$trimmed1."' AND '".$trimmed2."';

$trimmed1 and $trimmed2 are defined here:

< ? php

// Get the search variable from URL

$var1 = @$_GET['r'] ;
$var2 = @$_GET['s'] ;
$trimmed1 = trim($var1); //trim whitespace from the stored variable
$trimmed2 = trim($var2); //trim whitespace from the stored variable

'r' and 's' are derived from a form:

< FORM action = " rangequery . php " target = " < frame > " method = " get " >
from < INPUT type = " text " size = " 10 " name = " r " / >
< b r >to < INPUT type = " text " size = " 10 " name = " s " / >
< INPUT type = " submit " value = " Go " / >
< / FORM >

I have this form in my navigation frame and it generates a list based on a range of dates in a second frame based on the first and last dates entered in the form and passed to the "rangequery.php" by the variables 'r' and 's'. You might be able to do without the "trimmed" bit, but I use it. Hope this helps.

Michael Wassil

[shannah]

You're right Michael... it turned out I was getting erroneous results on my tests for another reason.

Thanks for pointing this out.

Jerry,

Stay tuned.. there will be a fix within the hour.

-Steve

[shannah]

http://sourceforge.net/tracker/index.php?func=detail&aid=1475846&group_id=153729&atid=788932

This is a temp fix. It will give you a text field for date searches. I also added some search instructions..

Best regards

Steve

[jvkranenburg]

Hi Steve,

Thats really fast!! And it works perfect for me now!

Tnx,
Jerry