Crio Projects - Student Result Management System | Crio.Do | Project-Based Learning Platform for Developers

Objective

You will be creating a full-stack website that uses the student information from a database as input and generates his/her result in PDF format as output which can then be downloaded and shared.

Project Context

In our day-to-day life, we come across various examination results such as school exam results, competitive exam results, college semester exam results, etc. How efficiently it produces someone's result by just filling in student details! But, have you ever thought of creating one? The ones who did basically went through the hectic job of perfecting their database and then using the knowledge of front-end and back-end to do so.

Developers are known for doing stuff automatically and efficiently. Thus we can have an easier take on it, by creating a system that accepts the student details as input and generates the desired result as output.

Project Stages

SRMS Project Stages

High-Level Approach

  • We will be following a Top Down Approach throughout this project i.e. starting from the end goal, back tracing to the starting point.
  • This project is having 3 sections that we have to implement namely Result Section for student's purpose, Database for storage purpose and Admin Section for administrative purpose.
  • Firstly, we will be understanding the end goal of this project i.e. Result Section.
  • Thereafter, we move towards the Database part starting from naive queries to optimizing it.
  • Then, we will jump to the Admin Section for performing various activities that previously were done manually i.e. data addition and modification.
  • Authentication for admin section will then take place.
  • Then, we will Club things all together into our system.

Primary goals

  • Performing normalization of the database.
  • Performing various database queries.
  • Automating the addition and modification of student data.
  • Implement an authentication system for admin.
  • Generate the result in PDF format and download it.

Applications

  • Create and manage school or institute result portal.
  • Create other types of management system.

Objective

You will be creating a full-stack website that uses the student information from a database as input and generates his/her result in PDF format as output which can then be downloaded and shared.

Project Context

In our day-to-day life, we come across various examination results such as school exam results, competitive exam results, college semester exam results, etc. How efficiently it produces someone's result by just filling in student details! But, have you ever thought of creating one? The ones who did basically went through the hectic job of perfecting their database and then using the knowledge of front-end and back-end to do so.

Developers are known for doing stuff automatically and efficiently. Thus we can have an easier take on it, by creating a system that accepts the student details as input and generates the desired result as output.

Project Stages

SRMS Project Stages

High-Level Approach

  • We will be following a Top Down Approach throughout this project i.e. starting from the end goal, back tracing to the starting point.
  • This project is having 3 sections that we have to implement namely Result Section for student's purpose, Database for storage purpose and Admin Section for administrative purpose.
  • Firstly, we will be understanding the end goal of this project i.e. Result Section.
  • Thereafter, we move towards the Database part starting from naive queries to optimizing it.
  • Then, we will jump to the Admin Section for performing various activities that previously were done manually i.e. data addition and modification.
  • Authentication for admin section will then take place.
  • Then, we will Club things all together into our system.

Primary goals

  • Performing normalization of the database.
  • Performing various database queries.
  • Automating the addition and modification of student data.
  • Implement an authentication system for admin.
  • Generate the result in PDF format and download it.

Applications

  • Create and manage school or institute result portal.
  • Create other types of management system.

Understanding system workflow

Our project is going to be a full stack application. A full stack application primarily comprises of 3 things - front-end, back-end and database.

So, this project is also divided into 3 parts:

  • Result Section (front-end)
  • Database
  • Admin Section (back-end)

We start by settings things up for the front-end, then for database and then move towards to the back-end part.

Requirements

  • A code editor like SUBLIME for writing code for the front-end and back-end.
  • An Apache distribution like XAMPP for handling server and database. Go download it, if not available to your system.
  • Head over to the htdocs folder of XAMPP and create a new folder by your preferred name for this project. Mine is SRMS.
  • All the folders/files will be created in this folder.
  • Execute XAMPP and open the apache and mysql in new tabs.
  • All the data creation/deletion/modification will take place at mysql tab. Go ahead and create a database with the same name as project folder. For my case it will be SRMS.
  • phpmyadmin tab will show you the output from your project. By default it starts with index.php file, you can change it in settings.
  • Instead of dashboard in the URL change it to your project folder name i.e. SRMS then put a forward slash and type your file name that you want to open. Ex. SRMS/homepage.html.
  • Go ahead and create landing page for this project using HTML. Make it stylish using CSS. Make it responsive using Bootstrap.

