Ponel

Thanks I grab. arms(This is the level arms a student belong i.e. A,B,C,D....) while armsLevel (This is the Level a student belong to i.e JS1, JS2, JS3, ...). So a student can belong to JS1A, JS1B, JS1C, JS2A, JS2B, JS2C .......

How can i know the actual number of student in a class for a particular armsLevelId, armsId, semesterid, and yearid from the below 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 Expected Output should be 2. Any clue on this?

Thank you so much Mr Barand.

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

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?

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 for user 6073 Thank You sir God bless you Abundantly

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

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

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

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

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

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

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.