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

Archived from the Xataface Users forum.

GenTarkin — Fri Jun 08, 2007 12:43 pm

Here is what we are trying achieve. We have a script that goes into a site that grabs reports of information automatically every day and that script then shoots over the values it pulls into a mySQL database I have set up.
The database consists of 2 tables one for one part of the script that grabs a certain section of data from units and then another table that does the same thing.
So we have 2 tables with data in them. Within these tables both of them have a field that has a string value of the “unit number / description”

I have managed thus far to successfully install DF onto our server and it is interacting with the mySQL db perfectly and I even set up users and a read only account and admin account like you instructed how to do in the getting started guide.

The rest of the guide as far as relationships, delegation and the templating and stuff just…I look at the code and have no idea where to start.
Here is what I want to do with this.
The customer will be seeing DF in its READ ONLY permissions set up by their user account. So, they will not be doing any editing or adding of new records or anything like that, it is simply going to be used so they can parse units by their group ( unit number / description ) mentioned above. And also by a range of dates of collected data.
Here is what the idea so far has been: To somehow add a navigation menu to the left side that will contain buttons or a drop down list for a section ( the unit number / description - mentioned above in that field )
and also a date range calender icon of 2 calenders to select their range or 2 date drop down fields to select the date range. Submit a query based on that information and have it displayed properly in the right main pane.

Im guessing this can all probably be done similarly to how your demo of the bookstore is set up .. you have a navigation menu on the left that has the “categories” and then that shows up in the right accordingly.
If I could get that somehow with a date finder below those categories that would be what we are ultimately looking for.
If anyone could help me or tell me the easiest way to achieve what we want this thing to do that would be awesome. Like just point me in the direction of something like… well first you need to do this relationship and then this delegate control thingy here and then do this in the template… Just get me started and I would be greatful.
I do not know how hard or easy this is to do what I need it to do.
Thanks a ton for all willing to help =)
Jason

shannah — Fri Jun 08, 2007 1:01 pm

Hi Jason,

It looks like you’re on the right track.ÃŠ Everything is pretty straight forward - except the calendar thing will probably require a little bit of javascript.
Using Dataface’s url convention, you can probably get what you need by just creating links in a clever way.
For example you can search for date range with a url like:
http://yourdomain.com/yourapp/index.php?date=2004-09-18..2005-09-12
(to find records where the date field has a value between sept 18 2004 and sept 12 2005).
And as you know, you can make this all happen by creating a form:

Enter a title: ÃŠ
etc…
The only tricky part will be the date fields.ÃŠ You ultimately need to have a single form field that contains the input for the date field (e.g. startdate..enddate).
One way to achieve this is to have a hidden form field with the same name as your date field on which you are searching.ÃŠ Then have 2 separate calendar widgets to select the start and end date.ÃŠ And with an onsubmit() javascript handler for the form, you could enter the appropriate value into the hidden date field when the form is submitted.
For the calendar widgets you have an array of choices out there that you can use.ÃŠ Dataface comes with one called JSCalendar (in the dataface/lib directory).ÃŠ You can find more information about JS Calendar at http://www.dynarch.com/projects/calendar/
Yahoo also makes a number of nice DHTML widgets, including a calendar widget.ÃŠ You can see more information about that one at http://developer.yahoo.com/yui/calendar/
Hope this helps you out a little..
Best regards
Steve

GenTarkin — Fri Jun 08, 2007 3:27 pm

ok awesome, I have then one question for you, The date / time format that the script from the server we are pulling the data from is now pulled in the format of 00/00/0000 00:00:00 MST which is I noticed not how the DATE field in mySQL stores that information. How do I make the date we are pulling compatible with a DATE field type in mySQL? also what would be the best field for the dates would it be DATE or another field you recommend?
They will only need to query using the date as the time does not matter.
Thanks,
Jason

shannah — Mon Jun 11, 2007 9:00 am

