WJM
|
| Posted: 04/29/2003, 1:50 PM |
|
Hi all,
I'm using CCS with PHP and mySQL. I think COUNT (for the navigation on grid forms) is not working with DISTINCT used in query and am looking for some help.
I have a grid form that is using a custom sql to get the data for it. The sql statement is:
SELECT DISTINCT bidcat.bidid AS bidcat_bidid, bidcat.subcatid AS bidcat_subcatid, bid.bidid AS bid_bidid, due, prebid, subcat.*, bidlocation.*, location.*
FROM (((bid INNER JOIN bidcat ON bidcat.bidid = bid.bidid) INNER JOIN bidlocation ON bidlocation.bidid = bid.bidid) INNER JOIN subcat ON subcat.subcatid = bidcat.subcatid) INNER JOIN location ON bidlocation.locationid = location.locationid
WHERE (due >= '{s_due}' or '{s_due}'='0')AND (due <= '{s_due2}' or '{s_due2}'='0') AND (subcat.subcatid = {s_subcatid} or {s_subcatid}='0') AND (countyid = {s_county} or {s_county}='0') AND due <= DATE_SUB(NOW(), INTERVAL 2 DAY) AND aproval = 'Yes'
GROUP BY bidcat.bidid
When I pull up the grid it will only display full pages, for example I allow 10 records per page on the grid, if there are 24 records that match the criteria the navigation will only show 2 pages (each having 10 records) so the last four records are never shown. It also will sometime show only 10 records when there are over 100. If I set the grid up to show up to 100 records it will show the first 100 records but not more unless there are more than 200. It seems that the COUNT feature is not working with this(DISTINCT). Does anyone have any ideas how I can get this to work?
|
|
|
 |
Edd
|
| Posted: 04/30/2003, 12:43 AM |
|
WJM
The problem is not in the distinct but in the GROUP BY.
What you need to do is override the Count SQL which has been automatically generated. You do this in the beforeselect event.
Have a search through the forum and (unfortately) it is a known problem.
If you can't find a solution repost the issue and we will give you a detailed explanation (I code in ASP so I cannot give you a PHP answer)
Hope this helps 
Edd
|
|
|
|
WJM
|
| Posted: 04/30/2003, 2:16 PM |
|
Thanks Edd,
I was able to get the code from cc support and they gave me a great work around. as you said, code needs to be added. I am including it here incase anyone else has this problem:
global $formname;
$formname->ds->RecordsCount = $formname->ds->num_rows();
in the After Execute Select Event of the Grid.
Thanks again
|
|
|
|
|
