FahmidasClassroom

Learn by easy steps

You Are Here

Web Programming

MySQL Fundamentals and PHP Database Connectivity

no Comments

Learning outcomes

At the end of the tutorial, students should be able to:

  1. Create and manage a MySQL database.
  2. Design relational tables using keys and constraints.
  3. Insert, retrieve, update, and delete records.
  4. Perform joins and aggregate queries.
  5. Connect PHP to MySQL using PDO.
  6. Use prepared statements.
  7. Implement basic CRUD operations.
  8. Validate user input.
  9. Handle database errors.
  10. Understand basic transaction management.
  11. Apply fundamental database security principles.

MySQL Fundamentals

Create the Database

Open MySQL and execute:

CREATE DATABASE university_lab

Select the database:

USE university_lab;

Verify:

SELECT DATABASE();

Expected result

university_lab

Create the Tables

We will build the following database:

departments

│

└──── students

│

└──── enrollments ──── courses

This demonstrates one-to-many and many-to-many relationships.

Departments

CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_code VARCHAR(10) NOT NULL UNIQUE,
department_name VARCHAR(100) NOT NULL
);

Students

CREATE TABLE students (
student_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE,
department_id INT NOT NULL,
cgpa DECIMAL(3,2),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_student_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);

Courses

CREATE TABLE courses (
course_id INT AUTO_INCREMENT PRIMARY KEY,
course_code VARCHAR(20) NOT NULL UNIQUE,
course_name VARCHAR(100) NOT NULL,
credit DECIMAL(3,1) NOT NULL
);

Enrollments

CREATE TABLE enrollments (
enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
semester VARCHAR(20) NOT NULL,
FOREIGN KEY (student_id)
REFERENCES students(student_id),
FOREIGN KEY (course_id)
REFERENCES courses(course_id),
UNIQUE(student_id, course_id, semester)
);

Insert Sample Data

Insert departments:

INSERT INTO departments
(department_code, department_name)
VALUES
('CSE', 'Computer Science and Engineering'),
('EEE', 'Electrical and Electronic Engineering'),
('BBA', 'Business Administration'),
('MAT', 'Mathematics');

Insert students:

INSERT INTO students
(name, email, department_id, cgpa)
VALUES
('Amina Rahman', 'amina@example.com', 1, 3.85),
('Rahim Ahmed', 'rahim@example.com', 1, 3.60),
('Karim Hasan', 'karim@example.com', 2, 3.72),
('Nadia Islam', 'nadia@example.com', 3, 3.91),
('Sadia Akter', 'sadia@example.com', 1, 3.45),
('Tanvir Hossain', 'tanvir@example.com', 2, 3.20);

Insert courses:

INSERT INTO courses
(course_code, course_name, credit)
VALUES
('CSE101', 'Computer Fundamentals', 3.0),
('CSE201', 'Database Systems', 3.0),
('CSE301', 'Web Programming', 3.0),
('MAT101', 'Discrete Mathematics', 3.0);

Insert enrollments:

INSERT INTO enrollments
(student_id, course_id, semester)
VALUES
(1, 1, 'Spring-2026'),
(1, 2, 'Spring-2026'),
(1, 3, 'Spring-2026'),
(2, 1, 'Spring-2026'),
(2, 2, 'Spring-2026'),
(3, 1, 'Spring-2026'),
(4, 4, 'Spring-2026');

Basic SELECT Queries

Display all students:

SELECT * FROM students;

Select specific columns:

SELECT student_id, name, cgpa FROM students;

Find students with CGPA above 3.50:

SELECT name, cgpa FROM students WHERE cgpa > 3.50;

Sort by CGPA:

SELECT name, cgpa FROM students ORDER BY cgpa DESC;

Display the top three:

SELECT name, cgpa FROM students ORDER BY cgpa DESC LIMIT 3;

Filtering and Pattern Matching

Find students whose names begin with A:

SELECT * FROM students WHERE name LIKE ‘A%’;

Aggregate Functions

Count students:

SELECT COUNT(*) AS total_students FROM students;

Calculate average CGPA:

