how can i use crosstable in mysql
Posted: Wed Jul 08, 2009 9:06 am
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
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