2. PHPRunner General questions
  3. Search fields in Charts using MSSQL database
This topic is locked

Search fields in Charts using MSSQL database

 		1/25/2012 7:30:56 PM
PHPRunner General questions
E
 enriquecampos author

Hi,

I am having problems using the search functionality inside the Charts connecting to a MSSQL Database. I have similar application running properly connecting to MySQL database, but MSSQL sql select works a little different and don't produce the right results. I need to be able to search/filter the data while in a chart connecting to MSSQL.
Sample MySQL select statement:

SELECT

forms.academicYear,

sites.schoolType AS SchoolType,

forms.siteID,

sites.name AS SchoolName,

forms.agRequirement,

forms.period_Time,

forms.department,

forms.substitute,

forms.year,

forms.month,

forms.day,

forms.date,

COUNT(forms.siteID) AS Total

FROM forms

INNER JOIN sites ON forms.siteID = sites.ID

GROUP BY forms.siteID
Screenshot:


Please let me know if it possible to do something like this but using MSSQL database.
Sergey Kornilov admin 1/25/2012

This query will work the same way in SQL Server, there is nothing MySQL specific in there.
Just make sure you removed backticks ` characters, they are field name wrappers in MySQL only.
E
 		enriquecampos author 1/26/2012



This query will work the same way in SQL Server, there is nothing MySQL specific in there.
Just make sure you removed backticks ` characters, they are field name wrappers in MySQL only.


please look at the this screenshot of my application connecting to MSSQL database:


the problem is that MSSQL is giving me is that if I use the COUNT in the select then it wants all the fields to be in a GROUP BY and if I add every single field to the GROUP BY then the results on the chart are incorrect.
GROUP BY siteID, academicYear,period_Time, substitute, [date]
any suggestion or what I may be doing wrong?
Sergey Kornilov admin 1/26/2012

You will need to remove columns from this query that are not part of GROUP BY query or do not participate in aggregate functions.
E
 		enriquecampos author 1/27/2012



You will need to remove columns from this query that are not part of GROUP BY query or do not participate in aggregate functions.


I want to provide search functionality by some columns, as I am able to do connecting to MySQL. If I remove the columns from the Select then I will not be able to use them in the search panel. Any idea on how I can have this chart with the search functionality using MSSQL. If this is not possible I may have to create multiple charts to provide this results. Any ideas or suggestions are appreciated.
Sergey Kornilov admin 1/27/2012

I would suggest to ask this question in MS SQL Server related newsgroups or at http://stackoverflow.com.
Once you figured out your query PHPRunner will accept it.