STUDENTS CRUD

The students table contains:

code text

student_id
student_first_name
student_last_name
student_course
student_created_at

The basic CRUD operations are:

code text

CREATE → INSERT
READ   → SELECT
UPDATE → UPDATE
DELETE → DELETE

---

2.1 CREATE Student

Use Case

The user enters:

code text

First Name
Last Name
Course

and submits the form. ---

Get Form Data

code php

$firstName = trim($_POST['student_first_name'] ?? '');
$lastName  = trim($_POST['student_last_name'] ?? '');
$course    = trim($_POST['student_course'] ?? '');

The values come from the HTML form:

code html

<form method="POST">

    <input
        type="text"
        name="student_first_name"
    >

    <input
        type="text"
        name="student_last_name"
    >

    <input
        type="text"
        name="student_course"
    >

    <button type="submit">
        Save
    </button>

</form>

---

SQL Query

code sql

INSERT INTO students (
    student_first_name,
    student_last_name,
    student_course
)
VALUES (?, ?, ?);

---

PHP PDO

code php

$stmt = $pdo->prepare("
    INSERT INTO students (
        student_first_name,
        student_last_name,
        student_course
    )
    VALUES (?, ?, ?)
");

$stmt->execute([
    $firstName,
    $lastName,
    $course
]);

The ? characters are placeholders. The values are supplied through:

code php

$stmt->execute([
    $firstName,
    $lastName,
    $course
]);

---

Database Result

For example:

code text

First Name: CLIFF AMADEUS
Last Name: EVANGELIO
Course: BSIT

creates:

code text

student_id: 5
student_first_name: CLIFF AMADEUS
student_last_name: EVANGELIO
student_course: BSIT
student_created_at: current timestamp

The student_id is automatically generated because it uses:

code sql

AUTO_INCREMENT

---

2.2 READ Students

Use Case

Display all students in an HTML table. ---

SQL Query

code sql

SELECT *
FROM students
ORDER BY student_id DESC;

---

PHP PDO

code php

$stmt = $pdo->query("
    SELECT *
    FROM students
    ORDER BY student_id DESC
");

$students = $stmt->fetchAll();

fetchAll() retrieves all matching records. ---

Display the Result

code php

<?php foreach ($students as $student): ?>

    <?= htmlspecialchars($student['student_id']) ?>

    <?= htmlspecialchars($student['student_first_name']) ?>

    <?= htmlspecialchars($student['student_last_name']) ?>

    <?= htmlspecialchars($student['student_course']) ?>

<?php endforeach; ?>

---

Read One Student

When editing a student, we first retrieve one record. URL:

code text

index.php?section=students&action=update&id=1

PHP:

code php

$studentId = (int) ($_GET['id'] ?? 0);

SQL:

code sql

SELECT *
FROM students
WHERE student_id = ?;

PHP:

code php

$stmt = $pdo->prepare("
    SELECT *
    FROM students
    WHERE student_id = ?
");

$stmt->execute([
    $studentId
]);

$student = $stmt->fetch();

fetch() retrieves one record. ---

2.3 UPDATE Student

Use Case

The user edits:

code text

First Name
Last Name
Course

and submits the updated information. ---

SQL Query

code sql

UPDATE students
SET
    student_first_name = ?,
    student_last_name = ?,
    student_course = ?
WHERE student_id = ?;

---

PHP PDO

code php

$stmt = $pdo->prepare("
    UPDATE students
    SET
        student_first_name = ?,
        student_last_name = ?,
        student_course = ?
    WHERE student_id = ?
");

$stmt->execute([
    $firstName,
    $lastName,
    $course,
    $studentId
]);

The WHERE clause identifies which student should be updated. For example:

code sql

UPDATE students
SET
    student_first_name = 'CLIFF',
    student_last_name = 'EVANGELIO',
    student_course = 'BSIT'
WHERE student_id = 1;

Only student 1 is updated. ---

2.4 DELETE Student

Use Case

The user clicks:

code text

Delete

for a student. ---

Get ID

code php

$studentId = (int) ($_GET['id'] ?? 0);

---

SQL Query

code sql

DELETE FROM students
WHERE student_id = ?;

---

PHP PDO

code php

$stmt = $pdo->prepare("
    DELETE FROM students
    WHERE student_id = ?
");

$stmt->execute([
    $studentId
]);

---

Important: Foreign Key Restriction

The database contains:

code sql

ON DELETE RESTRICT

for the relationship between borrow and students. Therefore, if a student has a borrow record, the student cannot be deleted. For example:

code text

Student #1
    |
    v
Borrow #1

Trying to delete Student #1 will be rejected by MySQL. This protects the relationship between the tables. ---

2.5 Students CRUD Summary

code text

| CRUD | SQL | PHP Method |
|---|---|---|
| Create | `INSERT` | `prepare()` + `execute()` |
| Read | `SELECT` | `query()` + `fetchAll()` |
| Read One | `SELECT WHERE` | `prepare()` + `fetch()` |
| Update | `UPDATE` | `prepare()` + `execute()` |
| Delete | `DELETE` | `prepare()` + `execute()` |

format_list_numbered Step 3 of 9

chevron_left Previous Next chevron_right