datadoit
|
| Posted: 08/20/2008, 9:46 AM |
|
CCS 3.2; MySQL5; PHP5
I have a grid with a Data Source of SQL, and here's the SQL:
SELECT clid, calldate, FROM_UNIXTIME(UNIX_TIMESTAMP(calldate) -
UNIX_TIMESTAMP(calldate) % 240) AS slice
FROM cdr
GROUP BY clid, slice
ORDER BY calldate desc
What this does is group similar records within a certain time period
(couple of minutes). This SQL works perfectly getting the records sought.
However, when the grid is displayed, it shows grid error:
Database Error: Unknown column 'slice' in 'group statement'
This is because CodeCharge doesn't know how to calculate the CountSQL.
So, in the grid's Before Execute Select event, I add:
$Container->ds->CountSQL = "SELECT clid, calldate,
FROM_UNIXTIME(UNIX_TIMESTAMP(calldate) - UNIX_TIMESTAMP(calldate) % 240)
AS slice FROM cdr GROUP BY clid, slice";
Which just produces some seemingly meaningless number that doesn't match
the actual number of records that were pulled and display in the grid.
How do I get the CountSQL right? or better yet, why does CodeCharge pull
up the correct records, but can't get the count of those right?
|
|
|
 |
wkempees
Posts: 1679
|
| Posted: 08/20/2008, 10:11 AM |
|
Quick reaction on my way out:
$Container->ds->CountSQL = "SELECT Count(*)
FROM cdr
add the COUNT()
loose the group by
create a WHERE clause that comes close to your obtained result
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
 |
|
datadoit
|
| Posted: 08/20/2008, 12:54 PM |
|
Well I've concluded (right or wrong) that it's impossible to get the
result set I need without grouping. Even trying a subselect with the
grouping doesn't pass syntax.
I can get the number of records from the SQL with no problems. Even in
Before Execute Select I can:
$sql = "SELECT clid, calldate, FROM_UNIXTIME(UNIX_TIMESTAMP(calldate) -
UNIX_TIMESTAMP(calldate) % 240) AS slice FROM cdr GROUP BY clid, slice"
$db = new clsDBPBXware();
$Records = mysql_num_rows($db->query($SQL));
$db->close();
and it gives me the correct number of records. Now if I try:
$Container->ds->CountSQL = $Records;
it won't let me. Can I at least 'force' the record count into CountSQL
in some way?
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/21/2008, 5:22 AM |
|
I have tried a few (obvious) solutions but cannot validate the resutl against your tables.
Last question first:
Quote :
Can I at least 'force' the record count into CountSQL
in some way?
- create a small table dd_count, ( ID varchar , ROWCNTD integer)
Before Execute Select:
$sql = "SELECT clid, calldate, FROM_UNIXTIME(UNIX_TIMESTAMP(calldate) -
UNIX_TIMESTAMP(calldate) % 240) AS slice FROM cdr GROUP BY clid, slice"
$db = new clsDBPBXware();
$Records = mysql_num_rows($db->query($SQL));
$SQL = "REPLACE into dd_record set ID='slicecount', ROWCNTD=". $db->ToSQL($Records, ccsInteger);
$db-execute($SQL);
$db->close();
Now you can do the
$Container->ds->CountSQL = 'Select ROWCNTD from dd_record where ID = "slicecount" ';
in BeforeExecuteSelect
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/21/2008, 5:30 AM |
|
The above, shorthand version, without dd_count table:
Before Execute Select:
$sql = "SELECT clid, calldate, FROM_UNIXTIME(UNIX_TIMESTAMP(calldate) -
UNIX_TIMESTAMP(calldate) % 240) AS slice FROM cdr GROUP BY clid, slice"
$db = new clsDBPBXware();
$Records = mysql_num_rows($db->query($SQL));
$db->close();
$Container->ds-CountSQL='Select ' . $Records ;
Walter
Keep me posted.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
datadoit
|
| Posted: 08/21/2008, 7:33 AM |
|
BRILLIANT!!
Here's the final code in BeforeExecuteSelect:
<code>
//Remove the order by - not needed for the count anyways.
$SQL = rtrim($Container->ds->SQL, "{SQL_OrderBy}");
//Do a separate query to get the row count only.
$db = new clsDBPBXware();
$Records = mysql_num_rows($db->query($SQL));
$db->close();
//Force in a new row count.
$Container->ds->CountSQL = "SELECT " . $Records;
</code>
I already had the original SQL in $Container->ds->SQL, just had to strip
out the OrderBy clause - not needed to get a row count.
The 'trick' in all of this is the "SELECT" in the CountSQL.
*****NOTE: Anytime you have a grid that will have a Data Source of SQL
and a GROUP BY clause in it, you'll have to do the above so that
CodeCharge can give you an accurate row count, and subsequently an
accurate Navigator. *****
Thanks Walter! I'll now pull for The Netherlands to win a medal in
something. :) I'm diggin' the women's handball team.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/21/2008, 10:04 AM |
|
Thanks.
As to the medals, we did some good ones.
Women's handball nice, but check out the hockey girls!
'Till next challenge!
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
datadoit
|
| Posted: 08/21/2008, 11:03 AM |
|
Just whooped the US in women's water polo!
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 08/21/2008, 1:26 PM |
|
How about this one:
Male 10K Swim marathon.
First time event, Dutch Gold!
Check out the guys history.........
http://www.maartenvanderweijden.com/
Respect!
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
laneoc
Posts: 154
|
| Posted: 02/03/2009, 8:15 PM |
|
Another approach...
$oldSQL=$YOUR_DATA_SOURCE_NAME->DataSource->SQL;
$myCountSQL="select count(*) from (".$oldSQL.") as CountTable";
$YOUR_DATA_SOURCE_NAME->DataSource->CountSQL=$myCountSQL;
Lane
_________________
Lane |
|
|
|
jjrjr1
Posts: 942
|
| Posted: 02/04/2009, 5:59 AM |
|
Hi
Another thought..
Use Dlookup action with the value looked up being the count.
This helps make more portable code than using custom code.
Have fun
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
|