You are able to adjust these values with jscalendar.ÃŠÃŠ I don’t have the specifics at my fingertips, but a good way to find this information is to just look at the HTML source for an edit record form that uses the calendar and see how it is done there (if you are outputing directly from javascript).ÃŠÃŠ If you are using PHP to generate the source for it, then you can check out the HTML/QuickForm/calendar.php file in the dataface distribution to see how dataface does it.

Let me know if you have trouble finding it.
-Steve

GenTarkin — Tue Jun 12, 2007 12:01 pm

ok another question, Im trying to use javascript like you suggest to handle certain parts of my form, but… the { } brackets, for IF statements in javascript, are seen as comments or something inside the dataface_application_menu.html and Im getting all sort of parsing errors because its not kicking in the javascript….how do I get aroud this?

GenTarkin — Tue Jun 12, 2007 12:03 pm

OH and question how to submit a search query that can basically do OR searches like if it contains THIS or THIS or THIS show all 3 of them.

GenTarkin — Tue Jun 12, 2007 12:11 pm

Also, sorry for the many replies but I keep having questions come from me, is there any way to display all the data in the list view from both tables at the same time? what can I use in my GET statement that would show both tables contents at same time on same list.
Thanks

shannah — Tue Jun 12, 2007 12:46 pm

the { } brackets, for IF statements in javascript, are seen as comments
or something inside the dataface_application_menu.html and Im getting
all sort of parsing errors

Wrap your javascript in {literal} … {/literal} tags.

e.g.

{literal}

{/literal}

OH and question how to submit a search query that can basically do OR
searches like if it contains THIS or THIS or THIS show all 3 of them.

Value 1 OR Value 2 OR Value 3

e.g. If you wanted to search for a person named Peter or Paul or John, you would type: “Peter OR Paul OR John” in the search field.

is there any way to display all the data in the list view from both tables at the same time

Yes.ÃŠ The way to do it will depend on what exactly you mean by displaying both tables at the same time.ÃŠ You could display two tables one on top of the other, in which case we don’t worry about how the data in the tables are related. Or you could display a combined table where each row has columns from both tables (i.e. a JOIN table).ÃŠ Which of these ways did you have in mind?

-Steve

GenTarkin — Thu Jun 14, 2007 9:49 am

ok I need help, I have the javascript successfully changing the value of a hidden field to correctly do DATESTART..DATESTOP into that hidden form element for the GET query. BUT, I need to also write javscript that will basically take out the .. in the query if the person does not enter any dates to the 2 fields.
So, here is the code I have so far, Im sure the syntax is just screwed up…if you could help me with it that would be awesome =)

{literal}

{/literal}

GenTarkin — Thu Jun 14, 2007 9:50 am

ok I need help, I have the javascript successfully changing the value of a hidden field to correctly do DATESTART..DATESTOP into that hidden form element for the GET query. BUT, I need to also write javscript that will basically take out the .. in the query if the person does not enter any dates to the 2 fields.

So, here is the code I have so far, Im sure the syntax is just screwed up…if you could help me with it that would be awesome =)

hrm it wouldnt let me put the code in =(

form Name=”form1” action=”index.php” method=”get”
onsubmit=”form1.call_data_time.value = form1.datestart.value + ‘..’ + form1.datestop.value;
if(form1.call_date_time.value == “..”){ form1.call_data_time.value = “”;
}else{
form1.call_data_time.value = form1.datestart.value + ‘..’ + form1.datestop.value;
}”>

GenTarkin — Thu Jun 14, 2007 9:50 am

ok there we go except with the correct < on the front =) and literals surrounding that entire block of code.

shannah — Thu Jun 14, 2007 10:25 am

Looks good, except your order is a little off.ÃŠ You probably want to do the check for ‘..’ after you have placed the values in.

e.g.

form1.call_data_time.value = form1.datestart.value + ‘..’ + form1.datestop.value;
if(form1.call_date_time.value == “..”){ form1.call_data_time.value = “”;
}

I also notice that there is probably a typo somewhere here, because you are using ‘call_date_time’ in some places and ‘call_data_time’ in other places.ÃŠ (note the difference between date and data).

-Steve

GenTarkin — Thu Jun 14, 2007 11:01 am

