Operations Reference - Primary School Management System

Overview

The Primary School Management System implements CRUD (Create, Read, Update, Delete) operations through PHP scripts that interact with a MySQL database. All operations use MySQLi extension for database connectivity.

Database Connection

All PHP operation files establish a database connection using the following pattern:

<?php
$link = mysqli_connect(
  "sdb-57.hosting.stackcp.net",
  "student84-353031351c89", 
  "ua92-studentAc",
  "student84-353031351c89"
);

// Check connection
if ($link === false) {
    die("Connection failed: ");
}
?>

host

string

Database server hostname: sdb-57.hosting.stackcp.net

username

string

Database username: student84-353031351c89

password

string

Database password: ua92-studentAc

database

string

Database name: student84-353031351c89

Student Operations

Add Student
View Students
Update Student
Delete Student

Add Student Operation

File: AddStudent.php
Table: Student1
Operation: INSERT

Process Flow

Check Form Submission

Verifies if the form was submitted using isset($_POST['submit'])

Extract Form Data

Retrieves all student fields from POST data

Execute INSERT Query

Inserts the new student record into the database

Display Result

Shows success or error message

PHP Variables

$Sname

string

Student’s first name from form input

$Ssurname

string

Student’s last name from form input

$Sbirthday

date

Student’s date of birth from form input

$Parent_ID

string

Parent ID (foreign key reference)

$Class_ID

string

Class ID (foreign key reference)

Database Query

if (isset($_POST['submit'])) {
    $Sname = $_POST['Sname'];
    $Ssurname = $_POST['Ssurname'];
    $Sbirthday = $_POST['Sbirthday'];
    $Parent_ID = $_POST['Parent_ID'];
    $Class_ID = $_POST['Class_ID'];

    $send = "INSERT INTO Student1 (Sname,Ssurname,Sbirthday,Parent_ID,Class_ID) 
             VALUES ('$Sname','$Ssurname','$Sbirthday','$Parent_ID','$Class_ID')";
    
    if (mysqli_query($link, $send)) {
      echo "New record created successfully";
    } else {
      echo "Error adding record ";
    }
}

View Students Operation

File: ViewStudent.php
Table: Student1
Operation: SELECT

Process Flow

Connect to Database

Establishes MySQLi connection

Execute SELECT Query

Retrieves all student records

Fetch Results

Iterates through result set using fetch_assoc()

Display Data

Renders student data in an HTML table

Retrieved Columns

Sid - Student ID (primary key)
Sname - Student first name
Ssurname - Student last name
Sbirthday - Student birthday
Parent_ID - Associated parent ID
Class_ID - Associated class ID

Database Query

$sql = mysqli_query($link, "SELECT Sid,Sname,Ssurname,Sbirthday,Parent_ID,Class_ID FROM Student1");

while ($row = $sql->fetch_assoc()) {
    echo "
    <tr>
        <th>{$row['Sid']}</th>
        <th>{$row['Sname']}</th>
        <th>{$row['Ssurname']}</th>
        <th>{$row['Sbirthday']}</th>
        <th>{$row['Parent_ID']}</th>
        <th>{$row['Class_ID']}</th>
    </tr>";
}

Update Student Operation

File: UptadeStudent.php
Table: Student1
Operation: UPDATE

PHP Variables

$Sid

string

Student ID to identify the record to update

$Sname

string

New student first name

$Ssurname

string

New student last name

$Sbirthday

date

New student birthday

Database Query

if (isset($_POST['submit'])) {
    $Sid = $_POST['Sid'];
    $Sname = $_POST['Sname'];
    $Ssurname = $_POST['Ssurname'];
    $Sbirthday = $_POST['Sbirthday'];

    $uptade = "UPDATE Student1 
               SET Sname = '$Sname', Ssurname = '$Ssurname', Sbirthday='$Sbirthday' 
               WHERE Sid = '$Sid'";
    
    if (mysqli_query($link, $uptade)) {
        echo "Record has been uptaded.";
    } else {
        echo "Error uptading record.";
    }
}

Delete Student Operation

File: DeleteStudent.php
Table: Student1
Operation: DELETE

PHP Variables

$Sid

string

Student ID to identify the record to delete

Database Query

if (isset($_POST['submit'])) {
    $Sid = $_POST['Sid'];
    
    $delete = "DELETE FROM Student1 WHERE Sid=$Sid";
    
    if (mysqli_query($connection, $delete)) {
        echo "Record has been deleted.";
    } else {
        echo "Error deleting record.";
    }
}

