Student Management System — questions and solutions
PHP and MySQL questions with full worked solutions, built around a student management system.
Source: student_mgmt.docx — course material by Kiran V.K., published here so it is readable without a Google account.
1. Create a Student Table
Question: Write a PHP script to create a students table in the Student Management System.
php
<?php
$dsn = 'mysql:host=sql123.infinityfree.com;dbname=epiz_12345678_testdb;charset=utf8';
$username = 'epiz_12345678';
$password = 'your_password';
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL query to create students table
$sql = "CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
age INT NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
)";
// Execute the query
$pdo->exec($sql);
echo "Table 'students' created successfully.";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>Explanation:
This PHP script creates a students table in the MySQL database. The table includes columns for the id, name, age, and email fields. The id is set to auto-increment as the primary key, ensuring uniqueness.
2. Connect to MySQL Database
Question: Write a PHP script to connect to the MySQL database on Infinity Free and list all tables to confirm the connection.
Solution:
php
<?php
$dsn = 'mysql:host=sql123.infinityfree.com;dbname=epiz_12345678_testdb;charset=utf8';
$username = 'epiz_12345678';
$password = 'your_password';
try {
// Create a new PDO instance
$pdo = new PDO($dsn, $username, $password);
// Set error mode to exceptions
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully to the MySQL database.<br>";
// Query to list all tables in the database
$sql = "SHOW TABLES";
$stmt = $pdo->query($sql);
$tables = $stmt->fetchAll(PDO::FETCH_NUM);
if (count($tables) > 0) {
echo "Tables in the database:<br>";
foreach ($tables as $table) {
echo $table[0] . "<br>";
}
} else {
echo "No tables found in the database.";
}
} catch (PDOException $e) {
echo "Connection failed: " . $e->getMessage();
}
?>Explanation:
This script connects to the MySQL database using PDO and lists all tables found in the connected database. It confirms the connection by displaying the tables, allowing the user to verify that the connection is correct and functional.
3. Insert Student Data (Student Registration)
Question: Write a PHP script to register a new student into the Student Management System.
Solution:
php
<?php
$dsn = 'mysql:host=sql123.infinityfree.com;dbname=epiz_12345678_testdb;charset=utf8';
$username = 'epiz_12345678';
$password = 'your_password';
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL query to insert data
$sql = "INSERT INTO students (name, age, email) VALUES (:name, :age, :email)";
// Prepare statement
$stmt = $pdo->prepare($sql);
// Sample student data (this would come from a form in practice)
$name = "John Doe";
$age = 20;
$email = "john.doe@example.com";
// Bind parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':age', $age);
$stmt->bindParam(':email', $email);
// Execute statement
$stmt->execute();
echo "Student registered successfully.";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>Explanation:
This script registers a new student into the students table by inserting their name, age, and email. PDO handles the SQL statement and safely binds parameters to avoid SQL injection.
4. Form Handling to Insert Student Data
Question: Write a PHP form handling script to register students and store their details in MySQL.
Solution:
php
**HTML Form (registration.html):**
<form action="register.php" method="POST">
Name: <input type="text" name="name" required><br>
Age: <input type="number" name="age" required><br>
Email: <input type="email" name="email" required><br>
<input type="submit" value="Register">
</form>
**PHP Script (register.php):**
<?php
$dsn = 'mysql:host=sql123.infinityfree.com;dbname=epiz_12345678_testdb;charset=utf8';
$username = 'epiz_12345678';
$password = 'your_password';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$name = $_POST['name'];
$age = $_POST['age'];
$email = $_POST['email'];
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL query to insert form data
$sql = "INSERT INTO students (name, age, email) VALUES (:name, :age, :email)";
$stmt = $pdo->prepare($sql);
// Bind parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':age', $age);
$stmt->bindParam(':email', $email);
// Execute query
$stmt->execute();
echo "Student registered successfully.";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
}
?>Explanation:
This script provides a front-end HTML form where students can enter their details. The data is then processed by register.php, which inserts the student information into the MySQL database.
5. Retrieve Student Data (SELECT Query)
Question: Write a PHP script to retrieve all student data from the Student Management System.
Solution:
php
<?php
$dsn = 'mysql:host=sql123.infinityfree.com;dbname=epiz_12345678_testdb;charset=utf8';
$username = 'epiz_12345678';
$password = 'your_password';
try {
$pdo = new PDO($dsn, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// SQL query to select all students
$sql = "SELECT * FROM students";
$stmt = $pdo->query($sql);
// Fetch and display all students
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
if ($students) {
foreach ($students as $student) {
echo "ID: " . $student['id'] . " Name: " . $student['name'] . " Age: " . $student['age'] . " Email: " . $student['email'] . "<br>";
}
} else {
echo "No students found.";
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
?>Explanation:
This script retrieves all students from the students table and displays their details on the web page. The SELECT query fetches all records, and PDO::FETCH_ASSOC is used to return results in an associative array.