hrm…I just did notice that typo I fixed it to data thats what its supposed to be but…. its still not working right no matter what I type into query it always is setting the value of call_data_time to nothing.

GenTarkin — Thu Jun 14, 2007 11:13 am

sweet figured it out =)
onsubmit=”form1.call_data_time.value = form1.datestart.value + ‘..’ +
form1.datestop.value;
if(form1.call_data_time.value == ‘..’){ form1.call_data_time.value = ‘’;
}”

cant use “” in the IF statement have to use ‘’ =)

ok next question…when I say put in a range of dates from 05/25/2007..06/06/2007
It will only show an actual query of dates from 05/25/2007 to 06/05/2007

Is there any way to make it with your engine to tell it to include all numbers typed in including the 06/06/2007 one in the query?
Cuz this will confuse the client lol.

shannah — Thu Jun 14, 2007 11:13 am

One technique that I often use when debugging javascript is to add alert() statements to see what is stored in different variables along the way.. This will tell you where you are going wrong.

e.g. Check what values are in form1.datestart.value and form1.datestop.value:

alert(form1.datestart.value);

alert(form1.datestop.value);

and so on… this will lead you to the problem.

-Steve

GenTarkin — Thu Jun 14, 2007 9:50 am

ok there we go except with the correct < on the front =) and literals surrounding that entire block of code.

shannah — Thu Jun 14, 2007 10:25 am

Looks good, except your order is a little off.ÃŠ You probably want to do the check for ‘..’ after you have placed the values in.

e.g.

form1.call_data_time.value = form1.datestart.value + ‘..’ + form1.datestop.value;
if(form1.call_date_time.value == “..”){ form1.call_data_time.value = “”;
}

-Steve

GenTarkin — Thu Jun 14, 2007 11:01 am

GenTarkin — Thu Jun 14, 2007 11:13 am

cant use “” in the IF statement have to use ‘’ =)

ok next question…when I say put in a range of dates from 05/25/2007..06/06/2007
It will only show an actual query of dates from 05/25/2007 to 06/05/2007

Is there any way to make it with your engine to tell it to include all numbers typed in including the 06/06/2007 one in the query?
Cuz this will confuse the client lol.

shannah — Thu Jun 14, 2007 11:13 am

One technique that I often use when debugging javascript is to add alert() statements to see what is stored in different variables along the way.. This will tell you where you are going wrong.

e.g. Check what values are in form1.datestart.value and form1.datestop.value:

alert(form1.datestart.value);

alert(form1.datestop.value);

and so on… this will lead you to the problem.

-Steve

GenTarkin — Thu Jun 14, 2007 11:32 am

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 — Thu Jun 14, 2007 11:53 am

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 — Thu Jun 14, 2007 12:23 pm

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

shannah — Fri Jun 15, 2007 1:01 am

It should work.ÃŠ Perhaps mysql doesn’t like the format of the dates.ÃŠ Mysql nativeÃŠÃŠ date format is ‘YYYY-MM-DD’

GenTarkin — Fri Jun 15, 2007 9:16 am

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 — Fri Jun 15, 2007 11:03 am

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 — Tue Jun 26, 2007 3:44 pm

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 — Tue Jun 26, 2007 4:02 pm

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:

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.
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).

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.’);
}

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.
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 — Wed Jun 27, 2007 9:36 am

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 — Wed Jun 27, 2007 10:40 pm

Yes. In the .htaccess file:

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

shannah — Fri Jun 15, 2007 11:03 am

Hi Jason,

Best solution is to change your fields to date fields.

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

Best regards

Steve

GenTarkin — Tue Jun 26, 2007 3:44 pm

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.

Thanks,
Jason

shannah — Tue Jun 26, 2007 4:02 pm

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:

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.
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).

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.’);
}

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.
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 — Wed Jun 27, 2007 9:36 am

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 — Wed Jun 27, 2007 10:40 pm

Yes. In the .htaccess file:

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

