This php script actually work, but there was a error or i should arranging error it makes
CREATE TABLE IF NOT EXISTS `jss` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`term` varchar(30) NOT NULL,
`idnumber` varchar(30) NOT NULL,
`section` varchar(4) NOT NULL,
`class` varchar(3) NOT NULL,
`subject` varchar(30) NOT NULL,
`score` varchar(30) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
INSERT INTO `jss` (`id`, `term`, `idnumber`, `section`, `class`, `subject`, `score`) VALUES
(1, 'Prelim', 'SBI-JS202', 'Alp', 'ss', 'Computer', '82'),
(2, 'Prelim', 'SBI-JS645', '', 'ss', 'Computer', '63'),
(3, 'Prelim', 'SBI-JS209', '', 'ss', 'Computer', '70'),
(4, 'Prelim', 'SBI-JS209', '', '', 'MTH', '65'),
(5, 'Prelim', 'SBI-JS202', 'Alp', '', 'MTH', '95'),
(6, 'Prelim', 'SBI-JS645', '', '', 'MTH', '45'),
(7, 'Prelim', 'SBI-JS200', '', 'ss', 'Computer', '69'),
(8, 'Prelim', 'SBI-JS200', '', '', 'MTH', '66');
I use this code bellow to determine the total score of each student base on their subject. i got the total calculation but the position is not properly arrange in this format (1st, 2nd, 2nd, 4th). Can someone help me with a code to determine it
<?php
$con = mysql_connect("localhost", "root", "");
if(!$con)
{
echo "Could not establish connection to the database " . mysql_error();
}
$mydb = mysql_select_db("ict", $con);
if (!$mydb)
{
echo "Error selecting database " . mysql_error();
}
?>
<table border = '2'>
<tr>
<td>Student ID</td>
<?php
$subj = mysql_query("SELECT * FROM JSS WHERE section = 'Alp'");
while($rowj = mysql_fetch_array($subj))
{
echo '<th class="rotate"><div>'.$rowj['subject'].'</div></th>';
}
?>
<td>Score</td>
<td>Position</td>
</tr>
<?php
$results = mysql_query("SELECT * FROM JSS WHERE class = 'ss'");
while($row = mysql_fetch_array($results))
{
?>
<tr>
<td style="border-left: 1px solid #C1DAD7"><?php echo $row['idnumber']?>
</td>
<?php $subj = mysql_query("SELECT * FROM JSS WHERE section = 'Alp'");
while($rowj = mysql_fetch_array($subj))
{
?>
<td>
<?php
$endt = mysql_query("SELECT * FROM JSS WHERE idnumber='".$row['idnumber']."' AND subject='".$rowj['subject']."'");
while($rowend = mysql_fetch_array($endt))
{
echo $rowend['score'];
}
?>
</td>
<?php
}
?>
<td>
<?php
$endt = mysql_query("SELECT sum(score) FROM JSS WHERE idnumber= '".$row['idnumber']."'");
while($rowend = mysql_fetch_array($endt))
{
$score = $rowend['sum(score)'];
echo $score;
}
?>
</td>
<td>
The bellow script is where i am having a problem..... i need this arrange (1st, 2nd, 2nd, 4th)
<?php
$mystatement = mysqli_query($con,"SELECT (SELECT COUNT(*) + 1
FROM JSS ref
WHERE ref.score > main.score AND idnumber='".$row['idnumber']."') as rank,
id, term, idnumber, subject, sum(score)
FROM JSS main WHERE idnumber='".$row['idnumber']."'
ORDER BY score DESC");$dbcount = mysql_num_rows($mystatement);
while ($row = mysql_fetch_array($mystatement))
{
echo $row['rank'];
}
?>
</td>
<?php
}
?>