Lecture Notes on Class 16: Working with Databases -
Part 2
Objective
In this class, students will learn to
perform CRUD (Create, Read, Update, Delete) operations with MySQL using PHP.
Students will practice executing SQL commands and handling query results effectively.
Introduction to CRUD Operations
CRUD operations are fundamental to any
database interaction. They allow users to manipulate data in the database.
Let’s briefly review each operation:
- Create: Add new
records to the database.
- Read: Retrieve
existing records from the database.
- Update: Modify
existing records in the database.
- Delete: Remove
records from the database.
Setting Up the Environment
Before proceeding with the CRUD
operations, ensure you have:
- XAMPP or WAMP
installed on your machine.
- A MySQL database set
up with a sample table to work with.
Sample Database and Table
Assume we have a database called
school with a table named students. The students table has the following
structure:
|
Field |
Type |
|
id |
INT (Primary Key, Auto Increment) |
|
name |
VARCHAR(50) |
|
age |
INT |
|
class |
VARCHAR(50) |
Connecting to the Database
First, let's establish a connection to
the MySQL database using PHP.
php
Copy code
<?php
$servername = "localhost";
$username = "root"; // Your
MySQL username
$password = ""; // Your
MySQL password
$dbname = "school";
// Create connection
$conn = new mysqli($servername,
$username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>
1. Create Operation
To insert new records into the
students table, you can use the INSERT SQL command.
Example Code for Inserting Data
php
Copy code
<?php
$name = "John Doe";
$age = 20;
$class = "10th Grade";
$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;
}
?>
Explanation
- INSERT INTO
students (name, age, class): Specifies the table and columns where
data will be inserted.
- VALUES ('$name',
$age, '$class'): The values to be inserted. Ensure to sanitize inputs
to prevent SQL injection.
2. Read Operation
To retrieve data from the students
table, use the SELECT SQL command.
Example Code for Reading Data
php
Copy code
<?php
$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";
}
?>
Explanation
- SELECT id, name,
age, class FROM students: Retrieves specified columns from the table.
- $result->fetch_assoc():
Fetches a result row as an associative array.
3. Update Operation
To modify existing records, use the
UPDATE SQL command.
Example Code for Updating Data
php
Copy code
<?php
$id = 1; // Assuming we want to update
the record with id 1
$newName = "Jane Doe";
$newAge = 21;
$sql = "UPDATE students SET
name='$newName', age=$newAge WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Record updated successfully";
} else {
echo "Error updating record: " . $conn->error;
}
?>
Explanation
- UPDATE students SET
name='$newName', age=$newAge: Specifies the table and the new values
for the specified columns.
- WHERE id=$id:
Ensures that only the record with the specified id is updated.
4. Delete Operation
To remove records from the students
table, use the DELETE SQL command.
Example Code for Deleting Data
php
Copy code
<?php
$id = 1; // Assuming we want to delete
the record with id 1
$sql = "DELETE FROM students
WHERE id=$id";
if ($conn->query($sql) === TRUE) {
echo "Record deleted successfully";
} else {
echo "Error deleting record: " . $conn->error;
}
?>
Explanation
- DELETE FROM
students: Specifies the table from which to delete.
- WHERE id=$id:
Ensures that only the specified record is deleted.
Conclusion
In this class, we explored how to
perform CRUD operations in a MySQL database using PHP. By executing SQL
commands, students learned how to create, read, update, and delete records.
These operations are essential for managing data in any application.
Practice Exercise
- Create a new record in
the students table.
- Retrieve and display
all records from the students table.
- Update the age of a
specific student in the table.
- Delete a student
record from the table.
Homework
Implement a simple PHP application
that allows users to perform all CRUD operations on the students table through
a web interface. This exercise will solidify your understanding of database
interactions in PHP.