I have a table named "pivo" with columns (ID, Name, Course, Marks). I want to convert "Name" column data into row heading, "Course" column data into column headings and "Marks" column data into row data for those columns converted from "Course" column data. Remeber to ignore "ID" column and it's data in selection query.
I tried this:
// Select Students with their Marks Dynamically
$SelectQuery3 = mysqli_query($connection, "SELECT distinct Course FROM pivo");
$numrows3 = mysqli_num_rows($SelectQuery3);
if ( ! $SelectQuery3)
{
die ("Sorry, database selection failed1. " . mysqli_connect_error());
}
else if ($numrows3 != 0)
{
echo "
<caption><h3>Students with their Marks Dynamically Head</h3></caption>
<table border = '1' style = 'border-collapse: collapse;'>
<thead>
<tr>
<th>Name</th>
";
$Crs = "";
while ($display3 = mysqli_fetch_array($SelectQuery3))
{
$Crs = $display3 [0];
echo "<th>{$display3 ['0']}</th>";
}
echo "
<th>Total</th>
<th>Average</th>
</tr>
</thead>
<tbody>";
$SelectQuery4 = mysqli_query($connection, "select `Name`,
sum(case when `Course` = '$Crs' then `Marks` else 0 end) '$Crs'
from pivo group by `Name`");
$numrows4 = mysqli_num_rows($SelectQuery4);
if ( ! $SelectQuery4)
{
die ("Sorry, database selection failed2. " . mysqli_connect_error());
}
else if ($numrows4 != 0)
{
while ($display4 = mysqli_fetch_array($SelectQuery4))
{
echo "
<tr>
<td>{$display4 ['0']}</td>
<td>{$display4 ['1']}</td>
</tr>";
}
}
else
{
echo "<div class='alert alert-info'> Sorry, no student found yet. </div>";
}
echo "
</tbody>
</table>";
}
else
{
echo "<div class='alert alert-info'> Sorry, no course found yet. </div>";
}
and get wrong result.
Someone could help.
Thank You.