Create subjects Postion and overall position

Barand · 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.

jazal · December 28, 2022

okay, thanks. will kindly do that

jazal · 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,
  `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

Barand · 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.

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

jazal · December 29, 2022

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

thanks for your support

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

Barand · 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)

jazal · 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.

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

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

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

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

jazal · 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.

Barand · 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.

jazal · January 3, 2023

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

Barand · January 3, 2023

Based on the sample output that you posted, you are short of a couple of tables

student assessment scores
teachers' remarks.

Sign In

Create subjects Postion and overall position

Recommended Posts

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