jazal

Thanks for your support, I will try and work to get the database right.

The initial image i posted was to ask if i can get rank for subject in each term and overall rank in each term as it was display in the image like position in each subject and class position. Because i want each student should know its rank in each subject so they can know the subject they outsmart their colleague and also know the best student in each class overall in every term.

every class have project and assignment every term and they are consistent, The highest score for ca1, ca2, project and assignment in each subject is 10 . That table looks like this below +-----------+---------------+-----+-----+------------+---------+------+-------+ | studentid | subject | CA1 | CA2 | Assignment | Project | Exam | total | +-----------+---------------+-----+-----+------------+---------+------+-------+ | 2393 | English Lang. | 10 | 10 | 10 | 10 | 60 | 100 | | 2393 | Mathematics | 8 | 9 | 6 | 9 | 34 | 66 | | 2393 | BST | 9 | 10 | 5 | 10 | 42 | 76 | | 936 | English Lang. | 8 | 10 | 9 | 7 | 49 | 83 | | 936 | Mathematics | 10 | 10 | 6 | 6 | 44 | 76 | | 936 | BST | 5 | 5 | 4 | 9 | 17 | 40 | | 3391 | English Lang. | 8 | 10 | 9 | 3 | 50 | 69 | | 3391 | Mathematics | 8 | 10 | 1 | 9 | 49 | 77 | | 3391 | BST | 8 | 10 | 3 | 1 | 54 | 76 | +-----------+---------------+-----+-----+------------+---------+------+-------+

i used his data structure the come with script that you said we have rebuild it so it can work properly. Overall score, Grade, Teacher Remark, total marks, percentage and result was done on the php script. i tired to derived rank for each subject and overall but i cant get it right. Thats why i want rebuild it from scratch. Any Help on that. Is it possible to have everything on database that what i just need to do is just query it out Thanks for your assistance

like this below

that was a sample of what i want to do. What i am tryin to do is that ca1, ca2, assignment and project are assignment with 10 marks each and exam have 60 marks and everything in total will be 100 in each subject.

`OverallMark` int(4) DEFAULT NULL, i forgot to add overall mark, this is have all the score in a specific term and also can be used to derived rank for class position

how can i derive the rank for each subject and overall rank using average thanks for your support