GenTarkin — Thu Jun 28, 2007 8:56 am

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 — Thu Jun 28, 2007 12:32 pm

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 — Thu Jun 28, 2007 3:38 pm

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 — Thu Jun 28, 2007 4:15 pm

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 — Thu Jun 28, 2007 4:16 pm

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

-Steve

GenTarkin — Thu Jun 28, 2007 4:34 pm

VARCHAR - could it be a collation issue?

shannah — Thu Jun 28, 2007 8:29 pm

Shouldn’t be collation.
What happens when you do:
select * from foo where datecol = ‘06/05’
?

Does it give you your results? If not, then your datecol is storing an extra character somewhere or using a different format.

-Steve

GenTarkin — Fri Jun 29, 2007 1:00 pm

this is the value of those fields and they are all the same format.
06/20/2007 06:11:46 MST

I tried what you suggested above and it returned 0 results. (made sure I had the correct table and column name).

shannah — Fri Jun 29, 2007 1:40 pm

There is your problem.
You are storing date and time - not just date.
There is nothing that can be done reasonably to achieve the results you want (using a varchar field). This is because
‘06/20/2007 06:11:46 MST’ < ‘06/20/2007 06:11:46’ < ‘06/20/2007’ when ordered alphabetically.

In fact because you are storing your dates with month first, then day, then year, the alphabetical order will be completely different than the date ordering.

e.g.
06/20/2007 06:11:46 MST comes after 05/20/2009 06:11:46 MST alphabetically, but chronologically it should come before.

This is the reason why you should not use a varchar field to store dates.

You should be using a datetime field type. Then you will be able to run range queries properly. There is no way to make this work properly with a varchar field (other than to load all of your records into memory and implement your own sorting in PHP – not advisable).

Once you have changed your field definitions to datetime fields you will be able to run queries like you want (almost).
Since you are storing both dates and times, you will still need to do exclusive ranges, because a date like:
2004-05-07
will be automatically converted to the datetime
2004-05-07 00:00:00

So if you are searching for records up to and including 2004-05-07 you would need to search for
datecol < ‘2004-05-08’
if you did
datecol <= ‘2004-05-07’
it would only return records with datecol earlier than 2004-05-07 00:00:00 - but times like 2004-05-07 12:30:05 would all come after this time.

An alternative that would allow you to do an inclusive search would be to also include the time.

e.g.

To find all records between 2004-05-07 and 2004-05-09 you could do:
2004-05-07 00:00:00..2004-05-09 23:59:59

Of course all of this would only work if you change your fields to datetime fields.

For the query, it is best to pass the dates to dataface in the native mysql format yyyy-mm-dd hh:mm:ss, however using the calendar widget you can easily hide this logic from your user.

Best regards

Steve

GenTarkin — Fri Jun 29, 2007 3:11 pm

