Student subject Positioning based on score using php and mysql database

Ponel · December 27, 2020

Hello sir, My name is Soji. I'm working on a php project on result transcript processing. I got to a stage where i need to rank student based on their score (Subject Position) and also Overall Position.

I have tried all i could but i still don't get it

I have a table called Subject position where with field (id, studentregNo, subjectid, levelid, armsid, yearid, total). what i want is if there is 3 student in a class and the first student score 50 in english and the second student score 45 and the third score 40. i want the system to tell me that the first student position in English is 1st and the second student position in English is 2nd and third student position in English is 3rd. So all this will be applicable to all subjects that the students in a particular level are offering.

Moreover, I need Rank student in a subject in that class, And this will work with each subject for each student in that class.

gw1500se · December 27, 2020

What have you tried? Post your code and be sure to use the code icon (<>) on the menu and specify PHP. List any error messages and/or what you get instead of what you expect.

Ponel · December 27, 2020

Thanks you gw1500se.

I have a project on student ranking. The sample school does three terminal exam in a year. I created a table where i stored all student test and exam according to their RegNo, year(session), semester, level and student arm(i.e A,B,C,D...) and all works perfectly. The problem i'm facing now is the student overall position and subject position. For student subject position, i created a table called subject position where i want to have the position of students based on subject. And for the overall position i created a table that will automatically sum all the student result termly and rank them.

This is my table structure

DROP TABLE IF EXISTS `subject_position`;

CREATE TABLE IF NOT EXISTS `subject_position` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`regNo` varchar(50) NOT NULL,

`subjectid` varchar(50) NOT NULL,

`armsLevelId` varchar(50) NOT NULL,

`armsId` varchar(50) NOT NULL,

`semesterid` varchar(11) NOT NULL,

`yearid` varchar(50) NOT NULL,

`total` varchar(50) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=73 DEFAULT CHARSET=latin1;

--

-- Dumping data for table `subject_position`

--

INSERT INTO `subject_position` (`id`, `regNo`, `subjectid`, `armsLevelId`, `armsId`, `semesterid`, `yearid`, `total`) VALUES

(1, '4663', '1', '1', '1', '1', '1', '72'),

(2, '6073', '1', '1', '1', '1', '1', '73'),

(3, '4663', '2', '1', '1', '1', '1', '47'),

(4, '6073', '2', '1', '1', '1', '1', '61'),

(5, '4663', '3', '1', '1', '1', '1', '82'),

(6, '6073', '3', '1', '1', '1', '1', '61'),

(7, '4663', '4', '1', '1', '1', '1', '99'),

(8, '6073', '4', '1', '1', '1', '1', '95'),

(9, '4663', '5', '1', '1', '1', '1', '70'),

(10, '6073', '5', '1', '1', '1', '1', '100'),

(11, '4663', '6', '1', '1', '1', '1', '69'),

(12, '6073', '6', '1', '1', '1', '1', '67'),

(13, '4663', '7', '1', '1', '1', '1', '77'),

(14, '6073', '7', '1', '1', '1', '1', '80'),

(15, '4663', '8', '1', '1', '1', '1', '58'),

(16, '6073', '8', '1', '1', '1', '1', '77'),

(17, '4663', '9', '1', '1', '1', '1', '96'),

(18, '6073', '9', '1', '1', '1', '1', '96'),

(19, '4663', '10', '1', '1', '1', '1', '78'),

(20, '6073', '10', '1', '1', '1', '1', '77'),

(21, '4663', '11', '1', '1', '1', '1', '48'),

(22, '6073', '11', '1', '1', '1', '1', '88'),

(23, '4663', '12', '1', '1', '1', '1', '69'),

(24, '6073', '12', '1', '1', '1', '1', '94'),

(25, '4663', '1', '1', '1', '2', '1', '28'),

(26, '6073', '1', '1', '1', '2', '1', '70'),

(27, '4663', '2', '1', '1', '2', '1', '68'),

(28, '6073', '2', '1', '1', '2', '1', '59'),

(29, '4663', '3', '1', '1', '2', '1', '68'),

(30, '6073', '3', '1', '1', '2', '1', '70'),

(31, '4663', '4', '1', '1', '2', '1', '81'),

(32, '6073', '4', '1', '1', '2', '1', '72'),

(33, '4663', '5', '1', '1', '2', '1', '84'),

(34, '6073', '5', '1', '1', '2', '1', '72'),

(35, '4663', '6', '1', '1', '2', '1', '58'),

(36, '6073', '6', '1', '1', '2', '1', '72'),

(37, '4663', '7', '1', '1', '2', '1', '71'),

(38, '6073', '7', '1', '1', '2', '1', '70'),

(39, '4663', '8', '1', '1', '2', '1', '48'),

(40, '6073', '8', '1', '1', '2', '1', '55'),

(41, '4663', '9', '1', '1', '2', '1', '66'),

