Assignments For Class 16: Working with Databases - Part 2
Here are some assignments based on the CRUD operations with MySQL and PHP, along with their solutions and explanations. These assignments will help reinforce the concepts learned in Class 16.
Assignment 1: Create a New Student Record
Task: Write a PHP script to create a new student record in the students table. The record should include the student’s name, age, and class.
Solution:
php
Copy code
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "school";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Variables for new student data
$name = "Alice Smith";
$age = 22;
$class = "12th Grade";
// SQL query to insert data
$sql = "INSERT INTO students (name, age, class) VALUES ('$name', $age, '$class')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
// Close connection
$conn->close();
?>
Explanation:
- This script connects to the MySQL database and defines variables for the new student's information.
- An INSERT SQL query is constructed and executed. If successful, a confirmation message is displayed.
Assignment 2: Retrieve All Student Records
Task: Write a PHP script to retrieve and display all records from the students table.
Solution:
php
Copy code
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "school";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to select all records
$sql = "SELECT id, name, age, class FROM students";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Age: " . $row["age"]. " - Class: " . $row["class"]. "<br>";
}
} else {
echo "0 results";
}
// Close connection
$conn->close();
?>
Explanation:
- The script connects to the database and runs a SELECT query to fetch all records from the students table.
- The results are displayed in a loop, and if no records are found, it shows "0 results".
Assignment 3: Update a Student's Age
Task: Write a PHP script to update the age of a student with a specific ID.
Solution:
php
Copy code
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "school";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Variables for updating student data
$id = 2; // Assuming we want to update the record with ID 2
$newAge = 23;
// SQL query to update data
$sql = "UPDATE students SET age=$newAge WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
// Close connection
$conn->close();
?>
Explanation:
- This script connects to the database and specifies the ID of the student whose age needs to be updated.
- An UPDATE SQL query is executed to modify the age for the specified student ID.
Assignment 4: Delete a Student Record
Task: Write a PHP script to delete a student record based on a specific ID.
Solution:
php
Copy code
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "school";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Variable for the student ID to delete
$id = 3; // Assuming we want to delete the record with ID 3
// SQL query to delete data
$sql = "DELETE FROM students WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
// Close connection
$conn->close();
?>
Explanation:
- The script connects to the database and specifies the ID of the student to be deleted.
- A DELETE SQL command is executed, and a success or error message is displayed based on the outcome.
Assignment 5: Display Students Above a Certain Age
Task: Write a PHP script to display all students who are older than a specified age.
Solution:
php
Copy code
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "school";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Define the age limit
$ageLimit = 18;
// SQL query to select students older than age limit
$sql = "SELECT id, name, age, class FROM students WHERE age > $ageLimit";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"]. " - Name: " . $row["name"]. " - Age: " . $row["age"]. " - Class: " . $row["class"]. "<br>";
}
} else {
echo "No students found above age $ageLimit";
}
// Close connection
$conn->close();
?>
Explanation:
- This script connects to the database and executes a SELECT query to find all students whose age is greater than the specified limit.
- The results are displayed; if no students meet the criteria, it indicates that no students were found.
Assignment 6: Count Total Students in Each Class
Task: Write a PHP script to count the total number of students in each class and display the results.
Solution:
php
Copy code
<?php
// Database connection
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "school";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// SQL query to count students in each class
$sql = "SELECT class, COUNT(*) as total_students FROM students GROUP BY class";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// Output data of each row
while($row = $result->fetch_assoc()) {
echo "Class: " . $row["class"]. " - Total Students: " . $row["total_students"]. "<br>";
}
} else {
echo "No students found";
}
// Close connection
$conn->close();
?>
Explanation:
- The script connects to the database and executes a SELECT query that counts the total number of students in each class using the GROUP BY clause.
- It outputs the class name along with the count of students.
Conclusion
These assignments cover various CRUD operations, allowing students to practice connecting to the database, executing SQL commands, and handling query results effectively. By completing these tasks, students will gain hands-on experience that reinforces their understanding of database interactions with PHP.