hi am beginner in php.. please help me..
rough explanation:
user will select orgname and the following code will get the orgname and import data from mssql. i wanted to filter those data according to groupname and count number of users access to the system in month basis according to date in logtime. (I simply print the output in code for checking purpose)

e.g the output will be :

count of logtime
groupname jan feb march....
A 3 1 2
B 1 4 5

is it possible to use SQL query on mssql_fetch_array or please suggest me a solution. thank you.

<?php
$variable=$_REQUEST['orgname']; 
$database="ibase"; 
mssql_connect ("SQLEXPRESS", "testuser", "testuser123"); 
@mssql_select_db($database) or die( "Unable to select database");
$result = mssql_query( "SELECT     COUNT(*) AS count, dbo.Reg_Group.RegID,     dbo.Reg_Group.GroupName, dbo.Reg_Group.GroupID,
 	       				dbo.Reg_Group.UserType, dbo.Reg_Group.Domainid, dbo.Reg_Group.trialgroup, dbo.Reg_Group.orgid, 
						dbo.Reg_User.ID, dbo.Reg_User.UserID, dbo.Reg_User.atype, 
                        dbo.Reg_Org.orgid AS orgzid, dbo.Reg_Org.orgname, dbo.Reg_Org.domainid AS domain, dbo.Reg_Org.create_date, 
                        dbo.Reg_Org.usertype AS usertype1, dbo.LoginLog.logintime AS logtime
             FROM     dbo.Reg_Group INNER JOIN
                      dbo.Reg_User ON dbo.Reg_Group.RegID = dbo.Reg_User.RegID INNER JOIN
                      dbo.Reg_Org ON dbo.Reg_Group.orgid = dbo.Reg_Org.orgid INNER JOIN
                      dbo.LoginLog ON dbo.Reg_User.UserID = dbo.LoginLog.userid INNER JOIN
                      dbo.User_Login ON dbo.Reg_User.UserID = dbo.User_Login.UserID
               WHERE    (dbo.Reg_Org.usertype = 's') AND (dbo.Reg_Org.orgname ='$variable')
               GROUP BY dbo.Reg_Group.RegID, dbo.Reg_Group.GroupName, dbo.Reg_Group.GroupID, dbo.Reg_Group.UserType, 
			           dbo.Reg_Group.Domainid, 
                      dbo.Reg_Group.trialgroup, dbo.Reg_Group.orgid, dbo.Reg_User.ID, dbo.Reg_User.RegID, dbo.Reg_User.UserID,
					   dbo.Reg_User.atype, 
                      dbo.Reg_Org.orgid, dbo.Reg_Org.orgname, dbo.Reg_Org.domainid, dbo.Reg_Org.create_date, dbo.Reg_Org.usertype,
					   dbo.LoginLog.logintime" ) or die("query error"); 
$num_rows = mssql_num_rows($result); 
print "There are $num_rows records.<P>"; 
print "<table width=200 border=1>\n"; 
while ($get_info = mssql_fetch_row($result)){ 
print "<tr>\n"; 
foreach ($get_info as $field) 
print "\t<td><font face=arial size=1/>$field</font></td>\n"; 
print "</tr>\n"; 
} 
print "</table>\n"; 
?>

this is expected output
groupname jan feb march....
A 3 1 2
B 1 4 5

then post table structure and sample data here

hi,
i have altered my query like this :

SELECT C1.GroupName,MONTH(C1.logtime)AS month, COUNT(*)as countoflogtime FROM
(query1) as C1
GROUP BY  C1.GroupName ,MONTH(C1.logtime)

it extracting the value from the results of query1.The result displayed was grouped by month and than followed by GroupName but i want to group it by GroupName only. when i remove

MONTH(C1.logtime)

from

GROUP BY

, am getting the following error : Column 'C1.logtime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
where it went wrong?? please help me.. thank you.

Following is the code for expected output, it is not compiled you may have to correct syntax error if any. Before running code, check whether query runs in your phpmyadmin or not.

<?php
$orgname="SCSW";

$querymain="SELECT C1.GroupID,
sum(if(MONTH(C1.logtime)=1,1,0))  Jan, 
sum(if(MONTH(C1.logtime)=2,1,0))  Feb, 
sum(if(MONTH(C1.logtime)=3,1,0))  Mar, 
sum(if(MONTH(C1.logtime)=4,1,0))  Apr, 
sum(if(MONTH(C1.logtime)=5,1,0))  May, 
sum(if(MONTH(C1.logtime)=6,1,0))  Jun, 
sum(if(MONTH(C1.logtime)=7,1,0))  Jul, 
sum(if(MONTH(C1.logtime)=8,1,0))  Aug, 
sum(if(MONTH(C1.logtime)=9,1,0))  Sep, 
sum(if(MONTH(C1.logtime)=10,1,0))  Oct, 
sum(if(MONTH(C1.logtime)=11,1,0))  Nov, 
sum(if(MONTH(C1.logtime)=12,1,0))  Dec,
count(*) total
tablename as C1
where orgname='{$orgname}'
GROUP BY  C1.GroupID ";

$resultmain=mysql_query($querymain);
echo "<table>";
echo "<tr><td>group</td>  <td>jan</td>  <td>feb</td>................. <td>Grand total</td>  </tr>";
while($rowmain=mysql_fetch_array($resultmain))
{


   echo "<tr><td>{$rowmain['GroupId']}</td>  <td>{$rowmain['Jan']}</td>  <td>{$rowmain['Feb']}</td>.................<td>{$rowmain['total']}</td>   </tr>";

   $querysub="SELECT C1.GroupName ,
   sum(if(MONTH(C1.logtime)=1,1,0))  Jan, 
   sum(if(MONTH(C1.logtime)=2,1,0))  Feb, 
   sum(if(MONTH(C1.logtime)=3,1,0))  Mar, 
   sum(if(MONTH(C1.logtime)=4,1,0))  Apr, 
   sum(if(MONTH(C1.logtime)=5,1,0))  May, 
   sum(if(MONTH(C1.logtime)=6,1,0))  Jun, 
   sum(if(MONTH(C1.logtime)=7,1,0))  Jul, 
   sum(if(MONTH(C1.logtime)=8,1,0))  Aug, 
   sum(if(MONTH(C1.logtime)=9,1,0))  Sep, 
   sum(if(MONTH(C1.logtime)=10,1,0))  Oct, 
   sum(if(MONTH(C1.logtime)=11,1,0))  Nov, 
   sum(if(MONTH(C1.logtime)=12,1,0))  Dec,
   count(*) total

   tablename as C1
   where orgname='{$orgname}' and groupID='{$rowmain['GroupID']}'
   GROUP BY  C1.GroupName ";



   $resultsub=mysql_query($querysub);
   while($rowsub=mysql_fetch_array($resultsub))
   {
	   echo "<tr><td>{$rowsub['GroupName']}</td>  <td>{$rowsub['Jan']}</td>  <td>{$rowsub['Feb']}</td>.................<td>{$rowsub['total']}</td>   </tr>";	 

   }
}
echo "</table>";
?>
tablename as C1

there is no specific table. I need to get the value from the resulting output of the query I posted earlier. here I made nested query : ......FROM
(query1) as C1 am getting error : the multi-part identifier "C1.Group" cannot be bound. / the multi-part identifier "C1.logtime" cannot be bound.

what it meant for?? and how to fix it?

can you post your mysql table(S) structure with sample data in sql query format. NOT EXCEL

sql file with table structure and sample data :
http://www.mediafire.com/?amte53a9mhvbdj6

I also provided query(q1) which getting data from all the tables. between can I create the resulting output from(q1) as new table in mssql itself??

The following query gives expected output but its grouped by month followed by C1.GroupID. i want it to be grouped by C1.GroupID only.. if remove "Month(C1.logtime)" from GROUP BY clause am getting error : Column 'C1.logtime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT C1.GroupID, 
Case Month(C1.logtime)
When 1 Then 'Jan'
When 2 Then 'Feb'
When 3 Then 'Mar' 
When 4 Then 'April'
When 5 Then 'May'
When 6 Then 'June'
When 7 Then 'July'
When 8 Then 'Aug'
When 9 Then 'Sept'
When 10 Then 'Oct'
When 11 Then 'Nov'
When 12 Then 'Dec'
End AS mon,
COUNT(*) AS total
FROM (SELECT     COUNT(*) AS count, dbo.Reg_Group.RegID, dbo.Reg_Group.GroupName, dbo.Reg_Group.GroupID, dbo.Reg_Group.UserType, 
                      dbo.Reg_Group.Domainid, dbo.Reg_Group.trialgroup, dbo.Reg_Group.orgid, dbo.Reg_User.ID, dbo.Reg_User.UserID, dbo.Reg_User.atype, 
                      dbo.Reg_Org.orgid AS orgzid, dbo.Reg_Org.orgname, dbo.Reg_Org.domainid AS domain, dbo.Reg_Org.create_date, 
                      dbo.Reg_Org.usertype AS usertype1, dbo.LoginLog.logintime AS logtime
FROM         dbo.Reg_Group INNER JOIN
                      dbo.Reg_User ON dbo.Reg_Group.RegID = dbo.Reg_User.RegID INNER JOIN
                      dbo.Reg_Org ON dbo.Reg_Group.orgid = dbo.Reg_Org.orgid INNER JOIN
                      dbo.LoginLog ON dbo.Reg_User.UserID = dbo.LoginLog.userid INNER JOIN
                      dbo.User_Login ON dbo.Reg_User.UserID = dbo.User_Login.UserID
WHERE    (dbo.Reg_Org.usertype = 's') AND (dbo.Reg_Org.orgname ='SCSJ')
GROUP BY dbo.Reg_Group.RegID, dbo.Reg_Group.GroupName, dbo.Reg_Group.GroupID, dbo.Reg_Group.UserType, dbo.Reg_Group.Domainid, 
                      dbo.Reg_Group.trialgroup, dbo.Reg_Group.orgid, dbo.Reg_User.ID, dbo.Reg_User.RegID, dbo.Reg_User.UserID, dbo.Reg_User.atype, 
                      dbo.Reg_Org.orgid, dbo.Reg_Org.orgname, dbo.Reg_Org.domainid, dbo.Reg_Org.create_date, dbo.Reg_Org.usertype, dbo.LoginLog.logintime) AS C1
GROUP BY  C1.GroupID,Month(C1.logtime)

I have tried to create a query like it is expected in excel file

give mysql login parameter to this file

save that file as php and run it

am getting this error from the following query Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'where'. (severity 15) in C:\xampp\htdocs\MySite\try.php on line 83

Warning: mssql_query() [function.mssql-query]: Query failed in C:\xampp\htdocs\MySite\try.php on line 83

Warning: mssql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\MySite\try.php on line 84

$querysub="SELECT C1.GroupName, 
Case Month(C1.logtime)
When 1 Then 'January'
When 2 Then 'February'
When 3 Then 'March' 
When 4 Then 'April'
When 5 Then 'May'
When 6 Then 'June'
When 7 Then 'July'
When 8 Then 'August'
When 9 Then 'September'
When 10 Then 'October'
When 11 Then 'November'
When 12 Then 'December'
End AS MNT,
COUNT(*) AS total
FROM ({$query1}) AS C1
GROUP BY  C1.GroupID,Month(C1.logtime)
where  C1.GroupID='{$rowmain['GroupID']}'   
GROUP BY  C1.GroupName,Month(C1.logtime)";

I hope you have not changed the code, because it is working here

before this like

$resultsub=mysql_query($querysub);

add one more line it shold look like this

echo $querysub;
 $resultsub=mysql_query($querysub);

post that generated query here

generated query :

SELECT C1.GroupName, Case Month(C1.logtime) When 1 Then 'January' When 2 Then 'February' When 3 Then 'March' When 4 Then 'April' When 5 Then 'May' When 6 Then 'June' When 7 Then 'July' When 8 Then 'August' When 9 Then 'September' When 10 Then 'October' When 11 Then 'November' When 12 Then 'December' End AS MNT, COUNT(*) AS total FROM (SELECT COUNT(*) AS count, Reg_Group.RegID, Reg_Group.GroupName, Reg_Group.GroupID, Reg_Group.UserType, Reg_Group.Domainid, Reg_Group.trialgroup, Reg_Group.orgid, Reg_User.ID, Reg_User.UserID, Reg_User.atype, Reg_Org.orgid AS orgzid, Reg_Org.orgname, Reg_Org.domainid AS domain, Reg_Org.create_date, Reg_Org.usertype AS usertype1, LoginLog.logintime AS logtime FROM Reg_Group INNER JOIN Reg_User ON Reg_Group.RegID = Reg_User.RegID INNER JOIN Reg_Org ON Reg_Group.orgid = Reg_Org.orgid INNER JOIN LoginLog ON Reg_User.UserID = LoginLog.userid INNER JOIN User_Login ON Reg_User.UserID = User_Login.UserID WHERE (Reg_Org.usertype = 's') AND (Reg_Org.orgname ='SCSJ') GROUP BY Reg_Group.RegID, Reg_Group.GroupName, Reg_Group.GroupID, Reg_Group.UserType, Reg_Group.Domainid, Reg_Group.trialgroup, Reg_Group.orgid, Reg_User.ID, Reg_User.RegID, Reg_User.UserID, Reg_User.atype, Reg_Org.orgid, Reg_Org.orgname, Reg_Org.domainid, Reg_Org.create_date, Reg_Org.usertype, LoginLog.logintime) AS C1 GROUP BY C1.GroupID,Month(C1.logtime) where C1.GroupID='ADP(09/08/07)' GROUP BY C1.GroupName,Month(C1.logtime)

I just change month to switch type because am getting syntax error from your query and added "Month(C1.logtime)" at group by clause because getting error : "Column 'C1.logtime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

Now you have changed the query drastically, Now don't you want output in 12 months columns.
whenever you get error, post output query with your post.

You please simply run my original e3.php(do not add anything except orgname and mysql login values), it is already formatted in that format. You do not have to worry about query.

PLEASE DO NOT CHANAGE ANYTHING (except orgname and mysql login values).

Here is output(output.jpg) for what you send me in sql file and main php file (e3.txt)(attaching again).

the following displayed without any change of your code sir.. :(
Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'if'. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near ','. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: message: Incorrect syntax near the keyword 'as'. (severity 15) in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_query() [function.mssql-query]: Query failed in C:\xampp\htdocs\MySite\try1.php on line 43

Warning: mssql_fetch_array() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\MySite\try1.php on line 60
group jan feb mar apr may jun jul august sep oct nov dec Grand total

echo the query and post its output here

echo $querymain;
$resultmain=mysql_query($querymain);
SELECT C1.GroupID, sum(if(MONTH(C1.logtime)=1,count1,0)) January, sum(if(MONTH(C1.logtime)=2,count1,0)) February, sum(if(MONTH(C1.logtime)=3,count1,0)) March, sum(if(MONTH(C1.logtime)=4,count1,0)) April, sum(if(MONTH(C1.logtime)=5,count1,0)) May, sum(if(MONTH(C1.logtime)=6,count1,0)) June, sum(if(MONTH(C1.logtime)=7,count1,0)) July, sum(if(MONTH(C1.logtime)=8,count1,0)) August, sum(if(MONTH(C1.logtime)=9,count1,0)) September, sum(if(MONTH(C1.logtime)=10,count1,0)) October, sum(if(MONTH(C1.logtime)=11,count1,0)) November, sum(if(MONTH(C1.logtime)=12,count1,0)) December, sum(count1) total from (SELECT COUNT(*) AS count1, Reg_Group.RegID, Reg_Group.GroupName, Reg_Group.GroupID, Reg_Group.UserType, Reg_Group.Domainid, Reg_Group.trialgroup, Reg_Group.orgid, Reg_User.ID, Reg_User.UserID, Reg_User.atype, Reg_Org.orgid AS orgzid, Reg_Org.orgname, Reg_Org.domainid AS domain, Reg_Org.create_date, Reg_Org.usertype AS usertype1, LoginLog.logintime AS logtime FROM Reg_Group INNER JOIN Reg_User ON Reg_Group.RegID = Reg_User.RegID INNER JOIN Reg_Org ON Reg_Group.orgid = Reg_Org.orgid INNER JOIN LoginLog ON Reg_User.UserID = LoginLog.userid INNER JOIN User_Login ON Reg_User.UserID = User_Login.UserID WHERE (Reg_Org.usertype = 's') AND (Reg_Org.orgname ='SCSJ') GROUP BY Reg_Group.RegID, Reg_Group.GroupName, Reg_Group.GroupID, Reg_Group.UserType, Reg_Group.Domainid, Reg_Group.trialgroup, Reg_Group.orgid, Reg_User.ID, Reg_User.RegID, Reg_User.UserID, Reg_User.atype, Reg_Org.orgid, Reg_Org.orgname, Reg_Org.domainid, Reg_Org.create_date, Reg_Org.usertype, LoginLog.logintime) as C1 GROUP BY C1.GroupID

copy this and see are you able to run this query in your phpmyadmin, I do not have all data as you so it is not giving any error to me.

am using ms sql server.. and getting same error while running the query at mssql.

I was in impression that you are using MYSQL. In MSSQL "if" will not work, we need to change it to "case when then". Ok let me check and I will come back soon.

wait this is very poor query. We will get the optimzed one soon.

check this

i will say ABP, CIB, BAFF as CATEGORY
and ABP(JUN-2010), BAFF -JUN-JUL-10 AS GROUPID.

in your reg_group table you do not have category, so for precise report, you must add one column to reg_group that is CATEGORY.

groupid, groupname, category
ABP(JUNJUL10), ABP(JUNJUL10), ABP
BAFF-JUL 10, BAFF-JUL 10, BAFF


IF YOU DO NOT WANT TO ADD MORE COLUMN THEN you may use trialgroup column if it is blank. you can add ABP, BAFF in trialgroup column.

Then we have to just modify query at few places.

So tell me what are you going to do now.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.