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

[GenTarkin]

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]

VARCHAR - could it be a collation issue?

[shannah]

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]

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]

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]

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 =/

[shannah]

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]

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]

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]

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]

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]

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]

AJAX setting did not work

Ok.. this looks like a bug. Find the section in ResultList.php that looks like:
echo '