saseow
Posts: 744
|
| Posted: 02/05/2009, 6:57 AM |
|
I have a tricky problem (I think) and desperately need some help/advice:
I have a standard grid with search form. The search form has 10 search fields, some text, some checkbox and some date. All work perfectly out of the box. (MySQL) Now.....
1) I want to show dates as "mmm yyyy" which is also no problem but, when it comes to the search it breaks down. What I want to do is: If the user selected 2009-02-16 it shows Jan 2009 which is great. Now, I want to search for all records that have the date in January 2009. I am thinking that the only way to do this is create an SQL that searches all records where the date is between 2008-12-31 and 2009-02-01. There may well be an easier way but I don't know. Anyway, my question is: Where and how do I create the 'WHERE" clause that will modify the form created SQL search? The user may well have entered some text in text fields and checked a few check boxes so I have to have them all come together somehow.
2) I have gone through the posts here and I am still unsure of how to present a date range for the user so he can select all records between two dates. There is only one date field in the record.
Any help, pointers or examples would be great!
|
 |
 |
jjrjr1
Posts: 942
|
| Posted: 02/05/2009, 8:01 AM |
|
Hi
Have you tried looking at Query Builder for the search results grid?
Maybe modify the generated query by CCS to get the results you want?
Should be pretty simple and is where you would want to make your changes to the WHERE clause.
Have fun
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
saseow
Posts: 744
|
| Posted: 02/05/2009, 8:20 AM |
|
Thank you for the reply. So, if I go into the query builder and look at the SQL instead of the table, I can modify this SQL? Won't CCS redo this by itself and take out my modifications? If not, this is perfect.
If I add text box stuff in the search form like date_to, I can simply put that in as well? This sounds too good and easy.
Can you please just confirm that I am on the right track.
Thanks so much.
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 02/05/2009, 8:34 AM |
|
Hi
Yes you are on the right track.
When you select the table and click on ... in the table properties lable, you will see the statements CCS built for the original query.
This is where you would modify and or, in your case, add another where criteria to select between 2 dates.
You might have to programmatically create the start & stop dates to use in the query but you will see all the options available when you see the Query builder screen.
And, no CCS will not over-write your changes if you do it here since it becomes part of the CCS page.
Have fun
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
saseow
Posts: 744
|
| Posted: 02/05/2009, 8:43 AM |
|
Thanks so much jjrjr1. Really appreciate this. Yup, I will create the required dates in the PHP code and then include the extra TO field in this main SQL.
Thanks again and best regards,
Tfrevor
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 02/05/2009, 9:01 AM |
|
Just a hint.
In the query builder you will see several options for the compare field. Any varialbles you create will not be in scope. So you might have to create a function to return the dates to the query and the type would be expression. Or you can use sessions, URL or what ever works for you in this application.
Have fun
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
saseow
Posts: 744
|
| Posted: 02/05/2009, 9:11 AM |
|
I think I have got it but not sure. I will give it a try and see what happens. I don't see any options to compare fields but I presume you mean just the standard LIKE = <+ etc. which you have to type in anyway.
If I use an expression to return the dates to the query, can you let me know where I enter this. I presume it is with the SQL parameters section.
Sorry to be such a pain jjrjr1.
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 02/05/2009, 11:16 AM |
|
In query builder everything is drop downs except for the right hand argument.
If you use a function you just put the function in the right hand argument and select expresion as the type
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
saseow
Posts: 744
|
| Posted: 02/05/2009, 11:31 AM |
|
Thanks jjrjr1, I think I am slowly getting it.
If I run into any more hassles I will post here and just hope that you are online!
Thanks again!
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 02/05/2009, 12:09 PM |
|
No Prob.
Just let me know.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
saseow
Posts: 744
|
| Posted: 02/06/2009, 6:12 AM |
|
OK, it is all done. Thank you so much for all your help.
With best regards,
Trevor
|
|
|
|
damian
Posts: 838
|
| Posted: 02/06/2009, 1:55 PM |
|
trevor - post your solution for others to learn from :)
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
saseow
Posts: 744
|
| Posted: 02/06/2009, 2:08 PM |
|
The solution was to talk the client into accepting yyyy-mm-dd format for the Search boxes. The grid still displays as mmm yyyyy. :)
When I have the time I am going to sort out the search box selection at mmm yyyy but time is very tight right now. I will post once I have it done.
|
|
|
|