(42, '6073', '9', '1', '1', '2', '1', '51'),

(43, '4663', '10', '1', '1', '2', '1', '37'),

(44, '6073', '10', '1', '1', '2', '1', '58'),

(45, '4663', '11', '1', '1', '2', '1', '57'),

(46, '6073', '11', '1', '1', '2', '1', '59'),

(47, '4663', '12', '1', '1', '2', '1', '67'),

(48, '6073', '12', '1', '1', '2', '1', '69'),

(49, '4663', '1', '1', '1', '3', '1', '94'),

(50, '6073', '1', '1', '1', '3', '1', '82'),

(51, '4663', '2', '1', '1', '3', '1', '69'),

(52, '6073', '2', '1', '1', '3', '1', '76'),

(53, '4663', '3', '1', '1', '3', '1', '63'),

(54, '6073', '3', '1', '1', '3', '1', '81'),

(55, '4663', '4', '1', '1', '3', '1', '81'),

(56, '6073', '4', '1', '1', '3', '1', '77'),

(57, '4663', '5', '1', '1', '3', '1', '72'),

(58, '6073', '5', '1', '1', '3', '1', '83'),

(59, '4663', '6', '1', '1', '3', '1', '78'),

(60, '6073', '6', '1', '1', '3', '1', '83'),

(61, '4663', '7', '1', '1', '3', '1', '77'),

(62, '6073', '7', '1', '1', '3', '1', '75'),

(63, '4663', '8', '1', '1', '3', '1', '74'),

(64, '6073', '8', '1', '1', '3', '1', '82'),

(65, '4663', '9', '1', '1', '3', '1', '56'),

(66, '6073', '9', '1', '1', '3', '1', '95'),

(67, '4663', '10', '1', '1', '3', '1', '87'),

(68, '6073', '10', '1', '1', '3', '1', '79'),

(69, '4663', '11', '1', '1', '3', '1', '70'),

(70, '6073', '11', '1', '1', '3', '1', '71'),

(71, '4663', '12', '1', '1', '3', '1', '82'),

(72, '6073', '12', '1', '1', '3', '1', '90');

COMMIT;

Table name: dummy (This table stores all students scores depending of RegNo, year(session), semester, level and student arm(i.e A,B,C,D...) respectively)

id regNo subjectid armsLevelId armsId semesterid yearid firstCA seconCA exam total grade comment
1 4663 1 1 1 1 1 9 8 55 72 B2 V.Good
2 6073 1 1 1 1 1 10 8 55 73 B2 V.Good
3 4663 2 1 1 1 1 6 8 33 47 D7 Pass
4 6073 2 1 1 1 1 11 6 44 61 C4 Credit

Table name: subject_position (This table stores all students total scores termly depending of RegNo, year(session), semester, level and student arm(i.e A,B,C,D...) respectively)

id regNo subjectid armsLevelId armsId semesterid yearid total
1 4663 1 1 1 1 1 72
2 6073 1 1 1 1 1 73
3 4663 2 1 1 1 1 47
4 6073 2 1 1 1 1 61

.. .... . . . . . ..

71 4663 12 1 1 3 1 82

72 6073 12 1 1 3 1 90

Expected Output: for subject_position irrespective of the subject list

Note: Number of student here is two. we can have more than two(2) students

id regNo subjectid armsLevelId armsId semesterid yearid total SubjectPos
1 4663 1 1 1 1 1 72 2
2 6073 1 1 1 1 1 73 1
3 4663 2 1 1 1 1 47 2
4 6073 2 1 1 1 1 61 1

.. .... . . . . . .. ..

71 4663 12 1 1 3 1 82 2

72 6073 12 1 1 3 1 90 1

My query

/*Where subject id $sub is coming from*/

$sql_subject = "select * from dummy where regid='$_GET[name]' and armsLevelId='$_SESSION[level_id]' and armsId='$_SESSION[arms]' and yearid='$_SESSION[session]' group by subjectid";

$result = mysqli_query($con,$sql_subject);

$datas_subject = array();

while ($row_query=mysqli_fetch_array($result)){

$RegNo=$row_query["regid"];

$subject_id=$row_query["subjectid"];

$level_id=$row_query["armsLevelId"];

$arms=$row_query["armsId"];

$semester=$row_query["semesterid"];

$session=$row_query["yearid"];

$fca=$row_query["firstCA"];

$sca=$row_query["secondCA"];

$exam=$row_query["exam"];

$total =$row_query["total"];

$grade =$row_query["grade"];

$comment =$row_query["comment"];

$query_sub=mysqli_query($con,"select * from subject where id='".$subject_id."'");

while ($row_sub=mysqli_fetch_array($query_sub)){

$r_sub = $row_sub["subject_name"];

}

$datas_subject[] = $subject_id;

}

