Jinsi ya Kutengeneza Exam Results CRUD System

Exam Results CRUD System inawawezesha walimu au admin:

Kuongeza matokeo ya mwanafunzi.

Kuona orodha ya matokeo.

Kuhariri au kufuta matokeo (CRUD).

Kuonyesha statistics au averages (baadaye).

Mfumo huu utatumia:

PDO + Prepared Statements (salama)

Password Hashing kwa user login (ikiwa kuna authentication)

Bootstrap (optional) kwa interface nzuri

⚙️ 2. Database Setup

Tengeneza database na table ya exam_results:

CREATE DATABASE school_db;

USE school_db;

CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
class VARCHAR(50) NOT NULL
);

CREATE TABLE exam_results (
id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
subject VARCHAR(50) NOT NULL,
marks INT NOT NULL,
exam_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
);


💡 Maelezo:

student_id ni foreign key kuunganisha na students.

ON DELETE CASCADE inahakikisha matokeo ya mwanafunzi yanafutwa ikiwa mwanafunzi anaondolewa.

⚙️ 3. Database Connection (config.php)
<?php
$dsn = "mysql:host=localhost;dbname=school_db;charset=utf8mb4";
$username = "root";
$password = "";

try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
die("❌ Connection failed: " . $e->getMessage());
}
?>

➕ 4. Add Exam Result (add_result.php)
<?php
include 'config.php';

// Get students for dropdown
$studentsStmt = $pdo->query("SELECT * FROM students ORDER BY name ASC");
$students = $studentsStmt->fetchAll(PDO::FETCH_ASSOC);

if($_SERVER['REQUEST_METHOD'] === 'POST'){
$student_id = $_POST['student_id'];
$subject = trim($_POST['subject']);
$marks = $_POST['marks'];
$exam_date = $_POST['exam_date'];

$stmt = $pdo->prepare("INSERT INTO exam_results (student_id, subject, marks, exam_date) VALUES (:student_id, :subject, :marks, :exam_date)");
$stmt->execute([
'student_id' => $student_id,
'subject' => $subject,
'marks' => $marks,
'exam_date' => $exam_date
]);

echo "<p style='color:green;'>✅ Exam result added successfully!</p>";
}
?>

<h2>➕ Add Exam Result</h2>
<form method="POST">
<select name="student_id" required>
<option value="">Select Student</option>
<?php foreach($students as $student): ?>
<option value="<?= $student['id'] ?>"><?= htmlspecialchars($student['name'])." - ".$student['class'] ?></option>
<?php endforeach; ?>
</select><br><br>

<input type="text" name="subject" placeholder="Subject" required><br><br>
<input type="number" name="marks" placeholder="Marks" required><br><br>
<input type="date" name="exam_date" required><br><br>
<button type="submit">Add Result</button>
</form>
<a href="results.php">🔙 Back to Results</a>

📄 5. View Exam Results (results.php)
<?php
include 'config.php';

$stmt = $pdo->query("SELECT er.id, s.name, s.class, er.subject, er.marks, er.exam_date
FROM exam_results er
JOIN students s ON er.student_id = s.id
ORDER BY er.exam_date DESC");
?>

<h2>📋 Exam Results</h2>
<a href="add_result.php">➕ Add Result</a><br><br>

<table border="1" cellpadding="8">
<tr>
<th>ID</th>
<th>Student</th>
<th>Class</th>
<th>Subject</th>
<th>Marks</th>
<th>Exam Date</th>
<th>Actions</th>
</tr>

<?php while($row = $stmt->fetch(PDO::FETCH_ASSOC)): ?>
<tr>
<td><?= $row['id'] ?></td>
<td><?= htmlspecialchars($row['name']) ?></td>
<td><?= $row['class'] ?></td>
<td><?= htmlspecialchars($row['subject']) ?></td>
<td><?= $row['marks'] ?></td>
<td><?= $row['exam_date'] ?></td>
<td>
<a href="edit_result.php?id=<?= $row['id'] ?>">✏️ Edit</a> |
<a href="delete_result.php?id=<?= $row['id'] ?>" onclick="return confirm('Are you sure?')">🗑️ Delete</a>
</td>
</tr>
<?php endwhile; ?>
</table>

✏️ 6. Edit Exam Result (edit_result.php)
<?php
include 'config.php';
$id = $_GET['id'];

// Get result
$stmt = $pdo->prepare("SELECT * FROM exam_results WHERE id=:id");
$stmt->execute(['id'=>$id]);
$result = $stmt->fetch(PDO::FETCH_ASSOC);

// Get students for dropdown
$studentsStmt = $pdo->query("SELECT * FROM students ORDER BY name ASC");
$students = $studentsStmt->fetchAll(PDO::FETCH_ASSOC);

if($_SERVER['REQUEST_METHOD'] === 'POST'){
$stmt = $pdo->prepare("UPDATE exam_results SET student_id=:student_id, subject=:subject, marks=:marks, exam_date=:exam_date WHERE id=:id");
$stmt->execute([
'student_id'=>$_POST['student_id'],
'subject'=>$_POST['subject'],
'marks'=>$_POST['marks'],
'exam_date'=>$_POST['exam_date'],
'id'=>$id
]);
echo "<p style='color:green;'>✅ Exam result updated successfully!</p>";
}
?>

<h2>✏️ Edit Exam Result</h2>
<form method="POST">
<select name="student_id" required>
<?php foreach($students as $student): ?>
<option value="<?= $student['id'] ?>" <?= $student['id']==$result['student_id']?'selected':'' ?>>
<?= htmlspecialchars($student['name'])." - ".$student['class'] ?>
</option>
<?php endforeach; ?>
</select><br><br>

<input type="text" name="subject" value="<?= htmlspecialchars($result['subject']) ?>" required><br><br>
<input type="number" name="marks" value="<?= $result['marks'] ?>" required><br><br>
<input type="date" name="exam_date" value="<?= $result['exam_date'] ?>" required><br><br>
<button type="submit">Update Result</button>
</form>
<a href="results.php">🔙 Back to Results</a>

❌ 7. Delete Exam Result (delete_result.php)
<?php
include 'config.php';
$id = $_GET['id'];

$stmt = $pdo->prepare("DELETE FROM exam_results WHERE id=:id");
$stmt->execute(['id'=>$id]);

header("Location: results.php");
exit;
?>

🧠 8. Vidokezo vya Security

PDO + Prepared Statements – Kuzuia SQL Injection.

Input Validation – Hakikisha marks ni nambari sahihi.

Authentication – Zuia users wasio admin ku-access system.

HTTPS – Linda data ya mtumiaji wakati inapotumwa.

CSRF Protection – Kuongeza token kwa forms ili kuzuia attacks.

✅ 9. Hitimisho

Mfumo huu ni msingi wa Exam Results CRUD System.

Admin au teacher anaweza ku-add, edit, delete, na view results kwa urahisi.

PDO na prepared statements zinaboresha security na data integrity.

🔗 Tembelea:

👉 https://www.faulink.com/

Kwa mafunzo zaidi ya PHP, PDO, MySQL, na web systems development.