Im still tackling this DATE issue with my site Im working on

[Jason1]

Well I found one way I can query the mySQL engine to make it convert my date strings to appropriate dates and sort based on that, it does it PERFECTLY...
Im wondering is this in some way integrateable to do custom query when sorting my date column by asc or desc or for the code purpose below...use the application menu side panel to execute this somehow? for my date ranges? Or is there just a way for dataface to display the dates using the STR_TO_DATE feature? and all operations done via asc or desc or finding are done with STR_TO_DATE?

SELECT * FROM myTable WHERE STR_TO_DATE(date,'%d/%m/%Y') <= '@dtFrom' AND STR_TO_DATE(date,'%d/%m/%Y') <= '@dtTo'

That works great for me...can this be done with dataface on one of my columns?
Thanks,
Jason

[Jason1]

OK I basically need to know how to get:

__sql__ = "select *, str_to_date(call_data_time ,'%m/%d/%Y') as searchable_date from apstable"

working...
Right now it returns this:

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

Ive double checked and rechecked, and this query runs correctly directly on the DB.
Thanks,
Jason

[shannah]

Not sure why it is objecting to this, but you an add some debugging code by going to Table.php on the line in question (1789).

Code: Select all

$data = $parser->parse($tsql);
foreach ( $data['columns'] as $col ){


Add a debugging line between these two:

Code: Select all

$data = $parser->parse($tsql);
if ( PEAR::isError($data) ) trigger_error($data->getMessage(), E_USER_ERROR);
foreach ( $data['columns'] as $col ){


This should give you a fatal error but at least it will tell you a little bit about why it failed to compile the query.

I can't see any reason why this query would fail to compile. I have tried it out in my dev version and works ok.

Let me know how it goes.

-Steve

[Jason1]

ok I did what you said and here is the result error code:

Fatal error: Parse error: Unexpected token "text_val" on line 1 select *, str_to_date(call_data_time ,'%m/%d/%Y') as searchable_date from apstable ^ found: "%m/%d/%Y" in /home/abscpu/public_html/dataface/Dataface/Table.php on line 1789

[shannah]

Looks like the str_to_date function is not registered with the parser yet. You can fix this by opening the lib/SQL/Dialect_MySQL.php file and adding 'str_to_date' to the 'functions' array.

Thanks for pointing this out. I have added it to my dev version now for the next release.

-Steve

[Jason1]

YES YOU ARE AWESOME!!!! EVERYTHING WORKS FINALLY WOOT WOOT!!!!
I also figured I would do my sort orders in that same __sql__ line ... much easier than having to put it in the index.php with lot more code lol...

[shannah]

There could be some undefined/unexpected bahavior from putting the sort in the __sql__ line. The result of the __sql__ line is meant to used as a drop-in replacement for the full table contents using default sorting, so adding sorting could cause some errors somewhere down the road.