DROP TABLE IF EXISTS `tblresult`; CREATE TABLE IF NOT EXISTS `tblresult` ( `id` int(11) NOT NULL AUTO_INCREMENT, `StudentId` int(5) DEFAULT NULL, `ClassId` int(5) DEFAULT NULL, `SubjectId` int(5) DEFAULT NULL, `test1` int(2) DEFAULT NULL, `test2` int(2) DEFAULT NULL, `assignment` int(2) DEFAULT NULL, `project` int(2) DEFAULT NULL, `exam` int(2) DEFAULT NULL, `totalmark` int(3) DEFAULT NULL, `subjectrank` int(2) DEFAULT NULL, `rank` int(2) DEFAULT NULL, `term` VARCHAR (10) DEFAULT NULL, `percent` floatval(3) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; -- -- Dumping data for table `tblresult` -- INSERT INTO `tblresult` (`id`, `Status`, `StudentId`, `ClassId`, `SubjectId`, `exam`, `test`) VALUES (1, 1, 1, 2, 10, 10, 5, 9, 30, `first`), (2, 1, 1, 2, 2, 4, 8, 7, 30, `first`), (3, 1, 1, 2, 6, 3, 9, 5, 45, `first`), (4, 1, 1, 2, 5, 5, 5, 1, 30, `first`), (5, 1, 1, 2, 4, 10, 5, 3, 50, `first`); (1, 1, 1, 3, 10, 10, 5, 7, 30, `first`), (2, 1, 1, 3, 2, 10, 8, 9, 30, `first`), (3, 1, 1, 3, 6, 7, 9, 8, 25, `first`), (4, 1, 1, 3, 5, 8, 5, 10, 35, `first`), (5, 1, 1, 3, 7, 10, 5, 5, 55, `first`); i want to add test1, test2, assignment and project to give me total mark, then total mark of each subject id determine subject rank and percentage determine the position in class for each term

DROP TABLE IF EXISTS `tblresult`; CREATE TABLE IF NOT EXISTS `tblresult` ( `id` int(11) NOT NULL AUTO_INCREMENT, `StudentId` int(5) DEFAULT NULL, `ClassId` int(5) DEFAULT NULL, `SubjectId` int(5) DEFAULT NULL, `exam` int(2) DEFAULT NULL, `test` int(2) DEFAULT NULL, `finalmark` int(3) DEFAULT NULL, `rank` int(2) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; -- -- Dumping data for table `tblresult` -- INSERT INTO `tblresult` (`id`, `Status`, `StudentId`, `ClassId`, `SubjectId`, `exam`, `test`) VALUES (1, 1, 1, 2, 50, 30), (2, 1, 1, 2, 20, 30), (3, 1, 1, 2, 10, 30), (4, 1, 1, 2, 15, 35), (5, 1, 1, 3, 40, 15); i want to add test and exam score for final mark result and also final mark decide rank

okay, thanks. will kindly do that

can we change all to 150? cos it work good on my live server

-- phpMyAdmin SQL Dump -- version 5.0.2 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1:3306 -- Generation Time: Jan 05, 2021 at 09:16 AM -- Server version: 5.7.31-log -- PHP Version: 7.4.9 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; START TRANSACTION; SET time_zone = "+00:00"; /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8mb4 */; -- -- Database: `api_app` -- -- -------------------------------------------------------- -- -- Table structure for table `admin` -- DROP TABLE IF EXISTS `admin`; CREATE TABLE IF NOT EXISTS `admin` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fullname` varchar(1000) DEFAULT NULL, `role` varchar(1000) DEFAULT NULL, `email` varchar(255) NOT NULL, `username` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `user_token` varchar(255) NOT NULL, `date` datetime NOT NULL, `ip` varchar(255) NOT NULL, `mobile` varchar(255) DEFAULT NULL, `passport` varchar(1000) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- -- Dumping data for table `admin` -- INSERT INTO `admin` (`id`, `fullname`, `role`, `email`, `username`, `password`, `user_token`, `date`, `ip`, `mobile`, `passport`) VALUES (1, 'admin', 'admin', '[email protected]', 'admin', '21232f297a57a5a743894a0e4a801fc3', '', '2021-01-13 15:08:14', '', NULL, NULL); -- -------------------------------------------------------- -- -- Table structure for table `settings` -- DROP TABLE IF EXISTS `settings`; CREATE TABLE IF NOT EXISTS `settings` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(10000) DEFAULT NULL, `description` text, `logo` varchar(1000) DEFAULT NULL, `siteback` varchar(1000) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; -- -- Dumping data for table `settings` -- INSERT INTO `settings` (`id`, `title`, `description`, `logo`, `siteback`) VALUES (1, 'Resut', 'Result Management', 'p.png', 'maxfemcollege.jpg'); -- -------------------------------------------------------- -- -- Table structure for table `students_data` -- DROP TABLE IF EXISTS `students_data`; CREATE TABLE IF NOT EXISTS `students_data` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` varchar(10000) NOT NULL, `uiid` mediumtext NOT NULL, `class` varchar(255) DEFAULT NULL, `status` varchar(10000) NOT NULL, `full_name` varchar(10000) NOT NULL, `email` varchar(1000) NOT NULL, `mobile` varchar(100) NOT NULL, `dob` varchar(10000) NOT NULL, `age` varchar(1000) NOT NULL, `sex` varchar(1000) NOT NULL, `address` varchar(10000) NOT NULL, `nationality` varchar(150) NOT NULL, `state` varchar(50) NOT NULL, `lga` varchar(50) NOT NULL, `home_town` varchar(100) NOT NULL, `religion` varchar(100) NOT NULL, `prev_school` varchar(100) NOT NULL, `prev_school_date_attended` varchar(100) NOT NULL, `prev_school_class_read` varchar(100) NOT NULL, `prev_school_reasonforleaving` varchar(100) NOT NULL, `father_full_name` varchar(100) NOT NULL, `father_address` varchar(100) NOT NULL, `father_occupation` varchar(100) NOT NULL, `father_office_address` varchar(100) NOT NULL, `father_email` varchar(100) NOT NULL, `father_mobile` varchar(100) NOT NULL, `mother_full_name` varchar(100) NOT NULL, `mother_address` varchar(100) NOT NULL, `mother_occupation` varchar(100) NOT NULL, `mother_office_address` varchar(100) NOT NULL, `mother_email` varchar(100) NOT NULL, `mother_mobile` varchar(100) NOT NULL, `passport` varchar(10000) NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `smsto` text, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `tblclasses` -- DROP TABLE IF EXISTS `tblclasses`; CREATE TABLE IF NOT EXISTS `tblclasses` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ClassName` varchar(80) DEFAULT NULL, `ClassNameNumeric` int(4) NOT NULL, `Section` varchar(10000) NOT NULL, `CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `price` int(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `person_name_and_idx` (`ClassName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `tbldepartments` -- DROP TABLE IF EXISTS `tbldepartments`; CREATE TABLE IF NOT EXISTS `tbldepartments` ( `id` int(11) NOT NULL AUTO_INCREMENT, `DepartmentName` varchar(10000) DEFAULT NULL, `CreationDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `Updationdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `person_name_and_idx` (`DepartmentName`(3072)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table `tblresult` -- DROP TABLE IF EXISTS `tblresult`; CREATE TABLE IF NOT EXISTS `tblresult` ( `id` int(11) NOT NULL AUTO_INCREMENT, `StudentId` int(11) DEFAULT NULL, `ClassId` int(11) DEFAULT NULL, `Status` varchar(1000) DEFAULT NULL, `SubjectId` int(11) DEFAULT NULL, `revision_` int(250) DEFAULT '0', `adminstatus` int(250) DEFAULT '0', `marks` int(11) DEFAULT NULL, `tmarks` varchar(1000) DEFAULT NULL, `PostingDate` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `UpdationDate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `term` varchar(10000) DEFAULT NULL, `year` varchar(10000) DEFAULT NULL, `Department` varchar(10000) DEFAULT NULL, `daysschool` varchar(1000) DEFAULT NULL, `dayspresent` varchar(1000) DEFAULT NULL, `daysabsence` varchar(1000) DEFAULT NULL, `termbegin` varchar(1000) DEFAULT NULL, `termends` varchar(1000) DEFAULT NULL, `termnext` varchar(1000) DEFAULT NULL, PRIMARY KEY (`id`), KEY `result_StudentId_and_ClassId` (`StudentId`,`ClassId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `tblstudents` -- DROP TABLE IF EXISTS `tblstudents`; CREATE TABLE IF NOT EXISTS `tblstudents` ( `StudentId` int(11) NOT NULL AUTO_INCREMENT, `StudentName` varchar(100) NOT NULL, `classtype` varchar(1000) DEFAULT NULL, `session` varchar(1000) DEFAULT NULL, `logo` varchar(10000) DEFAULT NULL, `RollId` varchar(100) NOT NULL, `StudentEmail` varchar(100) NOT NULL, `mobile` varchar(1000) DEFAULT NULL, `Gender` varchar(10) NOT NULL, `DOB` varchar(100) NOT NULL, `ClassId` varchar(1000) DEFAULT NULL, `user_token` varchar(1000) DEFAULT NULL, `RegDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `UpdationDate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP, `Status` int(1) NOT NULL, `Departments` mediumtext, `password` varchar(1000) DEFAULT NULL, PRIMARY KEY (`StudentId`), KEY `person_StudentId_and_StudentName` (`StudentName`,`StudentId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `tblsubjectcombination` -- DROP TABLE IF EXISTS `tblsubjectcombination`; CREATE TABLE IF NOT EXISTS `tblsubjectcombination` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ClassId` int(11) NOT NULL, `SubjectId` int(11) NOT NULL, `status` int(1) DEFAULT NULL, `CreationDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `Updationdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `person_name_and_idx` (`ClassId`,`SubjectId`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `tblsubjects` -- DROP TABLE IF EXISTS `tblsubjects`; CREATE TABLE IF NOT EXISTS `tblsubjects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `SubjectName` varchar(100) NOT NULL, `SubjectCode` varchar(100) NOT NULL, `Creationdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `UpdationDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `person_name_and_idx` (`SubjectName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Table structure for table `teacher` -- DROP TABLE IF EXISTS `teacher`; CREATE TABLE IF NOT EXISTS `teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fullName` varchar(255) NOT NULL, `pic` varchar(10000) DEFAULT NULL, `email` varchar(255) NOT NULL, `classid` int(100) DEFAULT NULL, `mobileNo` varchar(255) NOT NULL, `password` varchar(255) NOT NULL, `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Thanks @Barand

i already said it that i was trying to work on someone work to make it better