Jump to content

Student subject Positioning based on score using php and mysql database


Ponel

Recommended Posts

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.

Link to comment
Share on other sites

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";

     }

}

 

 

Link to comment
Share on other sites

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 264-1 forces it to use an ordered temporary table.

Edited by Barand
Add maria note
  • Like 1
Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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        |
+--------+------------+-------+-----------+------+-------+-------+-------------+

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Barand
  • Like 1
Link to comment
Share on other sites

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');

 

Link to comment
Share on other sites

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

 

 

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

 

  • Like 1
Link to comment
Share on other sites

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.

  • Like 1
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.