Ken
|
| Posted: 10/21/2004, 6:02 AM |
|
CCS 2.3.1.85, PHP, MySQL, Apache
Enjoying playing with CCS and seeing what it can do. Working on a sample
project with a search form that feeds parameters to a page with a grid for
which I have finally successfully written code to modify the SQL
beforeexecuteselect event. The SQL includes a Group by statement and the
data is correct in the grid, but the navigator doesn't display the correct
page numbers or links. I just used a general table type choice of data
source for the grid at design-time and use the custom SQL at run-time.
I searched the newsgroups and found a few posts which hinted at the problem,
but didn't actually fix it. I have copied part of the most promising
solution below, but I can't really make it work. Anyone have any sample
code/projects/snippets to get me through this? Seems that it should be a
simple fix. It's a good enough product, that I will probably buy even if
it's a complicated fix, but I want there to be a fix.
Thanks
Steve
______________________________
'Select count(*)' will give multiple result if 'group by' option is added,
and
I believe that CCS only takes the first row value as the result. Now I am
doing
workaround by 'wrapping' the $this->CountSQL in the grid BeforeExecuteSelect
event as follows:
// -------------------------
global $t_sales_grid;
// Write your own code here.'
$t_sales_grid->ds->CountSQL = "select count(*) from (" .
$t_sales_grid->ds->CountSQL . ") as tmp ";
// -------------------------
I think CCS should take care of counting the correct row when the sql
statement
contains group by option.
|
|
|
 |
Don Safar
|
| Posted: 10/21/2004, 8:20 AM |
|
You probably want to move the custom code to the beforebuildselect event.
Depending on the actual sql, you may need to modify the countsql property.
See this post on the forums -
http://forums.codecharge.com/posts.php?post_id=42864&s_keyword=countsql
Hope this helps
|
|
|
|
Zye
Posts: 56
|
| Posted: 04/07/2005, 3:26 PM |
|
CCS 2.3.2.24, PHP, MySQL - I have the same problem as Ken (i know it was a while ago) and I am failing to resolve my problem. My {articles_TotalRecords} label is giving an incorrect count and the grid navigator in turn is not giving the correct page count. I am using a multi select listbox to search my sub categories. The grid SQL looks like this. It works fine.
SELECT DISTINCT articles.article_id, articles.article_title, articles.article_date, articles.cat_id, cat_name
FROM (articles LEFT JOIN categories ON
articles.cat_id = categories.cat_id) LEFT JOIN
art_cats ON articles.article_id = art_cats.article_id
WHERE (articles.article_title LIKE '%{s_article_title}%'
OR articles.article_body LIKE '%{s_article_title}%')
AND (art_cats.sub_cat_id IN ({s_sub_cats}) OR '{s_sub_cats}'='0')
ORDER BY articles.article_date DESC
>> I have some code in {BeforeExecuteSelect} to get the search result/value for my catergory select listbox.
global $articles;
global $DBdiversity;
if (strlen(CCGetParam("s_cat_id", "")))
{
$articles->ds->SQL .= " AND categories.cat_id = ". $DBdiversity->ToSQL(CCGetParam("s_cat_id", ""), ccsText);
$articles->ds->CountSQL .= " AND categories.cat_id = ". $DBdiversity->ToSQL(CCGetParam("s_cat_id", ""), ccsText);
}
>> In grid event {AfterExecuteSelect} I have code from support to rectify my {articles_TotalRecords} count. It works if I set my records per page count to more than the articles in the DB.
global $articles;
$articles->articles_TotalRecords->SetValue($articles->ds->RecordsCount);
The solution in the post from Ken above looks promising. Has anyone solved a similar problem?
Thanks ...
|
 |
|
peterr
Posts: 5971
|
| Posted: 04/07/2005, 3:46 PM |
|
If you use DISTINCT or any other complex SQL then CCS may not be able to create CountSQL correctly. It's best to write your whole SQL count statement from scratch and test it against the database first, then assign to ds->CountSQL.
Also, I suspect that you're manipulating the ds->SQL incorrectly. It probably should be ds->Where.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
