I'm developing a simple student information system, for now i have 300 students and six subjects, so when i want to add marks obtained by each student i use html form and php script to add those marks, for each student i add marks for six subjects that is one subject at a time, so i'm asking if there is the way where by php can allow me retrieve one student and add all the marks for the six subjects at once and then take another and so on. Also i want to calculate total marks for each student and store those total in another table with respective student id so that i can know who is the first student and who is the last by using that total marks of each student.
here is the way i'm doing right now
<?php error_reporting(E_ALL ^ E_NOTICE); ?>
<html>
<body>
<div>
<div>
<form action="connmarks.php" method="post">
<table>
<tr><td colspan="6"> </td></tr>
<tr><td><p>Adding Student Results</p></td></tr>
<tr>
<td width="9%">Student Code<?php echo $mstudentcode;?></td>
<td width="17%"><input name="student_code" type="text" size="30" value="<?php echo $studentcode;?>" /></td></tr>
<tr>
<td width="10%">Subject Code<?php echo $msubjectcode;?></td>
<td width="18%"><input name="subject_code" type="text" size="30" value="<?php echo $subject_code;?>"/></td></tr>
<tr>
<td width="12%">Marks<?php echo $mmark;?></td>
<td width="34%"><input name="mark" type="text" size="30" value="<?php echo $mark;?>"/></td>
</tr>
<td> </td>
<td>
</td>
</tr>
<tr><td colspan="4"> </td></tr>
<tr><td colspan="6"> </td></tr>
<tr>
<td> </td><td colspan="6"><input type="submit" name="save" value="Add Marks" /></td>
</tr>
<tr><td colspan="6"><?php echo $sms1.$sms.$sms2;?></td></tr>
</table>
</form>
</div>
<div id="footer">Copyright <?php echo date("Y", time()); ?></div>
</div>
</body>
</html>
<?php error_reporting(E_ALL ^ E_NOTICE); ?>
<?php
session_start();
if(isset($_POST['save']))
{
// validating student code
if(empty($_POST['student_code']))
{
$mstudentcode='<font color="red"><b>**</b></font>';
}
else
{
$student_code=$_POST['student_code'];
}
// validation for kiswahili subject
if(empty($_POST['subject_code']))
{
$msubjectcode='<font color="red"><b>**</b></font>';
}
else
{
$subject_code=$_POST['subject_code'];
}
// validating english subject
if(empty($_POST['mark']))
{
$mmark='<font color="red"><b>**</b></font>';
}
else
{
$mark=$_POST['mark'];
}
// checking if there is any error message, if no error proceed, if there is error, display the error
// Then exit the script and redirect at the same page
if($mstudentcode||$msubjectcode||$mmark||$sms)
{
$sms1='<font color="red"><b>Error found,please check **</b></font><br/>';
include 'addmarks.php';
exit;
}
// if there is no error include connection file
if($student_code&&$subject_code&&$mark)
{
// include 'mysqli_connect.php';
require_once ('../../mysqli_connect.php');
$addmarks= "insert into result(student_code,subject_code,mark) values ('".$student_code."','".$subject_code."','".$mark."')";
$k = mysqli_query($dbc, $addmarks);
if ($k)
{
$sms1='<font color="green"><b>Student Marks Submitted Successfully</b></font><br/>';
include 'addmarks.php';
exit;
}
else
{
$sms1='<font color="red"><b>Failed To Add Student Marks</b></font><br/>';
include 'addmarks.php';
exit;
}
}
}
?>
<!DOCTYPE html>
<html>
<head>
<title></title>
</head>
<body>
<form method="post">
<!-- Displays all users in database in a select option -->
<select name="students">
<option>Student 1</option>
<option>Student 2</option>
<?php
//This code below is what you will need to use for yours to pull values out of the database(changing the values to suit yours obviously).
// $query = "SELECT * FROM students ORDER BY student_name ASC";
// $result = mysqli_query($conn,"$query");
// while($row = mysqli_fetch_assoc($result)){
// echo "<option>" . $row['student_name'] . "<br></option>";
// }
?>
</select><br>
<!-- All the different input fields for maths, english and science -->
<input type="text" name="eng_grade" value="" placeholder="Enter English Grade"><br>
<input type="text" name="math_grade" value="" placeholder="Enter Maths Grade"><br>
<input type="text" name="science_grade" value="" placeholder="Enter Science Grade"><br>
<input type="submit" name="submit" value="Submit">
</form>
<?php
//If submit is pressed
if (isset($_POST['submit'])) {
//this gets the value of the student name from the select box and stores it as $student
$student = $_POST['students'];
//These gets the values stored in the inputs above and store them in the 3 vairables
$english = $_POST['eng_grade'];
$maths = $_POST['math_grade'];
$science = $_POST['science_grade'];
//this is a mysql query that updates the data with whatever you put into the database(to add to existing tables you will have to dig abit deeper and create your
//database with all the correct fields!
$query = "UPDATE students SET maths_grade = '$maths', $english_grade = '$english', science_grade = '$science' WHERE student_name = '$student'";
$update = mysqli_query($conn, "$query"); //<-- this inserts the values into the database, changing the current #null value (null means nothing is in it)
}
?>
</body>
</html>
Create 2 tables, one for student and other for marks.
In one table, just insert student name and id(unique), and in other table student all 6 subject marks i.e. 6 columns for 6 sub, 1 for student id, and other one as auto increment id column.
So using that student id you can retrieve all the subject marks of a particular student.
Edit: Just noticed this question is 3 years old w00t!?
Drafting tables would be something like
student table:
id primarykey integer
name notnullable nvarchar(255)
subject table
id primarykey integer
name notnullable nvarchar(255)
student_subject table
id primarykey integer
student_id foreignkey notnullable unique integer
subject_id foreignkey notnullable unique integer
mark notnullable double(2,2)
E.g. Select the marks of a subject
Select student.name, subject.name, subject_student.mark from subject_student
inner join student on student.id = subject_student.student_id
inner join subjecton subject.id = subject_student.subject_id
where student_id = X; // X is the id of the student you want
Any calculation should be based on query, you don't want to store results in the database. You want data in the database and since it's volatil data (can be changed anytime), it's easier and faster to calculate whenever required. Sum, GroupBy, there are a ton of basic sql keywords that can help you. student_subject table is the table where it combines that Many students in Many subjects with a certain mark. If you want to save a student's mark, you just Insert into this table the id's and the mark value.
Since it's a school project, this should be suffice for you to work it out. In the future, take a look at prepared statements