Barand Posted December 28, 2022 Share Posted December 28, 2022 They should be the size required to store the content. Do you know anyone whose name or phone number is 150 chars long? Also many of those should not be varchar at all. For example, DOB should be DATE type. The classid in the class table is INT, do why is the foreign key classid in the students table varchar(1000)? They should match (ie both should be int) Does every student have their own personal logo? The whole database needs normalizing and rebuilding correctly to make it fit for purpose. When you've done that, come back to the re-coding of the pages. Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1603987 Share on other sites More sharing options...
jazal Posted December 28, 2022 Author Share Posted December 28, 2022 okay, thanks. will kindly do that Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1603995 Share on other sites More sharing options...
jazal Posted December 29, 2022 Author Share Posted December 29, 2022 (edited) 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 Edited December 29, 2022 by jazal correction Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1603996 Share on other sites More sharing options...
Barand Posted December 29, 2022 Share Posted December 29, 2022 That is certainly an improvement. What are the "exam" and "test" columns for? On your results output report that you showed us there were CA1, CA2 and Exam marks so I would expect a row in the result table would contain the marks achieved for one of these, so intead of those two columns, one would suffice. For example... `result_type` enum('CA1','CA2','Exam') DEFAULT NULL COMMENT '1 = CA1, 2 = CA2, 3 = Exam', I also think you need the "term" in there too, so you know when the marks were achieved. "Rank" is derived nd should not be stored. Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604003 Share on other sites More sharing options...
jazal Posted December 29, 2022 Author Share Posted December 29, 2022 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 Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604004 Share on other sites More sharing options...
jazal Posted December 29, 2022 Author Share Posted December 29, 2022 how can i derive the rank for each subject and overall rank using average thanks for your support Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604005 Share on other sites More sharing options...
jazal Posted December 29, 2022 Author Share Posted December 29, 2022 36 minutes ago, jazal said: 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 `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 Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604006 Share on other sites More sharing options...
Barand Posted December 29, 2022 Share Posted December 29, 2022 You are changing the table back to a spreadsheet again. Each row should contain the marks obtained for one event (CA1, CA2, Assignment, Project or Exam) only and a column to indicate which as I suggested earlier (now extended) `result_type` enum('CA1', 'CA2', 'Assignment', 'Project', 'Exam') DEFAULT NULL COMMENT '1 = CA1, 2 = CA2, 3 = Assignment, 4 = Project, 5 = Exam', Totals and ranks are derived and not stored. You get those by querying the result table. You should have something like this CREATE TABLE IF NOT EXISTS `result` ( `id` int(11) NOT NULL AUTO_INCREMENT, `studentid` int(11) DEFAULT NULL, `classid` int(11) DEFAULT NULL, `subjectid` int(11) DEFAULT NULL, `result_type` enum('CA1', 'CA2', 'Assignment', 'Project', 'Exam') DEFAULT NULL COMMENT '1 = CA1, 2 = CA2, 3 = Assignment, 4 = Project, 5 = Exam', `termid` INT(11) DEFAULT NULL, `percent` DECIMAL(4,1) DEFAULT NULL, PRIMARY KEY (`id`) KEY idx_result_student (studentid), KEY idx_result_class (classid), KEY idx_result_subject (subjectid), KEY idx_result_term (termid), ) ENGINE=InnoDB; I would also create a "term" table so you can have the term dates CREATE TABLE `term` ( id INT not null auto_increment, year INT, termno TINYINT, termstart DATE, termend DATE, PRIMARY KEY (id), ) ENGINE=InnoDB; This still begs the question of how to store the percent score attained in each test. On your sample report you have CA1 and CA2 each contributing 20% of the final total and the Exam contibuting 60%, therefore the weightings are 0.2, 0.2, 0.6 respectively. If a student score 80% in CA1, do you store this as 80 and apply the weighting when you produce the report or do you store the weighted score (16%) (Your example had no assignment or project scores so I don't know what happens with those) Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604008 Share on other sites More sharing options...
jazal Posted December 29, 2022 Author Share Posted December 29, 2022 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.  Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604028 Share on other sites More sharing options...
jazal Posted December 29, 2022 Author Share Posted December 29, 2022 Just now, jazal said: 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.  like this below Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604029 Share on other sites More sharing options...
jazal Posted December 29, 2022 Author Share Posted December 29, 2022 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 Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604030 Share on other sites More sharing options...
Barand Posted December 29, 2022 Share Posted December 29, 2022 Does every class have a project and an assigment every term so the score wieightings are consistent? (Your previous sample didn't) And my previous question (acknowledging that the weightings are now 0.1, 0.1, 0.1, 0.1, 0.6) - "If a student scores 80% in CA1, do you store this as 80 and apply the weighting when you produce the report or do you store the weighted score (8%)?" 47 minutes ago, jazal said: Is it possible to have everything on database that what i just need to do is just query it out That is the purpose of a database - to store the data you need to produce the outputs you want. The way to get it from the DB to the output is by using queries. So the answer is yes. However, if by "everything", you mean totals, ranks etc as well then, yes you can - if you want to do it stupid. I prefer to do it right. The database tables do not resemble the outputs you want to produce, as you seem to think they should. That's why we use queries and PHP. If you have a results table like this sample (3 students, 3 subjects, one term) +-----------+-----------+------------+-----------+-------------+-------+ | result_id | studentid | semesterid | subjectid | result_type | pcent | +-----------+-----------+------------+-----------+-------------+-------+ | 36 | 936 | 4 | 1 | CA1 | 8 | | 37 | 936 | 4 | 2 | CA1 | 11 | | 38 | 936 | 4 | 3 | CA1 | 5 | | 588 | 936 | 4 | 1 | CA2 | 14 | | 589 | 936 | 4 | 2 | CA2 | 12 | | 590 | 936 | 4 | 3 | CA2 | 5 | | 1140 | 936 | 4 | 1 | Exam | 49 | | 1141 | 936 | 4 | 2 | Exam | 44 | | 1142 | 936 | 4 | 3 | Exam | 17 | | 82 | 2393 | 4 | 1 | CA1 | 12 | | 83 | 2393 | 4 | 2 | CA1 | 8 | | 84 | 2393 | 4 | 3 | CA1 | 9 | | 634 | 2393 | 4 | 1 | CA2 | 12 | | 635 | 2393 | 4 | 2 | CA2 | 9 | | 636 | 2393 | 4 | 3 | CA2 | 11 | | 1186 | 2393 | 4 | 1 | Exam | 38 | | 1187 | 2393 | 4 | 2 | Exam | 34 | | 1188 | 2393 | 4 | 3 | Exam | 42 | | 146 | 3391 | 4 | 1 | CA1 | 8 | | 147 | 3391 | 4 | 2 | CA1 | 8 | | 148 | 3391 | 4 | 3 | CA1 | 8 | | 698 | 3391 | 4 | 1 | CA2 | 11 | | 699 | 3391 | 4 | 2 | CA2 | 11 | | 700 | 3391 | 4 | 3 | CA2 | 13 | | 1250 | 3391 | 4 | 1 | Exam | 50 | | 1251 | 3391 | 4 | 2 | Exam | 49 | | 1252 | 3391 | 4 | 3 | Exam | 54 | +-----------+-----------+------------+-----------+-------------+-------+ then with a query ... SELECT studentid , subject , SUM(IF(result_type='CA1', pcent, null)) as CA1 , SUM(IF(result_type='CA2', pcent, null)) as CA2 , SUM(IF(result_type='Assignment', pcent, null)) as Assignment , SUM(IF(result_type='Project', pcent, null)) as Project , SUM(IF(result_type='Exam', pcent, null)) as Exam , SUM(pcent) as total FROM result r JOIN student st USING (studentid) JOIN subject USING (subjectid) WHERE semesterid = 4 GROUP BY lastname, subjectid; you can produce something that resembles the output you want ... +-----------+---------------+-----+-----+------------+---------+------+-------+ | studentid | subject | CA1 | CA2 | Assignment | Project | Exam | total | +-----------+---------------+-----+-----+------------+---------+------+-------+ | 2393 | English Lang. | 12 | 12 | | | 38 | 62 | | 2393 | Mathematics | 8 | 9 | | | 34 | 51 | | 2393 | BST | 9 | 11 | | | 42 | 62 | | 936 | English Lang. | 8 | 14 | | | 49 | 71 | | 936 | Mathematics | 11 | 12 | | | 44 | 67 | | 936 | BST | 5 | 5 | | | 17 | 27 | | 3391 | English Lang. | 8 | 11 | | | 50 | 69 | | 3391 | Mathematics | 8 | 11 | | | 49 | 68 | | 3391 | BST | 8 | 13 | | | 54 | 75 | +-----------+---------------+-----+-----+------------+---------+------+-------+  Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604032 Share on other sites More sharing options...
jazal Posted December 30, 2022 Author Share Posted December 30, 2022 7 hours ago, Barand said: Does every class have a project and an assigment every term so the score wieightings are consistent? (Your previous sample didn't) And my previous question (acknowledging that the weightings are now 0.1, 0.1, 0.1, 0.1, 0.6) - "If a student scores 80% in CA1, do you store this as 80 and apply the weighting when you produce the report or do you store the weighted score (8%)?"    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 | +-----------+---------------+-----+-----+------------+---------+------+-------+   Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604038 Share on other sites More sharing options...
jazal Posted December 30, 2022 Author Share Posted December 30, 2022 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. Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604039 Share on other sites More sharing options...
Barand Posted December 30, 2022 Share Posted December 30, 2022 As I told you before, you need to get the database right before you embark on the processes - you are not ready yet for complex queries. If you must run before you can crawl then there is an example of ranking queries in the SQL Tutorial link in my signature and also here in this forum. 1 Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604042 Share on other sites More sharing options...
jazal Posted January 3, 2023 Author Share Posted January 3, 2023 Thanks for your support, I will try and work to get the database right. Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604145 Share on other sites More sharing options...
Barand Posted January 3, 2023 Share Posted January 3, 2023 Based on the sample output that you posted, you are short of a couple of tables student assessment scores teachers' remarks. Quote Link to comment https://forums.phpfreaks.com/topic/315729-create-subjects-postion-and-overall-position/page/2/#findComment-1604146 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.