djgjohng
Posts: 28
|
| Posted: 11/29/2006, 12:16 AM |
|
The available solution for multi-select checkbox and multi-select list box searches seems to only work in cases where the search parameters are limited to those from the listbox or check-box list. At least that's what I have figured out (but I will leave the reasoning to the experts for now.)
This method uses a session variable to store the array (list of values) which you with then reference in your SQL search statement. Thanks to DonB for the array code.
STEP 1:
Create your search form with a checkbox list or multi-select list box as per the Example Pack 1 example. A checkbox list is set up the same way.
Set the data type for the list as Integer (not text).
Set your return page to the page where you have a grid to display the search results (can be the same page.) Don't include the Before Build Select code in the example.
See Step 3 about changing the data source to SQL.
STEP 2:
On the grid page create a page After Initialize event - custom code as follows:
(Change 's_projectID' to whatever your list box or checkbox search component is named.)
Code:
$project_list = CCGetParam("s_projectID", "");
reset($project_list);
if (count($project_list) > 0 AND is_array($project_list))
{
CCSetSession("search_list",(implode(",",$project_list)));
} else CCSetSession("search_list",(CCGetParam("s_projectID","")));
STEP 3:
When you created the grid you probably used the visual query builder (VQB) to create any required table joins.
Make sure you have all query statements right. Don't add a WHERE parameter in regard to the search checkbox/listbox list. You will do this in a moment.
Click on the top SQL view button and copy the code (select, Ctrl+c) that displays there. Close the VQB and change the data source type for the grid to SQL. Then click on '...' button and paste the SQL code you copied. This saves a lot of time.
Click on + Sql Parameters and add the following:
Variable name: my_search (or whatever you want - no spaces)
Type: Text
Parameter: search_list (or whatever you called the session variable set in Step 2)
Type: Session
Default value: "0"
Remove any other variables that you might have previously created in regard to the same field reference.
STEP 4:
In the WHERE clause of the query add the following:
(Change 'mytable' to the name of the table that the search relates to, and 'projectID' to the name of the field that holds the value you are seeking, and 'my_search' to the name of your SQL parameter as set in Step 3.)
Code:
AND mytable.projectID IN ({my_search})
STEP 5:
Test it!
Add the following as a grid Before Show event:
(Change $your_grid_name)
echo "sql clause:" . $your_grid_name->ds->SQL . "<br>";
echo "sql where:" . $your_grid_name->ds->Where . "<br>";
echo "sql order by:" . $your_grid_name->ds->Order . "<br><br><br><br><br><br><br><br>";
$this_list = CCGetSession("my_search","");
echo $this_list."<br><br>";
EXTRAS:
Checkout the following link for a piece of Javascript to select / deselect all check boxes in a form:
http://www.ilovejackdaniels.com/blog/select-all-javascr...ng-to-an-array/
That should do the job. Good luck!
|
 |
 |
djgjohng
Posts: 28
|
| Posted: 11/29/2006, 3:33 PM |
|
ON-GOING PROBLEM WITH THE ABOVE
You will probably discover that the grid page will display (mostly right) but that you will have problems with selecting or changing fields controls in the grid.
Maybe I have it all wrong but the problem appears to stem from the fact that the MySql IN function is as follows:
AND mytable.projectID IN ({my_search})
> IN (1,2,3)
To keep CCS happy you would have to create it with a single quote around '{my_search}'
AND mytable.projectID IN ('{my_search}')
> IN ('1,2,3')
This produces very unreliable results if any.
The CCS example obviously works, but I can't figure out why it works. So if someone has the answer about the parameter issue I would love to hear about it.
|
|
|
|
djgjohng
Posts: 28
|
| Posted: 11/29/2006, 3:51 PM |
|
AND ANOTHER THING...
I have tried replacing the comma separator with ',' (adding single quotes around each number) before storing in the variable:
1','2','3','4','5
So the result would be:
AND mytable.projectID IN ('{my_search}')
> IN ('1','2','3','4','5')
This would be an acceptable IN statement. But the parameter variable stores this with escape slashes / (regardless of PHP magic_quotes being on or off)
End result - doesn't work. Again - any ideas are welcome.
|
|
|
|
|
