Jump to content

Recommended Posts

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.

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 by jazal
correction

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.

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 

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 

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)

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

Screenshot 2022-12-29 at 22.09.07.png

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

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

 

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

 

 

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.

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.

  • Like 1
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.