Trying to make DF work for what we need it for ... hardly an

[GenTarkin]

no but I mean....like your date range if I type in at the top in the address bar....
call_data_time=05/23/2007..06/06/2007
The actual query results only comes up with dates ranging from 05/23/2007 to 06/05/2007
how can I make it in your engine to show the actual requested range of dates including the 06/06/2007 date in the results?

[shannah]

Sorry .. posted my reply to the first message before you had posted the 2nd message.

...

In Dataface/QueryBuilder.php, around line 537, you'll find a line like:

$where .= $this->wc($this->_tablename, $key)." > '".addslashes($low)."' AND `{$this->_tablename}`.`$key` < '".addslashes($high)."' AND ";

Change the '>' and '<' to '>=' and '<=' respectively.

e.g.

$where .= $this->wc($this->_tablename, $key)." >= '".addslashes($low)."' AND `{$this->_tablename}`.`$key` <= '".addslashes($high)."' AND ";

If this looks like the more intuitive way to think of range searches I may change it for the next release to work this way.

-Steve

[GenTarkin]

hrm thats still not correctly displaying the date range =( anything else to try?

[shannah]

It should work.Ê Perhaps mysql doesn't like the format of the dates.Ê Mysql nativeÊÊ date format is 'YYYY-MM-DD'

[GenTarkin]

Im actually not using the DATE type setting in mysql its just storing our dates as VARCHAR...but the search is working fine with that except the issue Ive been having. So Im thinking when it does the range sort its converting the VARCHAR to like numbers or something on the fly...and sorting the numbers that way. I dont have a way to change the number format that goes into the DB either =(.
If there is any other script you think that I can impliment into the dataface application to remedy this sort method please let me know, otherwise not sure what to do =(

Thanks,
Jason

oh I guess another question after I put that change in the QueryBuilder.php do I have to reinitialize the dataface program or anything? all I did was refresh the dataface webpage I was on...

[shannah]

Hi Jason,

Any reason why you are using a varchar field to store dates?Ê Unless there is a very good reason, dates should always be stored in a date field.Ê This would be a clue as to why the search isn't working correctly.Ê The way it will search with a varchar field is in alphabetical order (or maybe ascii).Ê So with dates it lines up pretty closely.Ê (e.g 2004-05-07 comes before 2004-05-08 in alphabetical order).Ê However you would need to search for the exact format of the date for this to work.

e.g. 2004/05/07 is NOT equal to 2004-05-07 when searching as text, one will be before the other.Ê In fact if 2004/05/07 comes before 2004-05-07, then it would also come before 2004-01-01.ÊÊ Also any extra or different characters stored in your record's fields will cause the search to go wonky.Ê e.g. if there are any spaces before, in the middle of, or after.

Best solution is to change your fields to date fields.

if that is not possible, it would be a good idea to experiment with direct SQL with queries to find out what SQL queries return the results you need.Ê Then I can instruct you on how to duplicate that query with dataface.

No reset is required in dataface after the changes to query builder.

Best regards

Steve

[GenTarkin]

hrm....Ill try playing around with different field types ( I think thats what you meant by direct query not sure...)
Anyways sorry been gone for a while =P went on vacation.
I had another question for you.

I need to make this data displayed to the client as secure as possible with dataface. I went through the tutorial to do that one step when creating user logins and make that one delegate class that controls security or whatnot.

I need it so its very hard for someone to figure out the login for dataface with the admin account to the data or the read only accounts. Its very sensitive data.

I have the mySQL server stored on a UNIX based virtual server...so is dataface. If there is any recommendations you can give to me to make its secure as possible.
I was thinking about SSL also. How would I go about making DATAFACE once logged into it have all of it be over SSL traffic?

Thanks,
Jason

[shannah]

By direct query, I meant try using PHPMyAdmin to enter SQL queries manually to obtain your range searches. Once you find a query that returns what you want, you can show me the query and I'll tell you how to duplicate it the dataface way.

You might try forcing all connections to be SSL using mod_rewrite. This post may be helpful:
http://www.webmasterworld.com/forum13/3345.htm

As far as other security considerations go:
1. Make your application delegate class getPermissions() method to be very restrictive (i.e. NO_ACCESS), then implement get permissions methods in any of your tables' delegate classes to return appropriate permissions. To save some typing, you may want to use PHP's inheritance features. e.g. create a base class that implements getPermissions() and then make all of your delegate classes subclasses of this class.

2. Use md5 encryption on the password field. e.g.
In the fields.ini file for the users table:
[password]
encryption = md5

Passwords should be secure anyways because Dataface never loads passwords from the DB. (notice that the password field is always blank in the edit forms).

3. If history is enabled, then snapshots of each record is stored in tables of the form %tablename%__history. Dataface 0.7 automatically disables outside access to these tables. If you are using an older version of Dataface you may want to explicitly stop this by adding the following to the beginning of your index.php file:

if ( preg_match('/__history$/',@$_REQUEST['-table'] ) ){
die('Sorry you can't access this table.');
}

4. If you are using a shared server... many more considerations need to be made - all of which are general concerns true of any application housed on a shared server.

5. MOST IMPORTANTLY - Make sure that you have an .htaccess file to prevent apache from serving ini files. Try pointing the browser to your conf.ini file. If it displays it, then you need to add an .htaccess file as described in the getting started tutorial.

That's pretty much it.

-Steve

[GenTarkin]

ok, so that mod_rewrite stuff...that was in your link, where do I put that?
do I put it in a .htaccess file or where does it go?

Thanks,
Jason

[shannah]

Yes. In the .htaccess file:

http://httpd.apache.org/docs/2.0/mod/mod_rewrite.html

[GenTarkin]

AWESOME, ok I got all those security steps taken care of I believe...every step except the 1st one but the 1st one I also believe I have down...if thats the same thing as in what was in the starting guide and that one security thing you have in the guide for making the security delegate.
I got SSL working with the .htaccess (thats pretty sweet btw, a lot easier then a damn 2k3 web server).

The only thing left is figuring out the qeueries for the dates. and yes I dont have a way of changing the date into the proper date format for mySQL so....
If you meant by direct query to get the results I desired, I know what I have to do to get the proper results I want in a search if thats what you mean - I have to search one day in the future to get the proper date range thats desired. so if I want to display 5/25 - 06/05 I would have to search the dates 5/25 - 06/06

So let me know, and btw thank you for all your generous and informative help so far. =) making this easier on me and Im learning lots hehe.

Thanks,
Jason

[shannah]

What I mean by a direct query is something like:

select * from foo where datecol > '2004-06-12' and datecol < '2004-08-12'

Or in your case it might look like:
select * from foo where datecol >= '5/25' and datecol <= '06/05'

Now if your date fields are stored in a varchar field, that query will probably return an empty set because 06/05 comes before 05/25 in the alphabet.

select * from foo where datecol >= '05/25' and datecol <= '06/05'

Might give you the desired results - if every date is formatted consistently and accurately.

Play around with the SQL to see what you get.

-Steve

[GenTarkin]

see I know your 2nd example works perfectly except for the problem I am describing...

select * from foo where datecol >= '05/25' and datecol <= '06/05'

WORKS great EXCEPT....it will not show the 06/05 date it shows every date from 05/25 including 05/25 to 06/05 BUT does not include the 06/05 it just goes up to 06/04

[shannah]

You must have some extra characters stored in the datecol after it.
e.g.

if the datecol has value '06/05 ' (note the space after 05)

Then this won't be turned up by select * from foo where datecol >= '05/25' and datecol <= '06/05'

If you used a date column then you wouldn't have this problem.

In this case, you just have to make sure that what you are searching for matches the format of the data stored exactly. No extra spaces or characters of any kind.

-Steve

[shannah]

Oh... are you using a CHAR field or a VARCHAR field? CHAR fields tend to fill in the extra characters with spaces.

-Steve