This operation permanently removes the student record. Consider implementing soft deletes for data recovery.

Teacher Operations

Add Teacher
View Teachers
Update Teacher
Delete Teacher

Add Teacher Operation

File: AddTeacher.php
Table: Teacher
Operation: INSERT

PHP Variables

$bonus_amount

string

Optional bonus amount for the teacher

$teacher_field

string

Subject or field of expertise

$Tname

string

Teacher’s first name

$Tsurname

string

Teacher’s last name

$Taddress

string

Teacher’s complete address

$Tmobile

string

Teacher’s mobile phone number

$Temail

string

Teacher’s email address

Database Query

if (isset($_POST['submit'])) {
    $bonus_amount = $_POST['bonus_amount'];
    $teacher_field = $_POST['teacher_field'];
    $Tname = $_POST['Tname'];
    $Tsurname = $_POST['Tsurname'];
    $Taddress = $_POST['Taddress'];
    $Tmobile = $_POST['Tmobile'];
    $Temail = $_POST['Temail'];

    $sql = "INSERT INTO Teacher (bonus_amount,teacher_field,Tname,Tsurname,Taddress,Tmobile,Temail) 
            VALUES ('$bonus_amount','$teacher_field','$Tname','$Tsurname','$Taddress','$Tmobile','$Temail')";
    
    if (mysqli_query($link, $sql)) {
      echo "New record created successfully";
    } else {
      echo "Error adding record ";
    }
}

View Teachers Operation

File: ViewTeacher.php
Table: Teacher
Operation: SELECT

Retrieved Columns

Teacher_ID - Teacher ID (primary key)
bonus_amount - Bonus amount
teacher_field - Teaching field/subject
Tname - Teacher first name
Tsurname - Teacher last name
Taddress - Teacher address
Tmobile - Teacher mobile number
Temail - Teacher email

Database Query

$sql = mysqli_query($link, "SELECT Teacher_ID,bonus_amount,teacher_field,Tname,Tsurname,Taddress,Tmobile,Temail FROM Teacher");

while ($row = $sql->fetch_assoc()) {
    echo "
    <tr>
        <th>{$row['Teacher_ID']}</th>
        <th>{$row['bonus_amount']}</th>
        <th>{$row['teacher_field']}</th>
        <th>{$row['Tname']}</th>
        <th>{$row['Tsurname']}</th>
        <th>{$row['Taddress']}</th>
        <th>{$row['Tmobile']}</th>
        <th>{$row['Temail']}</th>
    </tr>";
}

Update Teacher Operation

File: UptadeTeacher.php
Table: Teacher
Operation: UPDATEUpdates teacher information using Teacher_ID as the identifier. All fields from the Add operation can be updated.

Delete Teacher Operation

File: DeleteTeacher.php
Table: Teacher
Operation: DELETEDeletes a teacher record by Teacher_ID.

Parent Operations

Add Parent
View Parents
Update Parent
Delete Parent

Add Parent Operation

File: AddParent.php
Table: Parent
Operation: INSERT

PHP Variables

$pname

string

Parent’s first name (note: variable name lowercase, column uppercase)

$psurname

string

Parent’s last name

$paddress

string

Parent’s complete address

$pemail

string

Parent’s email address (optional)

Database Query

if (isset($_POST['submit'])) {
    $pname = $_POST['Pname'];
    $psurname = $_POST['Psurname'];
    $paddress = $_POST['Paddress'];
    $pemail = $_POST['Pemail'];

    $sql = "INSERT INTO Parent (Pname,Psurname,Paddress,Pemail) 
            VALUES ('$pname','$psurname','$paddress','$pemail')";
    
    if (mysqli_query($link, $sql)) {
      echo "New record created successfully";
    } else {
      echo "Error adding record ";
    }
}

View Parents Operation

File: ViewParent.php
Table: Parent
Operation: SELECTRetrieves and displays all parent records from the database.

Update Parent Operation

File: UptadeParent.php
Table: Parent
Operation: UPDATEUpdates parent information using Parent_ID as the identifier.

Delete Parent Operation

File: DeleteParent.php
Table: Parent
Operation: DELETEDeletes a parent record by Parent_ID.

Class Operations

Add Class
View Classes
Update Class
Delete Class

Add Class Operation

File: AddClass.php
Table: Class
Operation: INSERT

PHP Variables

$classYear

string

Class name or year designation

$capacity

integer

Maximum student capacity

$Teacher_ID

string