my problem is I have no way of storing the dates in mysql in that 000-00-00 format.
They get inserted into the SQL in the way of 00/00/0000 =(

Its done by a program automatically throughout the day…I dont know what language that program is in or how to do preprocessing on the dates it pulls so it can properly insert them into a DATETIME field in mysql.

So, I pretty much just have to deal with it =/

GenTarkin — Thu Jun 28, 2007 4:34 pm

VARCHAR - could it be a collation issue?

shannah — Thu Jun 28, 2007 8:29 pm

Shouldn’t be collation.
What happens when you do:
select * from foo where datecol = ‘06/05’
?

Does it give you your results? If not, then your datecol is storing an extra character somewhere or using a different format.

-Steve

GenTarkin — Fri Jun 29, 2007 1:00 pm

this is the value of those fields and they are all the same format.
06/20/2007 06:11:46 MST

I tried what you suggested above and it returned 0 results. (made sure I had the correct table and column name).

shannah — Fri Jun 29, 2007 1:40 pm

In fact because you are storing your dates with month first, then day, then year, the alphabetical order will be completely different than the date ordering.

e.g.
06/20/2007 06:11:46 MST comes after 05/20/2009 06:11:46 MST alphabetically, but chronologically it should come before.

This is the reason why you should not use a varchar field to store dates.

An alternative that would allow you to do an inclusive search would be to also include the time.

e.g.

To find all records between 2004-05-07 and 2004-05-09 you could do:
2004-05-07 00:00:00..2004-05-09 23:59:59

Of course all of this would only work if you change your fields to datetime fields.

For the query, it is best to pass the dates to dataface in the native mysql format yyyy-mm-dd hh:mm:ss, however using the calendar widget you can easily hide this logic from your user.

Best regards

Steve

GenTarkin — Fri Jun 29, 2007 3:11 pm

my problem is I have no way of storing the dates in mysql in that 000-00-00 format.
They get inserted into the SQL in the way of 00/00/0000 =(

So, I pretty much just have to deal with it =/

shannah — Sat Jun 30, 2007 12:41 am

Ok.. here is a tricky way to accomplish this, using Dataface’s surrogate views.

Step 1: Add a calculated field called ‘searchable_date’ (or some other name that you like) by adding the following to the beginning of your fields.ini file:

__sql__ = “select *, STR_TO_DATE(datecol, ‘%m/%d/%y’) as searchable_date from foo”

This uses the mysql str_to_date function (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date) to create a calculated column that is of type ‘date’.

Then you can perform your search on the searchable_date field exactly how you wanted to do it.
I.e. change the date search so that it searches the searchable_date field instead of the datecol field.

This is a bit of a cheat, but it should work for ya.

Best regards

Steve

GenTarkin — Mon Jul 02, 2007 10:48 am

Awesome I will give that a shot.
I need some more help if you dont mind… I know Im probably getting annoying… LOL
Ive looked through the customizing look and feel and how to alter the appearence of the list tab.
I cannot figure out how to get the little checkmark out of the first column on each row…how do I remove that and also remove the + sign that expands the details of that item.
Also, how do I change the name of one of the columns…without altering the database for each table. Cuz I need to get a / in one of the columns and mySQL dont like that character as part of a field name.
Thanks,
Jason

GenTarkin — Mon Jul 02, 2007 11:32 am

Fatal error: Cannot use object of type PEAR_Error as array in /home/abscpu/public_html/dataface/Dataface/Table.php on line 1789

is the error I got when trying to do the thing you suggested with the dates.
I then put that directly into an SQL query and it worked fine BUT….the year was incorrect it was 2020 and not 2007 =(

GenTarkin — Mon Jul 02, 2007 1:07 pm

alright I was able to figure out how to hide the 2 items in the menu other than “list” mode I didnt need.
I did that through copying actions.ini to my apps folder and just blanking out the category value in details and find tabs definined in that file.
NOW upon further looking around I figured well why not just make a print action.
I noticed yours on your main dataface site and I used the same small javascript code you have on your print button at least the code I can see anyways….
BUT when I print mine I get like whatever is shown on the top portion of the screen. and it will not print my entire list view.
Is there any way to define what gets sent to the printer? I would just like the current list view to be sent to the printer and not the other stuff around it.
Thanks,
Jason

PS I still need to figure out how to take that checkmark out of the first column and not have that there at all and also the + sign for all the rows…dont need to expand the row at all…just need to basically view the data.

shannah — Tue Jul 03, 2007 3:26 pm

Fatal error: Cannot use object of type PEAR_Error as array in /home/abscpu/public_html/dataface/Dataface/Table.php on line 1789

Evidently the query is not being parsed. It is either a problem with the ini file (mismatched quotes or something) or the query itself is not being handled correctly by the SQL parser. Can you post the relevant portions of the fields.ini file so I can take a look..

how to get the little checkmark out of the first column on each row

You can hide some of this stuff with stylesheets. E.g. You’ll notice that the checkbox all have class ‘rowSelectorCheckbox’. Hence you can hide them by adding:
.rowSelectorCheckbox { display: none}
to your stylesheet.

You can get rid of the little ‘+’ sign in each row by setting the ‘enable_ajax_record_details’ preference to 0.

e.g. in the [_prefs] section of your conf.ini file you can do:
enable_ajax_record_details=0

change the name of one of the columns…without altering the database

If you set the widget:label attribute on the field it will change the column’s name whereever it is used inside dataface.

If you have made your own custom column headers, you can access the widget:label attribute as follows:
$table =& Dataface_Table::loadTable(‘%table_name%’); // Note %table_name% should be the name of the table
$field =& $table->getField(‘%field_name%’); // %field_name% should be the field name you want
$label = $field[‘widget’][‘label’];

I would just like the current list view to be sent to the printer and not the other stuff around it.

One way to achieve this is to make a special stylesheet for printing and set the display sections that you don’t want to print to ‘display: none’.

E.g.:

@media print {

#top-section,
#another-section-i-dont-want-to-print,
{ display: none;}
}

etc..

GenTarkin — Tue Jul 03, 2007 4:06 pm

ok awesome, great help….but some things.

can you link me really quick a place to find all the sections I need to hide for the printer?
not sure how to find this if there is not a link for this on your site can you type it out? =)

Also that AJAX setting did not work and I confirmed I put it correctly in there under [_prefs] - another way to hide this perhaps?

Also, I got the checkmarks to go away but the column still remains with the checkmark at the top and nothing in the column…how do I get rid of that column all together…can it be done with a visibility widget at all?

The widget:label worked great for the headers one other question though…how do I get the column titles to be case sensitive? do I have to change the collation in the db for the varchar fields to be CS? or is there another way to force DF to display the proper cases.

the date thing I will come back too….

thanks for your help once again =)
Jason

shannah — Tue Jul 03, 2007 4:27 pm

AJAX setting did not work

Ok.. this looks like a bug. Find the section in ResultList.php that looks like:
echo ‘ |’;

And change it to:
echo ‘ ‘; if ( !@$app->prefs[‘disable_ajax_record_details’] ){ echo ‘ ‘; } echo ‘ |’;

Then set the disable_ajax_record_details preference to 0.

how do I get rid of that column all together

Sorry.. no way to do this right now - without overriding the list view with your own.

how do I get the column titles to be case sensitive?

This is a stylesheet thing. Look in plone.css for ‘text-transform’ if you want to see where this is set.

can you link me really quick a place to find all the sections I need to hide for the printer?

I don’t have this off the top of my head. Just check the HTML source and look at the id of the various sections. It may take some trial and error, but you can probably get it done by only hiding 5 or 10 elements.

-Steve

GenTarkin — Tue Jul 03, 2007 4:56 pm

my code does not look like that. It looks like this:

$expandTreeImg = ‘

Â ’;
} else {
$expandTreeImg = ‘’;
}

