Page 1 of 1

how can i use crosstable in mysql

Posted: Wed Jul 08, 2009 9:06 am
by manojsemwal1
hai everyone...
i want generate report lke
-----------------------------------------------
University| A-Level | MCA | Total
---------|-------------- !-------------------
DOEACC | 1 | 0 | 1
MCRP | 0 | 1 | 1
__________________________________________

Course Name is coming from database and the University name is also coming from database..

iam using below query..
$sql="select count(Course) as no,University,Course from registeredstudenttb where Instid='$Sname' group by University,Course";
//echo $sql;
$rs=mysql_query($sql)or die(mysql_error());
//$count=mysql_num_rows($rs);
$sql4="select count(Course) as no,University,Course from registeredstudenttb where Instid='$Sname' group by University,Course";
//echo $sql;
$rs4=mysql_query($sql4)or die(mysql_error());

$sql1="SELECT DISTINCT `University` AS `University` FROM `registeredstudenttb`where Instid='$Sname' GROUP BY `University`";
//echo $sql1;
$rs1=mysql_query($sql1)or die(mysql_error());

//$c1= mysql_num_rows($rs1);
echo $c1;
$count1=mysql_num_rows($rs1);
//echo $c1;










$n = 1;
echo"<tr><th>University</th>";
while ($row = mysql_fetch_assoc($rs4))
{
$s1=$row['Course'];
//echo $s1;
echo'<th>'. $row['Course'].$row['no'].'</th>';

}
echo" </td></tr>";
echo"<tr>";
//while ($row = mysql_fetch_assoc($rs1))
while ($row = mysql_fetch_assoc($rs1))
{
$univ=$row['University'];
echo $univ ."<br>";
echo'<td>'. $row['University'].'</td>';


while ($row = mysql_fetch_assoc($rs))

{
$s1=$row['Course'];
echo $s1 ."<br>";
echo $count1;
$sql3="SELECT `University`, SUM(IF(`Course`='$s1',1,0)) AS C1 FROM `registeredstudenttb` WHERE (`University` = '$univ' and Instid='$Sname') GROUP BY `University` ORDER BY `University` ASC";
echo $sql3;
$rs3=mysql_query($sql3)or die(mysql_error());
while ($row = mysql_fetch_assoc($rs3))
{
echo'<td>'. $row['C1'].'</td>';
$n++;

}
$n++;
}
echo '</tr>';

$n++;
}





?>
can any one help me for that .

Thanks