for ($i=0; $i<count($datas_subject); $i++) {

if(!empty($datas_subject[$i])){

$sub = $datas_subject[$i];

echo "Subject ID: ".$sub ."\n"; // this printed all the list of subject ids

}else{

//echo "Error Occured";

}

/**/

$sql = "SELECT * FROM `subject_position` where regNo='$RegNo' and subjectid='$sub' and armsLevelId='$level_id' and armsId='$arms' and semesterid='$_SESSION[semester]' and yearid='$session' ORDER BY `total` DESC";

echo $sql . "\n"; // This printed SELECT * FROM `subject_position` where regNo='4663' and subjectid='12' and armsLevelId='1' and armsId='1' and semesterid='1' and yearid='1' ORDER BY `total` DESC

//without looping through the subject Ids

$result = mysqli_query($con,$sql);

if( !$result ){

echo 'SQL Query Failed';

}else{

$rank = 0;

$last_score = false;

$rows = 0;

while( $row = mysqli_fetch_array( $result ) ){

$rows++;

if( $last_score!= $row['total'] ){

$last_score = $row['total'];

$rank = $rows;

}

echo "rank ".$rank." is ".$row['regNo']." with point ".$row['total'] . "\n";

}

Barand · December 28, 2020

Try this

SELECT yearid
     , semesterid
     , subjectid
     , regno
     , rank
     , total
     , grade
     , comment
FROM (     
        SELECT yearid
             , semesterid
             , subjectid
             , @seq := CASE WHEN yss <> @prev THEN 1
                           ELSE @seq + 1
                           END as seq
             , @rank := CASE WHEN total = @prevtot THEN @rank
                             ELSE @seq
                             END as rank
             , @prevtot := total as total
             , @prev := yss as yss
             , regno
        FROM (
                SELECT yearid
                     , semesterid
                     , subjectid
                     , concat(yearid, semesterid, subjectid) as yss
                     , regno
                     , total
                FROM subject_position
                ORDER BY yearid, semesterid, subjectid, total DESC
                -- LIMIT 18446744073709551615                                         -- MariaDB requires this line uncommented *
             ) sorted
             JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init
    ) ranked
    JOIN
    grade ON total BETWEEN grade.lomark AND grade.himark;

(I invented my own grades)

mysql> select * from grade;
+----+-------+--------+--------+-------------+
| id | grade | lomark | himark | comment     |
+----+-------+--------+--------+-------------+
|  1 | A*    |     91 |    100 | Distinction |
|  2 | A     |     80 |     90 | Excellent   |
|  3 | B1    |     75 |     79 | V Good      |
|  4 | B2    |     70 |     74 | V Good      |
|  5 | C1    |     68 |     69 | Good        |
|  6 | C2    |     66 |     67 | Good        |
|  7 | C3    |     64 |     65 | Good        |
|  8 | C4    |     61 |     63 | Good        |
|  9 | D1    |     59 |     60 | Pass        |
| 10 | D2    |     57 |     58 | Pass        |
| 11 | D3    |     55 |     56 | Pass        |
| 12 | D4    |     53 |     54 | Pass        |
| 13 | D5    |     51 |     52 | Pass        |
| 14 | D6    |     49 |     50 | Pass        |
| 15 | D7    |     40 |     48 | Pass        |
| 16 | E     |     21 |     39 | Poor        |
| 17 | F     |      0 |     20 | Fail        |
+----+-------+--------+--------+-------------+

Query results...

+--------+------------+-----------+-------+------+-------+-------+-------------+
| yearid | semesterid | subjectid | regno | rank | total | grade | comment     |
+--------+------------+-----------+-------+------+-------+-------+-------------+
|      1 |          1 |         1 | 6073  | 1    |    73 | B2    | V Good      |
|      1 |          1 |         1 | 4663  | 2    |    72 | B2    | V Good      |
|      1 |          1 |         2 | 6073  | 1    |    61 | C4    | Good        |
|      1 |          1 |         2 | 4663  | 2    |    47 | D7    | Pass        |
|      1 |          1 |         3 | 4663  | 1    |    82 | A     | Excellent   |
|      1 |          1 |         3 | 6073  | 2    |    61 | C4    | Good        |
|      1 |          1 |         4 | 4663  | 1    |    99 | A*    | Distinction |
|      1 |          1 |         4 | 6073  | 2    |    95 | A*    | Distinction |
|      1 |          1 |         5 | 6073  | 1    |   100 | A*    | Distinction |
|      1 |          1 |         5 | 4663  | 2    |    70 | B2    | V Good      |
|      1 |          1 |         6 | 4663  | 1    |    69 | C1    | Good        |
|      1 |          1 |         6 | 6073  | 2    |    67 | C2    | Good        |
|      1 |          1 |         7 | 6073  | 1    |    80 | A     | Excellent   |
|      1 |          1 |         7 | 4663  | 2    |    77 | B1    | V Good      |
|      1 |          1 |         8 | 6073  | 2    |    77 | B1    | V Good      |
|      1 |          1 |         8 | 4663  | 2    |    58 | D2    | Pass        |
|      1 |          1 |         9 | 4663  | 1    |    96 | A*    | Distinction |
|      1 |          1 |         9 | 6073  | 1    |    96 | A*    | Distinction |
|      1 |          1 |        10 | 4663  | 1    |    78 | B1    | V Good      |
|      1 |          1 |        10 | 6073  | 2    |    77 | B1    | V Good      |
|      1 |          1 |        11 | 6073  | 1    |    88 | A     | Excellent   |
|      1 |          1 |        11 | 4663  | 2    |    48 | D7    | Pass        |
|      1 |          1 |        12 | 6073  | 1    |    94 | A*    | Distinction |
|      1 |          1 |        12 | 4663  | 2    |    69 | C1    | Good        |
|      1 |          2 |         1 | 6073  | 1    |    70 | B2    | V Good      |
|      1 |          2 |         1 | 4663  | 2    |    28 | E     | Poor        |
|      1 |          2 |         2 | 4663  | 1    |    68 | C1    | Good        |
|      1 |          2 |         2 | 6073  | 2    |    59 | D1    | Pass        |
|      1 |          2 |         3 | 6073  | 1    |    70 | B2    | V Good      |
|      1 |          2 |         3 | 4663  | 2    |    68 | C1    | Good        |
|      1 |          2 |         4 | 4663  | 1    |    81 | A     | Excellent   |
|      1 |          2 |         4 | 6073  | 2    |    72 | B2    | V Good      |
|      1 |          2 |         5 | 4663  | 1    |    84 | A     | Excellent   |
|      1 |          2 |         5 | 6073  | 2    |    72 | B2    | V Good      |
|      1 |          2 |         6 | 6073  | 2    |    72 | B2    | V Good      |
|      1 |          2 |         6 | 4663  | 2    |    58 | D2    | Pass        |
|      1 |          2 |         7 | 4663  | 1    |    71 | B2    | V Good      |
|      1 |          2 |         7 | 6073  | 2    |    70 | B2    | V Good      |
|      1 |          2 |         8 | 6073  | 1    |    55 | D3    | Pass        |
|      1 |          2 |         8 | 4663  | 2    |    48 | D7    | Pass        |
|      1 |          2 |         9 | 4663  | 1    |    66 | C2    | Good        |
|      1 |          2 |         9 | 6073  | 2    |    51 | D5    | Pass        |
|      1 |          2 |        10 | 6073  | 1    |    58 | D2    | Pass        |
|      1 |          2 |        10 | 4663  | 2    |    37 | E     | Poor        |
|      1 |          2 |        11 | 6073  | 1    |    59 | D1    | Pass        |
|      1 |          2 |        11 | 4663  | 2    |    57 | D2    | Pass        |
|      1 |          2 |        12 | 6073  | 1    |    69 | C1    | Good        |
|      1 |          2 |        12 | 4663  | 2    |    67 | C2    | Good        |
|      1 |          3 |         1 | 4663  | 1    |    94 | A*    | Distinction |
|      1 |          3 |         1 | 6073  | 2    |    82 | A     | Excellent   |
|      1 |          3 |         2 | 6073  | 1    |    76 | B1    | V Good      |
|      1 |          3 |         2 | 4663  | 2    |    69 | C1    | Good        |
|      1 |          3 |         3 | 6073  | 1    |    81 | A     | Excellent   |
|      1 |          3 |         3 | 4663  | 2    |    63 | C4    | Good        |
|      1 |          3 |         4 | 4663  | 1    |    81 | A     | Excellent   |
|      1 |          3 |         4 | 6073  | 2    |    77 | B1    | V Good      |
|      1 |          3 |         5 | 6073  | 1    |    83 | A     | Excellent   |
|      1 |          3 |         5 | 4663  | 2    |    72 | B2    | V Good      |
|      1 |          3 |         6 | 6073  | 1    |    83 | A     | Excellent   |
|      1 |          3 |         6 | 4663  | 2    |    78 | B1    | V Good      |
|      1 |          3 |         7 | 4663  | 1    |    77 | B1    | V Good      |
|      1 |          3 |         7 | 6073  | 2    |    75 | B1    | V Good      |
|      1 |          3 |         8 | 6073  | 1    |    82 | A     | Excellent   |
|      1 |          3 |         8 | 4663  | 2    |    74 | B2    | V Good      |
|      1 |          3 |         9 | 6073  | 1    |    95 | A*    | Distinction |
|      1 |          3 |         9 | 4663  | 2    |    56 | D3    | Pass        |
|      1 |          3 |        10 | 4663  | 1    |    87 | A     | Excellent   |
|      1 |          3 |        10 | 6073  | 2    |    79 | B1    | V Good      |
|      1 |          3 |        11 | 6073  | 1    |    71 | B2    | V Good      |
|      1 |          3 |        11 | 4663  | 2    |    70 | B2    | V Good      |
|      1 |          3 |        12 | 6073  | 1    |    90 | A     | Excellent   |
|      1 |          3 |        12 | 4663  | 2    |    82 | A     | Excellent   |
+--------+------------+-----------+-------+------+-------+-------+-------------+

[edit]

* The idiots at Maria maintain that as the subquery effectively creates a table, and tables are inherently unordered, then the ORDER BY clause should be ignored. The rest of the world think this is a resultset and there can be ordered. The LIMIT 2⁶⁴-1 forces it to use an ordered temporary table.

Edited December 28, 2020 by Barand
Add maria note

Ponel · December 28, 2020

bravo.. This worked well sir, I created the same grade as you. but where the problem lies now is i want to display a unique record for a particular user like i created something like this query

SELECT yearid
, semesterid
, subjectid
, regno
, rank
, total
, grade
, comment
FROM (
SELECT yearid
, semesterid
, subjectid
, @seq := CASE WHEN yss <> @prev THEN 1
ELSE @seq + 1
END as seq
, @rank := CASE WHEN total = @prevtot THEN @rank
ELSE @seq
END as rank
, @prevtot := total as total
, @prev := yss as yss
, regno
FROM (
SELECT yearid
, semesterid
, subjectid
, concat(yearid, semesterid, subjectid) as yss
, regno
, total
FROM subject_position
ORDER BY yearid, semesterid, subjectid, total DESC
-- LIMIT 18446744073709551615 -- MariaDB requires this line uncommented *
) sorted
JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init
) ranked
JOIN
grade ON total BETWEEN grade.lomark AND grade.himark where subject_position.regNo='4663' and subject_position.armsLevelId='1' and subject_position.armsId='1' and subject_position.semesterid='1' and subject_position.yearid='1'

I got this error:

Error

SQL query:

SELECT yearid , semesterid , subjectid , regno , rank , total , grade , comment FROM ( SELECT yearid , semesterid , subjectid , @seq := CASE WHEN yss <> @prev THEN 1 ELSE @seq + 1 END as seq , @rank := CASE WHEN total = @prevtot THEN @rank ELSE @seq END as rank , @prevtot := total as total , @prev := yss as yss , regno FROM ( SELECT yearid , semesterid , subjectid , concat(yearid, semesterid, subjectid) as yss , regno , total FROM subject_position ORDER BY yearid, semesterid, subjectid, total DESC ) sorted JO[...]

MySQL said:

#1054 - Unknown column 'subject_position.regNo' in 'where clause'

So i try removing the table name (Instead of subject_position.regNo i used regNo and so on like that). i got this error message too

Error

SQL query:

SELECT yearid , semesterid , subjectid , regno , rank , total , grade , comment FROM ( SELECT yearid , semesterid , subjectid , @seq := CASE WHEN yss <> @prev THEN 1 ELSE @seq + 1 END as seq , @rank := CASE WHEN total = @prevtot THEN @rank ELSE @seq END as rank , @prevtot := total as total , @prev := yss as yss , regno FROM ( SELECT yearid , semesterid , subjectid , concat(yearid, semesterid, subjectid) as yss , regno , total FROM subject_position ORDER BY yearid, semesterid, subjectid, total DESC ) sorted JO[...]

MySQL said:

#1054 - Unknown column 'armsLevelId' in 'where clause'

Please sir what's the way forward? Thanks in advance

Barand · December 28, 2020

Like this? You need to include those "unknown columns" in the intermediate subqueries

SELECT yearid
     , semesterid
     , regno
     , subjectid
     , rank
     , total
     , grade
     , comment
FROM (     
        SELECT yearid
             , semesterid
             , subjectid
             , @seq := CASE WHEN yss <> @prev THEN 1
                           ELSE @seq + 1
                           END as seq
             , @rank := CASE WHEN total = @prevtot THEN @rank
                             ELSE @seq
                             END as rank
             , @prevtot := total as total
             , @prev := yss as yss
             , regno
             , armsLevelId                                            -- added
             , armsid                                                 -- added
        FROM (
                SELECT yearid
                     , semesterid
                     , subjectid
                     , concat(yearid, semesterid, subjectid) as yss
                     , regno
                     , total
                     , armsLevelId                                   -- added
                     , armsid                                        -- added
                FROM subject_position
                ORDER BY yearid, semesterid, subjectid, total DESC
             ) sorted
             JOIN (SELECT @prev := '', @seq := 0, @rank := 0, @prevtot := 0) as init
    ) ranked
    JOIN
    grade ON total BETWEEN grade.lomark AND grade.himark
WHERE regNo='4663'                                                   -- A  
    and armsLevelId='1'                                              --  D
    and armsId='1'                                                   --   D 
    and semesterid='1'                                               --    E 
    and yearid='1'                                                   --     D   
ORDER BY subjectid                                                   --      .

Giving

+--------+------------+-------+-----------+------+-------+-------+-------------+
| yearid | semesterid | regno | subjectid | rank | total | grade | comment     |
+--------+------------+-------+-----------+------+-------+-------+-------------+
|      1 |          1 | 4663  |         1 | 2    |    72 | B2    | V Good      |
|      1 |          1 | 4663  |         2 | 2    |    47 | D7    | Pass        |
|      1 |          1 | 4663  |         3 | 1    |    82 | A     | Excellent   |
|      1 |          1 | 4663  |         4 | 1    |    99 | A*    | Distinction |
|      1 |          1 | 4663  |         5 | 2    |    70 | B2    | V Good      |
|      1 |          1 | 4663  |         6 | 1    |    69 | C1    | Good        |
|      1 |          1 | 4663  |         7 | 2    |    77 | B1    | V Good      |
|      1 |          1 | 4663  |         8 | 2    |    58 | D2    | Pass        |
|      1 |          1 | 4663  |         9 | 1    |    96 | A*    | Distinction |
|      1 |          1 | 4663  |        10 | 1    |    78 | B1    | V Good      |
|      1 |          1 | 4663  |        11 | 2    |    48 | D7    | Pass        |
|      1 |          1 | 4663  |        12 | 2    |    69 | C1    | Good        |
+--------+------------+-------+-----------+------+-------+-------+-------------+

Ponel · December 28, 2020

Bravo it worked quite well but some issue arises

This record is for user with regNo 6073

Note: For user with 6073 subject IDs 1,2,3,5,6,7,8,10,11,12 was displayed instead of 1,2,3,4,5,6,7,8,9,10,11,12 so subject IDs 4,9 was skipped

1111.PNG.4c9bd4eb064192e982f4bcdd2866fb39.PNG

This record is for user with regNo 4663

Note: For user with 4663 subject IDs 1,2,3,5,6,7,8,10,11 was displayed instead of 1,2,3,4,5,6,7,8,9,10,11,12 so subject IDs 4,9,12 was skipped

11111.PNG.3defaf96ec94e082f8fcec238d50a002.PNG

Note: Record user 6073 should be 12 records instead 9 was displayed while Record user 4663 should be 12 records instead 10 was displayed. The full records for the both user is display below

full_record.PNG.ca5d80d2b74b1fe099919df9a3c8c68f.PNG

full_record2.PNG.a897a41ecb08d8c202d2e2db45034e55.PNG

Barand · December 28, 2020

No such issues my end. My results for 4663 were posted earlier and for 6073 I get

+--------+------------+-------+-----------+------+-------+-------+-------------+
| yearid | semesterid | regno | subjectid | rank | total | grade | comment     |
+--------+------------+-------+-----------+------+-------+-------+-------------+
|      1 |          1 | 6073  |         1 | 1    |    73 | B2    | V Good      |
|      1 |          1 | 6073  |         2 | 1    |    61 | C4    | Good        |
|      1 |          1 | 6073  |         3 | 2    |    61 | C4    | Good        |
|      1 |          1 | 6073  |         4 | 2    |    95 | A*    | Distinction |
|      1 |          1 | 6073  |         5 | 1    |   100 | A*    | Distinction |
|      1 |          1 | 6073  |         6 | 2    |    67 | C2    | Good        |
|      1 |          1 | 6073  |         7 | 1    |    80 | A     | Excellent   |
|      1 |          1 | 6073  |         8 | 2    |    77 | B1    | V Good      |
|      1 |          1 | 6073  |         9 | 1    |    96 | A*    | Distinction |
|      1 |          1 | 6073  |        10 | 2    |    77 | B1    | V Good      |
|      1 |          1 | 6073  |        11 | 1    |    88 | A     | Excellent   |
|      1 |          1 | 6073  |        12 | 1    |    94 | A*    | Distinction |
+--------+------------+-------+-----------+------+-------+-------+-------------+

As you can see, 12 subjects for both using your data posted at the start.

Pulling both together, ordered by subject/rank gives

+--------+------------+-------+-----------+------+-------+-------+-------------+
| yearid | semesterid | regno | subjectid | rank | total | grade | comment     |
+--------+------------+-------+-----------+------+-------+-------+-------------+
|      1 |          1 | 6073  |         1 | 1    |    73 | B2    | V Good      |
|      1 |          1 | 4663  |         1 | 2    |    72 | B2    | V Good      |
|      1 |          1 | 6073  |         2 | 1    |    61 | C4    | Good        |
|      1 |          1 | 4663  |         2 | 2    |    47 | D7    | Pass        |
|      1 |          1 | 4663  |         3 | 1    |    82 | A     | Excellent   |
|      1 |          1 | 6073  |         3 | 2    |    61 | C4    | Good        |
|      1 |          1 | 4663  |         4 | 1    |    99 | A*    | Distinction |
|      1 |          1 | 6073  |         4 | 2    |    95 | A*    | Distinction |
|      1 |          1 | 6073  |         5 | 1    |   100 | A*    | Distinction |
|      1 |          1 | 4663  |         5 | 2    |    70 | B2    | V Good      |
|      1 |          1 | 4663  |         6 | 1    |    69 | C1    | Good        |
|      1 |          1 | 6073  |         6 | 2    |    67 | C2    | Good        |
|      1 |          1 | 6073  |         7 | 1    |    80 | A     | Excellent   |
|      1 |          1 | 4663  |         7 | 2    |    77 | B1    | V Good      |
|      1 |          1 | 6073  |         8 | 2    |    77 | B1    | V Good      |
|      1 |          1 | 4663  |         8 | 2    |    58 | D2    | Pass        |
|      1 |          1 | 4663  |         9 | 1    |    96 | A*    | Distinction |
|      1 |          1 | 6073  |         9 | 1    |    96 | A*    | Distinction |
|      1 |          1 | 4663  |        10 | 1    |    78 | B1    | V Good      |
|      1 |          1 | 6073  |        10 | 2    |    77 | B1    | V Good      |
|      1 |          1 | 6073  |        11 | 1    |    88 | A     | Excellent   |
|      1 |          1 | 4663  |        11 | 2    |    48 | D7    | Pass        |
|      1 |          1 | 6073  |        12 | 1    |    94 | A*    | Distinction |
|      1 |          1 | 4663  |        12 | 2    |    69 | C1    | Good        |
+--------+------------+-------+-----------+------+-------+-------+-------------+

Looks like you broke it or changed your data. Not much I can do about either.

Ponel · December 29, 2020

Thank you sir for your time I appreciate that. If i may ask, which version of mysql are you using sir?

Ponel · December 29, 2020

Thank you sir for your time I appreciate that. If i may ask, which version of mysql are you using sir?

Barand · December 29, 2020

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.21-log |
+------------+

Ponel · December 29, 2020

I'm using the same version as you. Why am i getting different output

Barand · December 29, 2020

The only change that I made at my end was to define the columns as int, not the varchar(50) that you used)

I have just changed mine to varchar to match yours but the only difference was the subject sequence (1, 10, 11, 12, 2,... as expected) and I still got all 12 subjects.

So still head-scratching.

Barand · December 29, 2020

It just occured to me that our grade tables are not the same.

The missing subjects are 4, 9 and 12 so I extracted those records...

mysql> SELECT * FROM subject_position
    -> WHERE subjectid in (4,9,12)
    ->     and semesterid='1'
    ->     and yearid='1'
    ->     and armsLevelId='1'
    ->     and armsId='1'
    -> ORDER BY regno, subjectid;
+----+-------+-----------+-------------+--------+------------+--------+-------+
| id | regNo | subjectid | armsLevelId | armsId | semesterid | yearid | total |
+----+-------+-----------+-------------+--------+------------+--------+-------+
| 23 | 4663  | 12        | 1           | 1      | 1          | 1      | 69    |
|  7 | 4663  | 4         | 1           | 1      | 1          | 1      | 99    |
| 17 | 4663  | 9         | 1           | 1      | 1          | 1      | 96    |
| 24 | 6073  | 12        | 1           | 1      | 1          | 1      | 94    |
|  8 | 6073  | 4         | 1           | 1      | 1          | 1      | 95    |
| 18 | 6073  | 9         | 1           | 1      | 1          | 1      | 96    |
+----+-------+-----------+-------------+--------+------------+--------+-------+

Could it be that those total values are missing from the grade ranges in your grade table and so no matches are found?

Try LEFT JOIN grade and see if all records now appear.

[edit]

Your results - Getting 100% and still failing must have been a little disheartening!

image.png.d4a8d1c8caa78586930ac31aef92ca08.png

Edited December 29, 2020 by Barand

Barand · December 29, 2020

PS. This is my grade table

DROP TABLE IF EXISTS `grade`;

CREATE TABLE `grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grade` char(2) NOT NULL DEFAULT '',
  `lomark` int(11) DEFAULT NULL,
  `himark` int(11) DEFAULT NULL,
  `comment` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `grade`
--

INSERT INTO `grade` VALUES 
(1,'A*',91,100,'Distinction'),(2,'A',80,90,'Excellent'),(3,'B1',75,79,'V Good'),
(4,'B2',70,74,'V Good'),(5,'C1',68,69,'Good'),(6,'C2',66,67,'Good'),
(7,'C3',64,65,'Good'),(8,'C4',61,63,'Good'),(9,'D1',59,60,'Pass'),
(10,'D2',57,58,'Pass'),(11,'D3',55,56,'Pass'),(12,'D4',53,54,'Pass'),
(13,'D5',51,52,'Pass'),(14,'D6',49,50,'Pass'),(15,'D7',40,48,'Pass'),
(16,'E ',21,39,'Poor'),(17,'F ',0,20,'Fail');

Ponel · December 29, 2020

You are too much Mr Barand. What i did was i just changed all the data type on my subject_position ID to int and then refresh my page. Then it works. Thank you...

This is the output sir

for user 4663

Capture333.PNG.ec66b8dc46939820a2a3c2a856b14b65.PNG

for user 6073

Capture2222.PNG.aac0a65d242d90621e0827581fd77fdc.PNG

Thank You sir God bless you Abundantly

Ponel · December 29, 2020

One More thing sir, I tried putting st,nd,rd,th at the back of this position

i.e. 1 ---- 1st

2 --- 2nd

3 --- 3rd

4 --- 4th

5 --- 5th

..............

13 ---- 13th

23 ---- 23rd

so i wrote this conditional statement inside my code

if (substr($row['total'], -1) == 1 && $row['total'] != 11){

echo $row['total'].'st'. "- ";

} elseif(substr($row['total'], -1) == 2 && $row['total'] != 12){

echo $row['total'].'nd'. "- ";

} else {

echo $row['total'].'th'. "- ";

}

this isn't working for 3rd, 23rd, 33rd and so on

any clue on this sir?

gw1500se · December 29, 2020

Where do you check for 3 and append 'rd'? I can only see 'st', 'nd' and 'th'.

Ponel · December 29, 2020

Ok sir I understand..

I wrote this

if (substr($position, -1) == 1 && $position != 11){

$position = $position.'st';

} elseif(substr($position, -1) == 2 && $position != 12){

$position = $position.'nd';

} elseif(substr($position, -1) == 3 && $position != 13){

$position = $position.'rd';

} else {

$position = $position.'th';;

}

The output

Captureeeee.PNG.8cf344aed0bc3d6b8cc4fe9fb3f52793.PNG

I only have two students data for now, i hope it works for the rest

Thank you very much sir. I will work on the overall position from this impact knowledge of yours. God bless you a bunch. And i would like to be your student sir

gw1500se · December 29, 2020

One additional note. You need to check for 11, 12 and 13 since they are not 11st, 12nd or 13rd. Teens are all 'th'.

Barand · December 29, 2020

Here's my function for ordinal suffices

  function ordSuffix($n) 
  
  {
      $str = "$n";
      $t = $n > 9 ? substr($str,-2,1) : 0;
      $u = substr($str,-1);
      if ($t==1) 
        return $str . 'th';
      else 
          switch ($u) 
          {
              case 1: return $str . 'st';
              case 2: return $str . 'nd';
              case 3: return $str . 'rd';
              default: return $str . 'th';
          }
  }
  
  echo ordSuffix(1).'<br>';      // 1st
  echo ordSuffix(11).'<br>';     // 11th
  echo ordSuffix(101).'<br>';    // 101st
  echo ordSuffix(2).'<br>';      // 2nd
  echo ordSuffix(3).'<br>';      // 3rd
  echo ordSuffix(8).'<br>';      // 8th

Barand · December 29, 2020

Looking at you latest output examples I would suggest you lose the "subject_position" table. All you need to store are the highlighted result values below. All the rest (totals, positions) can be derived by querying the data

image.png.ab018a2672ff3ec5d9b84fd0c735cdc4.png

TABLE: result
+-------------+--------------------------+------+-----+---------+----------------+
| Field       | Type                     | Null | Key | Default | Extra          |
+-------------+--------------------------+------+-----+---------+----------------+
| result_id   | int(11)                  | NO   | PRI | NULL    | auto_increment |
| regno       | int(11)                  | YES  | MUL | NULL    |                |
| year        | varchar(45)              | YES  |     | NULL    |                |
| semester    | varchar(45)              | YES  |     | NULL    |                |
| subjectid   | int(11)                  | YES  | MUL | NULL    |                |
| result_type | enum('CA1','CA2','Exam') | YES  |     | NULL    |                |
| pcent       | int(11)                  | YES  |     | NULL    |                |
+-------------+--------------------------+------+-----+---------+----------------+

Note: I have omitted armsid and armsLevel as I haven't clue what they are and where they belong in the schema.

Ponel · December 29, 2020

Thank you so much Mr Barand.

Ponel · December 30, 2020

How can i know the actual number of student in a class for a particular armsLevelId, armsId, semesterid, and yearid from the below

full_record.PNG.643e128810fc8a3bdd59e6fc7100652d.PNG

full_record2.PNG.a8427288b7c1d33ec8ad20390bdd0c0a.PNG

my Query

My query

SELECT COUNT(id) FROM `subject_position` WHERE armsLevelId='1' and armsId='1' and semesterid='1' and yearid='1' group by regNo

tt.PNG.a229903d9bfb061f3e2ba1037fa94139.PNG

Expected Output should be 2. Any clue on this?

Barand · December 30, 2020

Use

COUNT(DISTINCT regno) as students

What as arms and armsLevel?

Sign In

Student subject Positioning based on score using php and mysql database

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Barand

Barand

Barand

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information