Note

  • Here I am using HTML + CSS + JavaScript for front-end, MySQL for database and PHP for back-end.
  • Feel free to choose your preferred language for front-end, back-end and follow accordingly.

Expected Outcome

The main objective of this milestone is to make sure that you understand the basic overflow of the project. Also, you should have a code editor and apache distribution in place. With this you should be able to access the MySQL, phpmyadmin home page and your project files.

Dig into the result section

Let's start ourselves from the result section!!

What exactly we want from our system? Yes, you are thinking it right. We want from our system that it should take student details as input such as roll no, class, dob (for hiding it from other students, if required) and produce his/her result as output. Make a .php file with some convenient name and make a form which takes roll no and class as input. On submit, it should perform required SQL query(s) to obtain the result as output.

But wait! From which table are we going to fetch the required data? Did we create any table in the srms database? Yes, you are right, we don't have any table yet. Go ahead and create a table called students. But what should be the attributes in the table? The simplest table should have at least these following attributes -

  • StudentId => unique key for each student
  • StudentName => student name
  • RollId => roll number
  • ClassName => class name
  • ClassSection => section of class (if needed)
  • SubjectName => all subjects name in separate tuple
  • Marks => marks of that particular subject

Apart from that you can add attributes if you want such as gender, mail, contact number, etc. But this should be there for proper data storing and fetching. Shouldn't it?

Requirements

  • Fill some data into your students table on your own for now.
  • Connect your database srms to your PHP page.
  • Store the filled form data in separate variables.
  • Perform a SQL search query in the students table where roll no and class matches with your database entries.
  • Extract each and every row which matches to your search query.
  • Output only selected data such as subject name and corresponding marks.

Bring it On!

  • Go ahead and print some additional details on the result page such as student name, class, total marks obtained, percentage, etc.
  • Make it stylish by aligning the result properly.

Expected Outcome

You should be able to make a database, table and attributes with some data into it. You should also be able to get student data as input, parse it, perform search queries and output it to the user.

Play around with the database

Let's analyze our previous database from various DBMS perspectives.

  1. Is our database consistent?
  2. Is our database efficient?
  3. Is our database scalable?
  4. Is our database usable?
  5. Is our database manageable?

The answer to all the questions is NO, why? Think about it.

Let's suppose if some student data is changed like address, contact number, etc. Then, we have to reflect that change at all the entries in the database because there are many entries for a single unique student depends on number of subjects.

Also, it is not looking great if we have to scale it for various classes, schools, etc. Because at the end data will become more and more redundant. Also, we can't perform other high level operations on this current database due to its not so good looking structure.

Requirements

  • Understand each term that's mentioned above.
  • Read about data normalization in database.
  • Think why is it true.

Bring it On!

  • Head over to the database and try to split it so that it will follow above properties.

Expected Outcome

You should be able to justify why this database will not be a good fit in terms of various purposes that are mentioned above. You should be able to understand what normalization is and why there is need to normalize.

Time to normalize our database

Let's split our original table students into various usable and manageable tables. The tables are split in a manner such that data will remain consistent.

task_4_table_1

task_4_table_2

task_4_table_3

task_4_table_4

task_4_table_5

Requirements

  • Go to MySQL from XAMPP server and create all these tables and corresponding attributes.
  • Delete the previous students table after updating the data according to the new tables.
  • Add other attributes in each table as your wish such as you can add status attribute in each table which can be a boolean variable storing 0/1 depending upon that particular entry is currently in work or not.

Tip

  • You can go ahead and try your own database structure.

Note

  • Don't forgot to make attributes in a table as a foreign key which are from other tables.
  • Primary keys are must to have in a table. Don't forget that too.

Bring it On!

  • Try to fetch the result of a student now using this updated database structure.

Expected Outcome

You should be able to make various tables for this database and its corresponding attributes.

Let's fetch the result now!!

Now, as we have our new database structure, we definitely have to rewrite the SQL queries for student result fetching.

Start from what your user will type at the front-end. We can safely assume that your user will know at least his/her Roll Id, Class Name and Section. But our user doesn't know that how the class name and section is stored in the database. Also, we as developers don't know that how users will type his/her class name and section because there are many possibilities.