ID of the assigned teacher (foreign key)

Database Query

if (isset($_POST['submit'])) {
    $classYear = $_POST['classYear'];
    $capacity = $_POST['capacity'];
    $Teacher_ID = $_POST['Teacher_ID'];

    $sql = "INSERT INTO Class (classYear,capacity,Teacher_ID) 
            VALUES ('$classYear','$capacity','$Teacher_ID')";
    
    if (mysqli_query($link, $sql)) {
      echo "New record created successfully";
    } else {
      echo "Error adding record ";
    }
}

View Classes Operation

File: ViewClass.php
Table: Class
Operation: SELECTRetrieves and displays all class records with their capacity and assigned teacher.

Update Class Operation

File: UptadeClass.php
Table: Class
Operation: UPDATEUpdates class information using Class_ID as the identifier.

Delete Class Operation

File: DeleteClass.php
Table: Class
Operation: DELETEDeletes a class record by Class_ID.

Salary Operations

Add Salary
View Salaries
Update Salary
Delete Salary

Add Salary Operation

File: AddSalary.php
Table: Salary
Operation: INSERT

PHP Variables

$Teacher_ID

string

Teacher ID to assign salary to

$salary_amount

string

Salary amount

$workingTimes

string

Working time type: partTime or fullTime

Database Query

if (isset($_POST['submit'])) {
    $Teacher_ID = $_POST['Teacher_ID'];
    $salary_amount = $_POST['salary_amount'];
    $workingTimes = $_POST['workingTimes'];

    $sql = "INSERT INTO Salary (Teacher_ID,salary_amount,workingTimes) 
            VALUES ('$Teacher_ID','$salary_amount','$workingTimes')";
    
    if (mysqli_query($link, $sql)) {
        echo "New record created successfully";
    } else {
        echo "Error adding record ";
    }
}

View Salaries Operation

File: ViewSalary.php
Table: Salary
Operation: SELECTRetrieves and displays all salary records.

Update Salary Operation

File: UptadeSalary.php
Table: Salary
Operation: UPDATEUpdates salary information using Salary_ID as the identifier.

Delete Salary Operation

File: DeleteSalary.php
Table: Salary
Operation: DELETEDeletes a salary record by Salary_ID.

Gym Member Operations

Add Gym Member

Add Gym Member Operation

File: AddGymMember.php
Table: Gym
Operation: INSERT

PHP Variables

$Sid

string

Student ID reference

$gymFullName

string

Full name of gym member

$userRegDate

date

Registration date

$memberType

string

Membership tier (bronzeMember, silverMember, goldMember, diamondMember)

$medicalCondition

string

Medical conditions (optional)

Special Logic: Membership Expiration Calculation

The system calculates membership expiration based on member type:

$memberType = $_POST['memberType'];

if ($memberType == "bronzeMember") {
    $endingDate = date_create("now");
    date_add($endingDate, date_interval_create_from_date_string("30 days"));
    echo "Your membership will expire on ";
    echo date_format($endingDate, "Y-m-d");
} elseif ($memberType == "silverMember") {
    $endingDate = date_create("now");
    date_add($endingDate, date_interval_create_from_date_string("60 days"));
    echo "Your membership will expire on ";
    echo date_format($endingDate, "Y-m-d");
} elseif ($memberType == "goldMember") {
    $endingDate = date_create("now");
    date_add($endingDate, date_interval_create_from_date_string("90 days"));
    echo "Your membership will expire on ";
    echo date_format($endingDate, "Y-m-d");
} elseif ($memberType == "diamondMember") {
    $endingDate = date_create("now");
    date_add($endingDate, date_interval_create_from_date_string("180 days"));
    echo "Your membership will expire on ";
    echo date_format($endingDate, "Y-m-d");
}

Database Query

if (isset($_POST['submit'])) {
    $Sid = $_POST['Sid'];
    $gymFullName = $_POST['gymFullName'];
    $userRegDate = $_POST['userRegDate'];
    $memberType = $_POST['memberType'];
    $medicalCondition = $_POST['medicalCondition'];

    $gym = "INSERT INTO Gym (Sid,gymFullName,userRegDate,memberType,medicalCondition) 
            VALUES ('$Sid','$gymFullName','$userRegDate','$memberType','$medicalCondition')";
    
    if (mysqli_query($link, $gym)) {
      echo "<br><br>New record created successfully";
    } else {
      echo "<br><br>Error adding record ";
    }
}

Membership duration:

