Capela
|
| Posted: 12/05/2002, 3:32 PM |
|
Hi everybody !
I need to list some records using dates interval choosen by user.
There is RECORD form, used to selection input, where user could type date interval: start-date / end-date.
If fields are not filled, all table rows has to be listed.
This form calls another page, with GRID form. At DataSource Property, I set all fields, condition and parameters to match right condition.
In this case, condition are "date_field >= s_start_date" AND "date_field <= s_end_date".
Almost everything works fine.
The only problem is: if user set some end-date (for example: 11/31/2002), table rows are listed BEFORE 11/31/2002, so, this date is not considered (but SHOULD be !)
I understand that it's right, because the query "understands" date/time as "2002-11-31 00:00:00", witch means "list all rows until 00h 00min 00secs of the day 11/31/2002", so, any rows with date/time passing this limit are not considered...
But human users don't understand this way, because he/she wants to list all rows, INCLUDING all day 11/31/2002 (UNTIL 23h 59min 59sec !).
So, I tried to change code, adding 1 day to this end-date to the passed parameter. But I couldn't do it right.
Thanks GOD, I've already know how to add 1 day to this date (if you has tried to do this using PHP, you know how difficult is !...)
So, all I need now is to change the value of passed parameter and make SQL work right. But it's hard to achieve this.
I tried following code (at "Before Execute Select" Event):
$gridname->ds->Parameters["urls_end_date"] = $new_date
Parameter value is changed, but SQL still returns wrong results.
So, anybody knows where is my mistake ?
By the way, I'm using PHP with MySQL.
Thanks for any cooperation !
--Capela--
|
|
|
 |
Lee Irving
|
| Posted: 12/06/2002, 5:02 AM |
|
Capela,
Thanks for this. I have had this problem but could not work out what the crux of the problem was. You have now shown me the problem....
As for a fix. You could try the following ......
In the Grid parameters change the end date field type to expression and set the parameter source to realenddate().
Now in the events code file create a new funtion realenddate
function realenddate()
{
$realenddate=CCGetParam("EndDate","");
$realenddate=$realenddate + (1day)
return($realenddate)
}
I am not sure of the add 1 day code. Maybe you could shed a light on this part. I have used this technique for other reasons before.
Hope this at least points you in the right direction
Cheers
Lee
|
|
|
|
Capelaaaaahhhhhhhh
|
| Posted: 12/09/2002, 4:41 PM |
|
Hi Lee,
Thanks for your help, but unfortunately, I dont think it will work, because there is just 1 column on the "grid" to be compared with 2 passed parameters by "search form".
Search form passes "s_dt_start" AND "s_dt_end" parameters, that has to be filled by user, in a format "dd/mm/yyyy".
These dates are going to be used on the next page, "Grid" type, to filter records that has to be shown. This filter is used at the QUERY (SQL sentence).
* I've already get parameters value using:
> $param_dthr_end = CCGetParam( "s_dt_end", "" )
* I've already change their value, using:
> $var_dthr_end1 = dtDDMMYYYY_toMMDDYYYY( $param_dthr_end, "/" );
(this is a function I've made to change date format)
> $var_dthr_end2 = date( "d/m/Y", strtotime( "+1 day",
strtotime( var_dthr_end1 ) ) );
(this is a PHP function that adds 1 day. It's weird, but believe me,
it works !)
> $regligs->ds->Parameters["s_dt_end"] = $var_dthr_end2;
(here, I tried to change original parameter value...)
The problem is, even doing this, besides parameter value IS changed, it couldn't change SQL results, because it's not changing this date on SQL clause (It can be seen if I turn DEBUG on).
I think that's because SQL clause is created before this function (or SQL creation uses another variable...).
Well, maybe I could answer some of your question.
But I think I still haven't aswers for MY question.
See ya.
|
|
|
|
Capelaaaaaaahhhhhh
|
| Posted: 12/09/2002, 4:43 PM |
|
Lee and everybody.
I hope my example could help anybody who has the same problem !
Besides all this things done just don't solve my problem, I've already could solved it using another method: The only thing that really worked was CHANGING "WHERE" SENTENCE.
So, if you are experiencing same problem, just do it (no, it's not NIKE...): CHANGE THE f*ck*ng "WHERE" SENTENCE !
After so many hours trying to do it, spendind lot of my time trying to understand the code behind the scenes (because the documentation is very poor, and NOBODY at CodeCharge could help me), I could do it by myself. Yes, just by myself.
So,
I'm just trying to use this utility, to see if this could be interesting to me.
I was thinking it is a very good utility. Well, it really is. And I'd like to buy it...
... BUT, considering the poor documentation, considering the ZERO Support of CodeCharge Team, because they don't gave me even a little-little help, i'm reconsidering my decision.
So, how can I pay almost $300.00 for a software if the support team don't cares about you ??
What a pitty.
|
|
|
|
fmb
|
| Posted: 12/09/2002, 7:47 PM |
|
I had a similar problem in vb. i added "11:59PM" to the end date in the query, so sql ended up being something like this:
If IsDate(txtFrom) Then sSQL = sSQL & " and DateRecvd >= #" & txtFrom & "#"
If IsDate(txtThrough) Then sSQL = sSQL & " and DateRecvd <= #" & txtThrough + " 11:59PM" & "#"
Haven't thought through how it would look with php, but it might give you an idea.
|
|
|
|
Lee Irving
|
| Posted: 12/10/2002, 4:18 AM |
|
Capella it will work.
There is no reason why your function cannot return a full string ie
"Where StartDate >= '01/01/2002' AND StartDate <= '31/12/2002'"
You could even change your grid from being a table view to pure sql and any of the SQL can be changed to a parameter including the table etc. Here is an example of parametising SQL in CCS.
http://www.gotocode.com/art.asp?art_id=198&
|
|
|
|
|