So, to make it clear for both the user and you, we should give them a drop-down menu of all the classes in our database. From which table of our database we will do it? Yes, you are right from tblclasses table. User will see his/her class name along with section and in return we will store the class id.

Here's the code for your reference.

<select name="class" class="form-control" id="default" required="required">
<option value="">Select Class</option>
<?php $sql = "SELECT * from tblclasses";
$query = $dbh->prepare($sql);
$query->execute();
$results=$query->fetchAll(PDO::FETCH_OBJ);
if($query->rowCount() > 0) {
foreach($results as $result) { ?>
<option value="<?php echo htmlentities($result->id); ?>"><?php echo htmlentities($result->ClassName); ?>&nbsp; Section-<?php echo htmlentities($result->Section); ?>
</option>
<?php }} ?>
</select>

Store the roll id and class id in some variables on the redirected page after user click submit.

$rollid=$_POST['rollid'];
$classid=$_POST['class'];
$_SESSION['rollid']=$rollid;
$_SESSION['classid']=$classid;

Now, coming to the query part where we bind our above parameters and fetch student result. Result should at least contains his/her subject name (not subject id because user don't know about subject id at all), corresponding marks.

Here's the query for the same.

"select t.StudentName, t.RollId, t.ClassId, t.marks, SubjectId, tblsubjects.SubjectName from 
(select sts.StudentName, sts.RollId, sts.ClassId, tr.marks, SubjectId from 
tblstudents as sts join tblresult as tr on tr.StudentId=sts.StudentId) as t 
join tblsubjects on tblsubjects.id=t.SubjectId where (t.RollId=:rollid and t.ClassId=:classid)"

Requirements

  • Make required number of PHP files and add relevant code as per your understanding.
  • Design your web page as you like.
  • Perform above SQL queries to take input from user and then use it to fetch student result.

Bring it On!

  • Go ahead and add another input parameter such as DOB so that one student can't see other's result without that knowledge.
  • Try to make result page more illustrative and attractive by adding school logo, student name, class name, total marks obtained, percentage, etc.

Expected Outcome

You should be able to fetch and output student result accurately by taking input as per your willingness.

It's admin time now

As said earlier this project is divided into 3 parts:

  • [x] Result Section (front-end)
  • [x] Database
  • [ ] Admin Section (back-end)

We settled up things for the front-end and database, it's time to move towards the back-end part.

But is there any need to develop a back-end part separately? Why this is required?

Till now we have been doing our back-end part with the database. But this is not looking convenient for adding, deleting and modifying data. That's why there is a need to make a separate back-end portal where we can add/update student details like class, subject, subject combination, result and other details through a proper UI.

Requirements

  • Make a separate PHP file for this admin portal.
  • Design your page accordingly.
  • Make a sidebar menu for various options.
  • Make a dedicated page for each options.
  • Attach this sidebar to each of the page.
  • In the sidebar menu following options are must to have, rest you can add on your own interest -
    1. Add and Update Class
    2. Add and Update Students
    3. Add and Update Subjects
    4. Add and Update Subject Combinations
    5. Add and Update Result

Bring it On!

  • Go ahead and try to bind each of the options with the back-end with required database queries.

Expected Outcome

The main motive of this milestone is to understand the concept behind making an admin portal. You should be able to design basic outline of the admin portal.

Building each module

Let's try to build each module (renamed option to module).

Here, I will explain 2 functionalities - add and update classes. Rest all are similar in nature just the SQL query changes.

Add Class

  1. Make a form which takes all the input corresponding to each attribute in tblclasses table.

  2. On submitting the form it should perform a MySQL insert query into the tblclasses of same database.

  3. Your back-end code should resemble below code -

    $classname=$_POST['classname'];
    $section=$_POST['section'];
    $sql="INSERT INTO tblclasses(ClassName, Section) VALUES(:classname, :section)";
    $query = $dbh->prepare($sql);
    $query->bindParam(':classname', $classname, PDO::PARAM_STR);
    $query->bindParam(':section', $section, PDO::PARAM_STR);
    $query->execute();
    $lastInsertId = $dbh->lastInsertId();
    if($lastInsertId) {
    $msg="Class Created successfully";
    } else {
    $error="Something went wrong. Please try again";
    }
    

Update Class

  1. Make a table which shows all the classes details such as serial number, class name, class section, etc. depending on your table structure.

  2. Make an extra column for each row for editing that entry in the database, attach the class id with that value.

  3. Print the existing details in form format for that class id to user using MySQL select command which user can change.

  4. After submitting form, the back-end of that page should perform MySQL update query as follows -

    $classname=$_POST['classname'];
    $section=$_POST['section'];
    $cid=intval($_GET['classid']);
    $sql="update tblclasses set ClassName=:classname, Section=:section where id=:cid";
    $query = $dbh->prepare($sql);
    $query->bindParam(':classname', $classname, PDO::PARAM_STR);
    $query->bindParam(':section', $section, PDO::PARAM_STR);
    $query->bindParam(':cid',$cid, PDO::PARAM_STR);
    $query->execute();
    $msg="Data has been updated successfully";
    

Requirements

  • Perform above mentioned steps for each module.
  • Test each module by cross checking it in phpmyadmin database server.

Bring it On!

  • Try to make an enable/disable button in admin portal for taking DOB also as input during result checking by user.

Expected Outcome

You should able to design, code and test each module using your admin portal and phpmyadmin database server.

Securing admin section

Now, the need arises to secure this admin section. Why? Because otherwise students will change theirs and others results and details which in turn becomes a very serious issue.

There are two ways to secure this admin section - first is to separate this portal completely with new location and project while the second is to make it password protected. First is not actually a full proof solution. Why? Because if anyone gets to know about this portal and its location then eventually the same problem still persists. So, we will go with the second approach.

Requirements

  • Make a new table into database with attributes - UserName and Password.

  • Fill these values depending on the administrative team.

  • Make a form which accepts user name and password as an input and in turns verifies from this table data.

  • If the entries filled are correct then store these values in the session for future reference and land him to the actual admin section.

    $uname=$_POST['username'];
    $password=md5($_POST['password']);
    $sql ="SELECT UserName, Password FROM admin WHERE UserName=:uname and Password=:password";
    $query= $dbh -> prepare($sql);
    $query-> bindParam(':uname', $uname, PDO::PARAM_STR);
    $query-> bindParam(':password', $password, PDO::PARAM_STR);
    $query-> execute();
    $results=$query->fetchAll(PDO::FETCH_OBJ);
    if($query->rowCount() > 0) {
    $_SESSION['alogin']=$_POST['username'];
    echo "<script type='text/javascript'> document.location = 'dashboard.php'; </script>";
    } else {
    echo "<script>alert('Invalid Details');</script>";
    }
    

Note

  • Password is stored in an encrypted format i.e. MD5.
  • Try to understand it why it is required and how to do it.

Bring it On!

  • Check session is expired or not at each page which contains some serious information or which can affect some serious information.
  • If session expires then redirect him to the login page again.

Expected Outcome

The main motive of this milestone is to make admin section secure. You should be able to authenticate the person before sending him to the admin section.

End Game

Now, we are at the end stage of this project. We will try to put all things together and test it. Additionally you will learn how to generate PDF with required content so that one can download and share it wherever he/she wants.

Requirements

  • Make a homepage, design it with school logo, vision, faculty information, contact details.
  • With that make 2 more sections - result and admin.
  • On clicking result section, it should redirect it to result page which will ask user to fill his/her details and get the result.
  • On clicking admin section, it should redirect it to admin authentication page which asks for username and password. On successful verification, it should redirect the user to the admin portal where students details can be added and modified.
  • Make a logout button as well inside admin portal. Upon clicking, it should delete current session details and ask for re-login.
  • For the PDF thing, refer to the references.

Note

  • You can use other PDF generator methods as well.
  • I used PHP throughout this project, you can adjust according to your preferred back-end language.

Bring it On!

  • Host this project publicly, share it with your friends and school/college.
  • Try to add 2 factor authentication using SMS or Mail for admin verification purpose.
  • Try to add more functionalities in this project.
  • Go ahead and make other types of management system using similar logic and structure.

Expected Outcome

The main motive of this milestone is to make every cluster club together and test it's functionalities.