Bronze: 30 days
Silver: 60 days
Gold: 90 days
Diamond: 180 days

View Gym Members

View Gym Members Operation

File: ViewGymMember.php
Table: Gym
Operation: SELECTRetrieves and displays all gym member records.

Update Gym Member

Update Gym Member Operation

File: UptadeGym.php
Table: Gym
Operation: UPDATEUpdates gym member information using gymMemberID as the identifier.

Delete Gym Member

Delete Gym Member Operation

File: DeleteGymMember.php
Table: Gym
Operation: DELETEDeletes a gym member record by gymMemberID.

Contact Operations

Add Contact Message

File: Contact.php
Table: Contact
Operation: INSERT

PHP Variables

$contactName

string

Name of the person submitting the contact

$returnContact

string

Return email address (optional)

$message

text

Message content

Database Query

if (isset($_POST['submit'])) {
    $contactName = $_POST['contactName'];
    $returnContact = $_POST['returnContact'];
    $message = $_POST['message'];
   
    $send = "INSERT INTO Contact (contactName,returnContact,message) 
             VALUES ('$contactName','$returnContact','$message')";
    
    if (mysqli_query($link, $send)) {
      echo "New record created successfully";
    } else {
      echo "Error adding record ";
    }
}

View Contact Messages

File: ViewContact.php
Table: Contact
Operation: SELECT Retrieves and displays all submitted contact messages.

Database Tables Structure

Based on the operations, here are the database tables used:

Student1

Sid (Primary Key)
Sname
Ssurname
Sbirthday
Parent_ID (Foreign Key)
Class_ID (Foreign Key)

Teacher

Teacher_ID (Primary Key)
bonus_amount
teacher_field
Tname
Tsurname
Taddress
Tmobile
Temail

Parent

Parent_ID (Primary Key)
Pname
Psurname
Paddress
Pemail

Class

Class_ID (Primary Key)
classYear
capacity
Teacher_ID (Foreign Key)

Salary

Salary_ID (Primary Key)
Teacher_ID (Foreign Key)
salary_amount
workingTimes

Gym

gymMemberID (Primary Key)
Sid (Foreign Key)
gymFullName
userRegDate
memberType
medicalCondition

Contact

Contact_ID (Primary Key)
contactName
returnContact
message

Common Operation Patterns

INSERT Operations

All INSERT operations follow this pattern:

Verify Submission

Check if form was submitted using isset($_POST['submit'])

Extract POST Data

Retrieve all form fields from $_POST array

Build SQL Query

Construct INSERT query with extracted values

Execute Query

Use mysqli_query($link, $sql) to execute

Return Feedback

Echo success or error message

SELECT Operations

All SELECT operations follow this pattern:

Execute Query

Use mysqli_query($link, $sql) with SELECT statement

Iterate Results

Loop through results using while ($row = $sql->fetch_assoc())

Display Data

Echo HTML table rows with data from $row array

UPDATE Operations

All UPDATE operations follow this pattern:

Get Record ID

Extract the primary key from POST data

Get New Values

Extract updated field values from POST

Build UPDATE Query

Construct UPDATE query with WHERE clause using ID

Execute and Confirm

Execute query and display result message

DELETE Operations

All DELETE operations follow this pattern:

Get Record ID

Extract the primary key from POST data

Build DELETE Query

Construct DELETE query with WHERE clause

Execute and Confirm

Execute query and display result message

Security Vulnerabilities

Critical Security Issues:The current implementation has severe security vulnerabilities that must be addressed:

SQL Injection

All queries use direct string concatenation without sanitization:

Vulnerable Code

$sql = "INSERT INTO Student1 (Sname) VALUES ('$Sname')";

Solution: Use prepared statements with parameterized queries:

Secure Code

$stmt = $link->prepare("INSERT INTO Student1 (Sname) VALUES (?)");
$stmt->bind_param("s", $Sname);
$stmt->execute();

No Input Validation

No server-side validation of user inputs. The system trusts all POST data.Solution: Implement input validation:

Input Validation

if (empty($_POST['Sname']) || strlen($_POST['Sname']) > 100) {
    die("Invalid student name");
}
$Sname = filter_var($_POST['Sname'], FILTER_SANITIZE_STRING);

Exposed Credentials

Database credentials are hardcoded in every PHP file.Solution: Use environment variables or configuration files:

Secure Config

$link = mysqli_connect(
    getenv('DB_HOST'),
    getenv('DB_USER'),
    getenv('DB_PASS'),
    getenv('DB_NAME')
);