I tried changing it to what you said but it gave me an error, cuz well mine dont have the echo td’s around them as you can see.

GenTarkin — Tue Jul 03, 2007 4:06 pm

ok awesome, great help….but some things.

can you link me really quick a place to find all the sections I need to hide for the printer?
not sure how to find this if there is not a link for this on your site can you type it out? =)

Also that AJAX setting did not work and I confirmed I put it correctly in there under [_prefs] - another way to hide this perhaps?

the date thing I will come back too….

thanks for your help once again =)
Jason

shannah — Tue Jul 03, 2007 4:27 pm

AJAX setting did not work

Ok.. this looks like a bug. Find the section in ResultList.php that looks like:
echo ‘ |’;

And change it to:
echo ‘ ‘; if ( !@$app->prefs[‘disable_ajax_record_details’] ){ echo ‘ ‘; } echo ‘ |’;

Then set the disable_ajax_record_details preference to 0.

how do I get rid of that column all together

Sorry.. no way to do this right now - without overriding the list view with your own.

how do I get the column titles to be case sensitive?

This is a stylesheet thing. Look in plone.css for ‘text-transform’ if you want to see where this is set.

can you link me really quick a place to find all the sections I need to hide for the printer?

-Steve

GenTarkin — Tue Jul 03, 2007 4:56 pm

my code does not look like that. It looks like this:

$expandTreeImg = ‘

Â ’;
} else {
$expandTreeImg = ‘’;
}

I tried changing it to what you said but it gave me an error, cuz well mine dont have the echo td’s around them as you can see.