SELECT AVG(cgpa) AS average_cgpa FROM students;

Find highest CGPA:

SELECT MAX(cgpa) AS highest_cgpa FROM students;

Find lowest CGPA:

SELECT MIN(cgpa) AS lowest_cgpa FROM students;

GROUP BY CLAUSE

Find the number of students in each department:

SELECT
department_id,
COUNT(*) AS total_students
FROM students
GROUP BY department_id;

GROUP BY with HAVING CLAUSE

Find departments having at least two students:

SELECT
d.department_name,
COUNT(s.student_id) AS total_students
FROM departments AS d
JOIN students AS s
ON d.department_id = s.department_id
GROUP BY
d.department_id,
d.department_name
HAVING COUNT(s.student_id) >= 2;

Remember:

WHERE → filters rows

HAVING → filters groups

UPDATE and DELETE

Update a student’s CGPA:

UPDATE students SET cgpa = 3.75 WHERE student_id = 2;

Verify:

SELECT * FROM students WHERE student_id = 2;

Delete an enrollment:

DELETE FROM enrollments WHERE enrollment_id = 7;

PHP Database Connectivity

Create the PHP Project

Create a project directory such as:

university_lab/

│

├── config/

│ └── database.php

│

├── students/

│ ├── index.php

│ ├── create.php

│ ├── edit.php

│ └── delete.php

│

└── index.php

This structure separates configuration from application functionality.

Create the PDO Connection

Create:

config/database.php

Add:

<?php

$host = 'localhost';
$dbname = 'university_lab';
$username = 'app_user';
$password = 'your_password';
$dsn = "mysql:host=$host;dbname=$dbname;charset=utf8mb4";

try {
$pdo = new PDO(
$dsn,
$username,
$password,
[

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC

]
);
} catch (PDOException $e) {

error_log($e->getMessage());
exit('Database connection failed.');
}

Important settings

PDO::ATTR_ERRMODE

makes database errors available as exceptions.

PDO::ATTR_DEFAULT_FETCH_MODE

sets the default result format.

PDO::FETCH_ASSOC

returns rows as associative arrays.

Test the Connection

Create:

index.php

<?php

require_once 'config/database.php';
echo "Database connection successful.";

Open the application through your local web server.

Expected output:

Database connection successful.

Retrieve Students with PHP

Create:

students/index.php

<?php

require_once '../config/database.php';

$sql = "

SELECT
s.student_id, s.name, s.email, d.department_name, s.cgpa
FROM students AS s
JOIN departments AS d
ON s.department_id = d.department_id
ORDER BY s.student_id DESC";

$stmt = $pdo->query($sql);
$students = $stmt->fetchAll();
?>

<!DOCTYPE html>

<html>
<head>
<meta charset="UTF-8">
<title>Student List</title>
</head>

<body>
<h1>Student List</h1>
<table border="1" cellpadding="8">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Department</th>
<th>CGPA</th>
</tr>

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

<?= htmlspecialchars($student['student_id']) ?>
</td>
<td>
<?= htmlspecialchars($student['name']) ?>
</td>
<td>
<?= htmlspecialchars($student['email']) ?>
</td>
<td>
<?= htmlspecialchars($student['department_name']) ?>
</td>
<td>
<?= htmlspecialchars($student['cgpa']) ?>
</td>
</tr>
<?php endforeach; ?>
</table>
</body>
</html>

Why htmlspecialchars()?

Suppose a database contains:

<script>alert(‘Hello’)</script>

If it is directly printed into HTML, it could be interpreted as executable markup.

Therefore:

htmlspecialchars($value)

converts special HTML characters into safe representations.

This is an example of output encoding.

Create a Student

Create:

students/create.php

<?php

require_once '../config/database.php';
$message = '';

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$name = trim($_POST['name'] ?? '');
$email = trim($_POST['email'] ?? '');
$departmentId = $_POST['department_id'] ?? '';
$cgpa = filter_input(
INPUT_POST,
'cgpa',
FILTER_VALIDATE_FLOAT
);