No CSRF Protection

Forms lack CSRF tokens, allowing cross-site request forgery attacks.Solution: Implement CSRF tokens in all forms.

Poor Error Handling

Generic error messages provide no debugging information but could expose database structure.Solution: Log detailed errors server-side, show generic messages to users.

Best Practices & Recommendations

Use Prepared Statements

Replace all string concatenation queries with prepared statements to prevent SQL injection

Input Validation

Validate and sanitize all user inputs on the server side

Secure Configuration

Move database credentials to environment variables or secure config files

Error Handling

Implement proper error logging and user-friendly error messages

Transaction Support

Use database transactions for operations that modify multiple tables

Connection Pooling

Implement a database connection class to avoid redundant connections

Data Type Enforcement

Use proper data types in database and validate before insertion

Audit Logging

Log all CRUD operations for accountability and debugging

MySQLi Functions Reference

mysqli_connect()

Establishes a connection to the MySQL database server.Parameters: host, username, password, database
Returns: MySQLi link identifier or false on failure

mysqli_query()

Executes a SQL query on the database.Parameters: connection link, SQL query string
Returns: Result object for SELECT queries, true/false for other queries

fetch_assoc()

Fetches a result row as an associative array.Returns: Associative array with column names as keys
Note: Field names are case-sensitive

isset()

Checks whether a variable is set and not NULL.Returns: true if variable exists and is not NULL, false otherwise
Usage: Verifying form submission

Database

Pages

Documentation Index

​Overview

​Database Connection

​Student Operations

​Add Student Operation

​Process Flow

​PHP Variables

​Database Query

​View Students Operation

​Process Flow

​Retrieved Columns

​Database Query

​Update Student Operation

​PHP Variables

​Database Query

​Delete Student Operation

​PHP Variables

​Database Query

​Teacher Operations

​Add Teacher Operation

​PHP Variables

​Database Query

​View Teachers Operation

​Retrieved Columns

​Database Query

​Update Teacher Operation

​Delete Teacher Operation

​Parent Operations

​Add Parent Operation

​PHP Variables

​Database Query

​View Parents Operation

​Update Parent Operation

​Delete Parent Operation

​Class Operations

​Add Class Operation

​PHP Variables

​Database Query

​View Classes Operation

​Update Class Operation

​Delete Class Operation

​Salary Operations

​Add Salary Operation

​PHP Variables

​Database Query

​View Salaries Operation

​Update Salary Operation

​Delete Salary Operation

​Gym Member Operations

​Add Gym Member Operation

​PHP Variables

​Special Logic: Membership Expiration Calculation

​Database Query

​View Gym Members Operation

​Update Gym Member Operation

​Delete Gym Member Operation

​Contact Operations

​Add Contact Message

​PHP Variables

​Database Query

​View Contact Messages

​Database Tables Structure

Student1

Teacher

Parent

Class

Salary

Gym

Contact

​Common Operation Patterns

​INSERT Operations

​SELECT Operations

​UPDATE Operations

​DELETE Operations

​Security Vulnerabilities

​Best Practices & Recommendations

Use Prepared Statements

Input Validation

Overview

Database Connection

Student Operations

Add Student Operation

Process Flow

PHP Variables

Database Query

View Students Operation

Process Flow

Retrieved Columns

Database Query

Update Student Operation

PHP Variables

Database Query

Delete Student Operation

PHP Variables

Database Query

Teacher Operations

Add Teacher Operation

PHP Variables

Database Query

View Teachers Operation

Retrieved Columns

Database Query

Update Teacher Operation

Delete Teacher Operation

Parent Operations

Add Parent Operation

PHP Variables

Database Query

View Parents Operation

Update Parent Operation

Delete Parent Operation

Class Operations

Add Class Operation

PHP Variables

Database Query

View Classes Operation

Update Class Operation

Delete Class Operation

Salary Operations

Add Salary Operation

PHP Variables

Database Query

View Salaries Operation

Update Salary Operation

Delete Salary Operation

Gym Member Operations

Add Gym Member Operation

PHP Variables

Special Logic: Membership Expiration Calculation

Database Query

View Gym Members Operation

Update Gym Member Operation

Delete Gym Member Operation

Contact Operations

Add Contact Message

PHP Variables

Database Query

View Contact Messages

Database Tables Structure

Common Operation Patterns

INSERT Operations

SELECT Operations

UPDATE Operations

DELETE Operations

Security Vulnerabilities

Best Practices & Recommendations

MySQLi Functions Reference