CodeCharge Studio
search Register Login  

Web Reporting

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 Dynamic chart with URL parameters

Print topic Send  topic

Author Message
Dorin

Posts: 50
Posted: 02/25/2014, 3:25 AM

Hello,

I have a simple chart with a SQL data source. In the same page I built a search form for selecting a date interval. I want to pass these two data parameters to the chart's data source.

I added two parameters to the sql: "s_data" and "e_data". The parameters are defined like this:

Variable name: s_data Parameter name: s_data
Variable type: date Parameter type: URL
Variable format: mm/dd/yyyy Parameter format: mm/dd/yyyy
Default value: 01/01/2014
Design Value: 2014-01-01

And this is my SQL:

SELECT
COUNT(0) AS Total,
SUM(IF((closed = 'Y'),1,0)) AS Solved,
SUM(IF((closed = 'N'),1,0)) AS Open,
SUM(IF((closed = 'P'),1,0)) AS Pending,
SUM(IF((closed = 'H'),1,0)) AS OnHold,
SUM(IF((closed = 'R'),1,0)) AS Rejected
FROM tickets
WHERE type = 2
AND data >= '{s_data}'
AND data <= '{e_data}'

The chart shows 0 results. Could anyone show me what is wrong?

Thanks.
View profile  Send private message
eratech


Posts: 513
Posted: 02/25/2014, 5:59 PM

Dorin - yes, those dates can be a hassle. I've done a bunch of them but still manage to muck something up now and then.

Short answer: it looks Ok from here- try dropping the query into your Database to see. Check DB connection parameters

Longer: You appear to have the right set up (parameters named correctly and in the right spots, and the date formats appear ok)

My usual method is to then copy the SQL out of the Query editor and drop it into the database query (phpMyAdmin etc) and run it natively. It usually shows that I've formatted my dates strangely.

DB Connection: Sometimes the strange date format is in the original server connection details - for example, my 'Dev' server connection didn't care about the date format, but the 'Live' server defaulted to 'mm/dd/yyyy H:nn', but I'm in Australia so I prefer 'dd/mm/yyyy' or the ISO standard 'yyyy-mm-dd'. Once I sorted that out, most of the errors went away.

Cheers

Eric
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
Dorin

Posts: 50
Posted: 02/26/2014, 2:31 AM

Hi Eric,

Thanks for your reply. The query works ok against mysql table. Although I made a grid with the same data source and I added a "Before execute select" event for altering the query with the search parameters and it works too.

The only problem I have is this chart.


Quote eratech:
Dorin - yes, those dates can be a hassle. I've done a bunch of them but still manage to muck something up now and then.

Short answer: it looks Ok from here- try dropping the query into your Database to see. Check DB connection parameters

Longer: You appear to have the right set up (parameters named correctly and in the right spots, and the date formats appear ok)

My usual method is to then copy the SQL out of the Query editor and drop it into the database query (phpMyAdmin etc) and run it natively. It usually shows that I've formatted my dates strangely.

DB Connection: Sometimes the strange date format is in the original server connection details - for example, my 'Dev' server connection didn't care about the date format, but the 'Live' server defaulted to 'mm/dd/yyyy H:nn', but I'm in Australia so I prefer 'dd/mm/yyyy' or the ISO standard 'yyyy-mm-dd'. Once I sorted that out, most of the errors went away.

Cheers

Eric
View profile  Send private message
eratech


Posts: 513
Posted: 02/26/2014, 9:22 PM

I'm afraid I haven't use the charts in years.

BTW - what are you doing in the Before Execute Select that can't be done in the Query window?

I've seen other people make changes there, but I've only ever had to do it once, and I'm wondering what I'm missing.

Thanks

E
_________________
CCS 3/4/5 ASP Classic, VB.NET, PHP
Melbourne, Victoria, Australia
View profile  Send private message
Dorin

Posts: 50
Posted: 02/27/2014, 1:37 AM

I used the Before Execute Select event because was easier for me to control the query and date format.

Like this:

$sdate = CCGetParam("s_data","");
$sdata = CCFormatDate(CCParseDate($sdate, array("GeneralDate")), array("yyyy","-","mm","-","dd"," ","HH",":","nn",":","ss"));
$edate = CCGetParam("e_data","");
$edata = CCFormatDate(CCParseDate($edate, array("GeneralDate")), array("yyyy","-","mm","-","dd"," ","HH",":","nn",":","ss"));
if (($sdata != "") or ($edata != "")) {

$total->DataSource->SQL = "SELECT COUNT(0) AS Total,SUM(IF((closed = 'Y'),1,0)) AS Solved,SUM(IF((closed = 'N'),1,0)) AS Open,SUM(IF((closed = 'P'),1,0)) AS Pending,SUM(IF((closed = 'H'),1,0)) AS OnHold,SUM(IF((closed = 'R'),1,0)) AS Refused FROM tickets WHERE type = 2 AND data >='".$sdata."' AND data <= '".$edata."'";
}

But I couldn't reproduce this to charts. I although found that if you modify something, regarding the query parameters, in the event page, the builder goes crazy: it adds another parameter with the same name every time you generate the page. I use CCS 5.1.1.18990 version.

Regards,
Dorin

Quote eratech:
I'm afraid I haven't use the charts in years.

BTW - what are you doing in the Before Execute Select that can't be done in the Query window?

I've seen other people make changes there, but I've only ever had to do it once, and I'm wondering what I'm missing.

Thanks

E
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Web Database

Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.