if ($name === '') {
$message = 'Name is required.';
} elseif (!filter_var($email, FILTER_VALIDATE_EMAIL)) {

$message = 'Invalid email address.';
} elseif (!ctype_digit((string)$departmentId)) {

$message = 'Invalid department.';

} elseif ($cgpa === false || $cgpa < 0 || $cgpa > 4) {
$message = 'CGPA must be between 0 and 4.';
} else {

$sql = "
INSERT INTO students
(name, email, department_id, cgpa)
VALUES
(:name, :email, :department_id, :cgpa)
";

$stmt = $pdo->prepare($sql);
$stmt->execute([
'name' => $name,
'email' => $email,
'department_id' => $departmentId,
'cgpa' => $cgpa
]);

header('Location: index.php');
exit;
}
}
?>

<!DOCTYPE html>

<html>
<head>
<meta charset="UTF-8">
<title>Add Student</title>
</head>

<body>

<h1>Add Student</h1>
<?php if ($message): ?>
<p><?= htmlspecialchars($message) ?></p>
<?php endif; ?>
<form method="post">
<label>
Name:
<input type="text" name="name" required>
</label>

<br><br>
<label>
Email:
<input type="email" name="email" required>
</label>
<br><br>
<label>

Department ID:
<input type="number" name="department_id" required>
</label>

<br><br>
<label>
CGPA:
<input  type="number" name="cgpa" min="0" max="4" step="0.01" required >

</label>
<br><br>
<button type="submit">
Save Student
</button>
</form>
</body>
</html>

Prepared Statements

The following is unsafe:

$sql = "SELECT * FROM students
WHERE name = '$name'";

Instead:

$sql = " SELECT * FROM students WHERE name = :name";
$stmt = $pdo->prepare($sql);
$stmt->execute([
'name' => $name
]);

The second approach separates:

SQL structure

from:

Data values

This is a fundamental defense against SQL injection.

CRUD Operation

Read

Students are displayed using:

$stmt = $pdo->query(
"SELECT student_id, name, email, cgpa
FROM students"
);

$students = $stmt->fetchAll();
Students can then be displayed using a loop:
foreach ($students as $student) {
echo htmlspecialchars($student['name']);
}

Update

A simple update operation:

$studentId = 1;
$newCgpa = 3.95;
$sql = "

UPDATE students
SET cgpa = :cgpa
WHERE student_id = :id";

$stmt = $pdo->prepare($sql);
$stmt->execute([
'cgpa' => $newCgpa,
'id' => $studentId
]);

Delete

$studentId = 5;
$sql = "

DELETE FROM students
WHERE student_id = :id";

$stmt = $pdo->prepare($sql);
$stmt->execute([
'id' => $studentId

]);

For a real web application, deletion should normally use a POST request rather than a simple GET URL.

Search Students

Create a search form:

<form method="get">
<input  type="text" name="q"  placeholder="Search student" >

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

</form>

PHP:
$q = trim($_GET['q'] ?? '');
$sql = "

SELECT student_id, name, email, cgpa
FROM students
WHERE name LIKE :search
OR email LIKE :search
ORDER BY name";

$stmt = $pdo->prepare($sql);
$stmt->execute([
'search' => "%$q%"
]);

$students = $stmt->fetchAll();

This demonstrates a practical use of a parameterized LIKE query.

Exercises:

Employee Salary Filter
Create an employees table containing ID, name, department, and salary. Write a PHP page that accepts a minimum salary and displays all employees earning more than the specified amount using a MySQL query.

Product Stock Update
Create a product table containing Product ID, Product Name, Price, and Quantity. Develop a PHP form that accepts a Product ID and a quantity to add. Update the product’s stock quantity in MySQL and display the updated record.

Department-wise Student Count
Create a students table containing Student ID, Name, Department, and CGPA. Write a PHP program using SQL GROUP BY to display the number of students in each department.

Simple Attendance Calculator
Create a PHP form to enter a student’s name, total classes, and attended classes. Calculate the attendance percentage and store the result in MySQL. Display “Eligible” if attendance is at least 70%, otherwise display “Not Eligible”.