0

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"; 
?>

Edited by Ichcha: n/a

2
Contributors
34
Replies
35
Views
6 Years
Discussion Span
Last Post by Ichcha
0

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

then post table structure and sample data here

0

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.

Edited by Ichcha: n/a

0

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>";
?>

Edited by urtrivedi: n/a

0
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?

0

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)

Edited by Ichcha: n/a

0

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

Attachments
<?php 		
//	error_reporting(E_ALL); 
//	ini_set("display_errors", 1); 
$dblink = mysql_connect("sever","user","pwd") or die(mysql_error());
mysql_select_db("dbname");

$orgname="SCSJ";


$query1="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 ='{$orgname}')                     
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";
                      

$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
 ({$query1}) as C1

GROUP BY  C1.GroupID ";
//echo $querymain;
//where orgname='{$orgname}'

$resultmain=mysql_query($querymain);
echo "<table border=1>";
echo "<tr><td>group</td>";
echo "  <td>jan</td>  ";
echo "<td>feb</td>";
echo "<td>mar</td>";
echo "<td>apr</td>";
echo "<td>may</td>";
echo "<td>jun</td>";
echo "<td>jul</td>";
echo "<td>august</td>";
echo "<td>sep</td>";
echo "<td>oct</td>";
echo "<td>nov</td>";
echo "<td>dec</td>";

echo " <td>Grand total</td>  </tr>";
while($rowmain=mysql_fetch_array($resultmain))
{


   echo "<tr><td><font color='red'>{$rowmain['GroupID']}</font></td>";
   echo "  <td><font color='red'>{$rowmain['January']}</font></td> ";
   echo " <td><font color='red'>{$rowmain['February']}</font></td>";
   echo " <td><font color='red'>{$rowmain['March']}</font></td>";
   echo " <td><font color='red'>{$rowmain['April']}</font></td>";
   echo " <td><font color='red'>{$rowmain['May']}</font></td>";
   echo " <td><font color='red'>{$rowmain['June']}</font></td>";            
   echo " <td><font color='red'>{$rowmain['July']}</font></td>";   
   echo " <td><font color='red'>{$rowmain['August']}</font></td>";
   echo " <td><font color='red'>{$rowmain['September']}</font></td>";
   echo " <td><font color='red'>{$rowmain['October']}</font></td>";
   echo " <td><font color='red'>{$rowmain['November']}</font></td>";            
   echo " <td><font color='red'>{$rowmain['December']}</font></td>";   
   
   echo "<td><font color='red'>{$rowmain['total']}</font></td>   </tr>";

   $querysub="SELECT C1.GroupName ,
	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 ({$query1}) as C1
   where  groupID='{$rowmain['GroupID']}'
   GROUP BY  C1.GroupName ";
//orgname='{$orgname}' and


   $resultsub=mysql_query($querysub);
   while($rowsub=mysql_fetch_array($resultsub))
   {
   echo "<tr><td>{$rowsub['GroupName']}</td>";
   echo "  <td>{$rowsub['January']}</td> ";
   echo " <td>{$rowsub['February']}</td>";
   echo " <td>{$rowsub['March']}</td>";
   echo " <td>{$rowsub['April']}</td>";
   echo " <td>{$rowsub['May']}</td>";
   echo " <td>{$rowsub['June']}</td>";            
   echo " <td>{$rowsub['July']}</td>";   
   echo " <td>{$rowsub['August']}</td>";
   echo " <td>{$rowsub['September']}</td>";
   echo " <td>{$rowsub['October']}</td>";
   echo " <td>{$rowsub['November']}</td>";            
   echo " <td>{$rowsub['December']}</td>";   
   
   echo "<td>{$rowsub['total']}</td>   </tr>";

   }
}
echo "</table>";
?>
0

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)";
0

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

Edited by urtrivedi: n/a

0

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."

0

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.

0

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).

Attachments
<?php 		
//	error_reporting(E_ALL); 
//	ini_set("display_errors", 1); 
$dblink = mysql_connect("sever","user","pwd") or die(mysql_error());
mysql_select_db("dbname");

$orgname="SCSJ";


$query1="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 ='{$orgname}')                     
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";
                      

$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
 ({$query1}) as C1

GROUP BY  C1.GroupID ";
//echo $querymain;
//where orgname='{$orgname}'

$resultmain=mysql_query($querymain);
echo "<table border=1>";
echo "<tr><td>group</td>";
echo "  <td>jan</td>  ";
echo "<td>feb</td>";
echo "<td>mar</td>";
echo "<td>apr</td>";
echo "<td>may</td>";
echo "<td>jun</td>";
echo "<td>jul</td>";
echo "<td>august</td>";
echo "<td>sep</td>";
echo "<td>oct</td>";
echo "<td>nov</td>";
echo "<td>dec</td>";

echo " <td>Grand total</td>  </tr>";
while($rowmain=mysql_fetch_array($resultmain))
{


   echo "<tr><td><font color='red'>{$rowmain['GroupID']}</font></td>";
   echo "  <td><font color='red'>{$rowmain['January']}</font></td> ";
   echo " <td><font color='red'>{$rowmain['February']}</font></td>";
   echo " <td><font color='red'>{$rowmain['March']}</font></td>";
   echo " <td><font color='red'>{$rowmain['April']}</font></td>";
   echo " <td><font color='red'>{$rowmain['May']}</font></td>";
   echo " <td><font color='red'>{$rowmain['June']}</font></td>";            
   echo " <td><font color='red'>{$rowmain['July']}</font></td>";   
   echo " <td><font color='red'>{$rowmain['August']}</font></td>";
   echo " <td><font color='red'>{$rowmain['September']}</font></td>";
   echo " <td><font color='red'>{$rowmain['October']}</font></td>";
   echo " <td><font color='red'>{$rowmain['November']}</font></td>";            
   echo " <td><font color='red'>{$rowmain['December']}</font></td>";   
   
   echo "<td><font color='red'>{$rowmain['total']}</font></td>   </tr>";

   $querysub="SELECT C1.GroupName ,
	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 ({$query1}) as C1
   where  groupID='{$rowmain['GroupID']}'
   GROUP BY  C1.GroupName ";
//orgname='{$orgname}' and


   $resultsub=mysql_query($querysub);
   while($rowsub=mysql_fetch_array($resultsub))
   {
   echo "<tr><td>{$rowsub['GroupName']}</td>";
   echo "  <td>{$rowsub['January']}</td> ";
   echo " <td>{$rowsub['February']}</td>";
   echo " <td>{$rowsub['March']}</td>";
   echo " <td>{$rowsub['April']}</td>";
   echo " <td>{$rowsub['May']}</td>";
   echo " <td>{$rowsub['June']}</td>";            
   echo " <td>{$rowsub['July']}</td>";   
   echo " <td>{$rowsub['August']}</td>";
   echo " <td>{$rowsub['September']}</td>";
   echo " <td>{$rowsub['October']}</td>";
   echo " <td>{$rowsub['November']}</td>";            
   echo " <td>{$rowsub['December']}</td>";   
   
   echo "<td>{$rowsub['total']}</td>   </tr>";

   }
}
echo "</table>";
?>
output.JPG 29.75 KB
0

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

0
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
0

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.

Edited by urtrivedi: n/a

0

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

0

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.

Edited by urtrivedi: n/a

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.