Introduction
Database operations are an important part of dynamic PHP applications. They allow users to add new information, view existing records, modify data and remove unwanted entries. In this chapter, you will practise the four main database operations—INSERT, SELECT, UPDATE and DELETE—using PHP, MySQL and prepared statements. Insert, Select, Update and Delete Records in PHP Practice questions with solutions to help you to understand the concepts.
Database Setup
Before starting the questions, create a database named school_db and a table named students.
SQL Table Structure
CREATE DATABASE school_db;
USE school_db;
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
course VARCHAR(100),
marks INT
);
Insert Sample Records
INSERT INTO students (name, email, course, marks)
VALUES
('Rahul', 'rahul@gmail.com', 'PHP', 85),
('Priya', 'priya@gmail.com', 'Python', 90),
('Amit', 'amit@gmail.com', 'Java', 75),
('Neha', 'neha@gmail.com', 'PHP', 95),
('Karan', 'karan@gmail.com', 'Python', 80);
The following questions use this database and table.
Question 1: Insert a New Student Record Using PHP and MySQLi
Problem Statement
A school wants to add a new student to its database.
Create a PHP program that inserts the following information into the students table using a MySQLi prepared statement.
| Name | Course | Marks | |
|---|---|---|---|
| Anjali | anjali@gmail.com | PHP | 88 |
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "INSERT INTO students (name, email, course, marks)
VALUES (?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$name = "Anjali";
$email = "anjali@gmail.com";
$course = "PHP";
$marks = 88;
$stmt->bind_param("sssi", $name, $email, $course, $marks);
if ($stmt->execute()) {
echo "Student record inserted successfully!";
} else {
echo "Unable to insert student record.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Student record inserted successfully!
Explanation
INSERT INTOadds a new record in PHP to a database table.- The column names specify where the information will be stored.
- Each
?represents a placeholder for a value. prepare()prepares the SQL statement.bind_param("sssi", ...)attaches the values to the placeholders.srepresents a string, andirepresents an integer.execute()runs the INSERT query.
The database automatically generates the student ID because the id column uses AUTO_INCREMENT.
Concepts Covered
- INSERT query
- MySQLi connection
- Prepared statements
bind_param()execute()
Question 2: Insert Multiple Student Records Using PHP
Problem Statement
A school wants to register three students at the same time.
Create a PHP program that inserts the following records into the students table using a prepared statement.
| Name | Course | Marks | |
|---|---|---|---|
| Mohit | mohit@gmail.com | Python | 92 |
| Pooja | pooja@gmail.com | PHP | 87 |
| Arjun | arjun@gmail.com | Java | 78 |
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "INSERT INTO students (name, email, course, marks)
VALUES (?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$students = [
["Mohit", "mohit@gmail.com", "Python", 92],
["Pooja", "pooja@gmail.com", "PHP", 87],
["Arjun", "arjun@gmail.com", "Java", 78]
];
$stmt->bind_param("sssi", $name, $email, $course, $marks);
foreach ($students as $student) {
[$name, $email, $course, $marks] = $student;
$stmt->execute();
}
echo "All student records inserted successfully!";
$stmt->close();
$conn->close();
?>
Sample Output
All student records inserted successfully!
Explanation
This program inserts multiple records without writing a separate INSERT query for every student.
$studentsstores three student records in a multidimensional array.prepare()creates the SQL statement once.bind_param()connects the variables to the placeholders.foreachprocesses each student record in PHP.- Each iteration assigns the student’s details to the variables.
execute()inserts the current student’s information.
The same prepared statement is reused for all three records.
Concepts Covered
- Multiple record in PHP insertion
- Multidimensional arrays
foreachloop- Prepared statement reuse
- MySQLi
bind_param()
Question 3: Display All Student Records Using SELECT
Problem Statement
A school administrator wants to view all students registered in the database.
Create a PHP program that retrieves all records from the students table and displays their ID, name, email, course and marks.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "SELECT * FROM students";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . "<br>";
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Email: " . htmlspecialchars($row["email"]) . "<br>";
echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
echo "Marks: " . $row["marks"] . "<br><br>";
}
} else {
echo "No student records found.";
}
$conn->close();
?>
Sample Output
The following output assumes the original five sample records are present.
ID: 1
Name: Rahul
Email: rahul@gmail.com
Course: PHP
Marks: 85
ID: 2
Name: Priya
Email: priya@gmail.com
Course: Python
Marks: 90
ID: 3
Name: Amit
Email: amit@gmail.com
Course: Java
Marks: 75
ID: 4
Name: Neha
Email: neha@gmail.com
Course: PHP
Marks: 95
ID: 5
Name: Karan
Email: karan@gmail.com
Course: Python
Marks: 80
Explanation
SELECT * FROM studentsretrieves all columns and records.$conn->query()executes the SELECT query.num_rowschecks how many records were returned.fetch_assoc()retrieves one record in PHP as an associative array.whilerepeats until all records have been retrieved.htmlspecialchars()safely displays text in HTML.
Concepts Covered
- SELECT query
- Retrieving database record in PHP
query()num_rowsfetch_assoc()- Safe HTML output
Question 4: Retrieve a Student Record Using Student ID
Problem Statement
A school administrator wants to find the details of a particular student using their ID.
Create a PHP program that retrieves the student record whose ID is 3.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "SELECT * FROM students WHERE id = ?";
$stmt = $conn->prepare($sql);
$studentId = 3;
$stmt->bind_param("i", $studentId);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
echo "Student ID: " . $row["id"] . "<br>";
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Email: " . htmlspecialchars($row["email"]) . "<br>";
echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
echo "Marks: " . $row["marks"];
} else {
echo "Student not found.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Student ID: 3
Name: Amit
Email: amit@gmail.com
Course: Java
Marks: 75
Explanation
WHERE id = ?searches for a record in PHP with a specific ID.prepare()prepares the query.bind_param("i", $studentId)binds the student ID as an integer.execute()runs the query.get_result()retrieves the query result as a result object.fetch_assoc()retrieves the matching student record.
Note: MySQLi’s get_result() requires the MySQL Native Driver (mysqlnd). If it is unavailable, use bind_result() and fetch() instead.
Concepts Covered
- SELECT with WHERE
- Searching by ID
- Prepared statements
get_result()fetch_assoc()
Question 5: Update Student Marks Using UPDATE
Problem Statement
A teacher wants to correct Amit’s marks.
The current marks are 75, and the new marks should be 85.
Create a PHP program that updates Amit’s marks using his student ID.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "UPDATE students SET marks = ? WHERE id = ?";
$stmt = $conn->prepare($sql);
$newMarks = 85;
$studentId = 3;
$stmt->bind_param("ii", $newMarks, $studentId);
if ($stmt->execute()) {
if ($stmt->affected_rows > 0) {
echo "Student marks updated successfully!";
} else {
echo "No record was changed.";
}
} else {
echo "Unable to update student marks.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Student marks updated successfully!
Explanation
UPDATE studentsidentifies the table to modify.SET marks = ?specifies the new marks.WHERE id = ?identifies the student whose marks should change.bind_param("ii", ...)binds both values as integers.execute()performs the update.affected_rowsreports how many rows were changed.
If the student already has 85 marks, the query may execute successfully but report zero changed rows.
Concepts Covered
- UPDATE query
- SET clause
- WHERE condition
- Prepared statements
affected_rows
Question 6: Update Multiple Columns of a Student Record
Problem Statement
A student has changed their email address and course.
Update the following information for student ID 2.
| Field | Old Value | New Value |
|---|---|---|
| priya@gmail.com | priya.sharma@gmail.com | |
| Course | Python | Data Analytics |
Create a PHP program that updates both columns in one SQL query.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "UPDATE students
SET email = ?, course = ?
WHERE id = ?";
$stmt = $conn->prepare($sql);
$email = "priya.sharma@gmail.com";
$course = "Data Analytics";
$studentId = 2;
$stmt->bind_param("ssi", $email, $course, $studentId);
if ($stmt->execute()) {
if ($stmt->affected_rows > 0) {
echo "Student information updated successfully!";
} else {
echo "No record was changed.";
}
} else {
echo "Unable to update student information.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Student information updated successfully!
Explanation
A single UPDATE query can modify multiple columns.
SET email = ?, course = ?specifies two columns to update.WHERE id = ?identifies the student."ssi"means string, string and integer.bind_param()attaches all three values to the query.execute()updates the selected record.
Without the WHERE condition, the update could affect every student in the table.
Concepts Covered
- Updating multiple columns
- UPDATE query
- Multiple placeholders
bind_param()- WHERE condition
Question 7: Delete a Student Record Using DELETE
Problem Statement
A school wants to remove Karan’s record from the database because he has left the institution.
Create a PHP program that deletes the student whose ID is 5.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "DELETE FROM students WHERE id = ?";
$stmt = $conn->prepare($sql);
$studentId = 5;
$stmt->bind_param("i", $studentId);
if ($stmt->execute()) {
if ($stmt->affected_rows > 0) {
echo "Student record deleted successfully!";
} else {
echo "Student record not found.";
}
} else {
echo "Unable to delete student record.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Student record deleted successfully!
Explanation
DELETE FROM studentsremoves records from the students table.WHERE id = ?identifies the record to delete.bind_param("i", $studentId)binds the student ID.execute()runs the DELETE query.affected_rowschecks whether a record was deleted.
Important: Never execute a DELETE query without checking its WHERE condition when you intend to remove only one particular record. Without WHERE, all records can be deleted.
Concepts Covered
- DELETE query
- Prepared statements
- WHERE condition
affected_rows- Record deletion
Question 8: Search Student Records by Course
Problem Statement
A school administrator wants to display all students enrolled in the PHP course.
Create a PHP program that retrieves only those records where the course is PHP.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "SELECT * FROM students WHERE course = ?";
$stmt = $conn->prepare($sql);
$course = "PHP";
$stmt->bind_param("s", $course);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
echo "Students Enrolled in PHP Course<br><br>";
while ($row = $result->fetch_assoc()) {
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Email: " . htmlspecialchars($row["email"]) . "<br>";
echo "Marks: " . $row["marks"] . "<br><br>";
}
} else {
echo "No students found in this course.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Assuming the original five records are still present and no previous exercises have changed them:
Students Enrolled in PHP Course
Name: Rahul
Email: rahul@gmail.com
Marks: 85
Name: Neha
Email: neha@gmail.com
Marks: 95
Explanation
WHERE course = ?filters records by course name.bind_param("s", $course)supplies the course name as a string.execute()runs the prepared query.get_result()returns the matching records.num_rowschecks whether matching students exist.fetch_assoc()retrieves each matching student.
The query returns only records where the course matches PHP.
Concepts Covered
- SELECT with WHERE
- Filtering records
- Prepared statements
- Retrieving matching records
num_rows
Question 9: Display Student Records in Descending Order of Marks
Problem Statement
A school wants to prepare a merit list by displaying students from highest marks to lowest marks.
Create a PHP program that retrieves all students and sorts them by marks in descending order.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "SELECT * FROM students ORDER BY marks DESC";
$result = $conn->query($sql);
echo "Student Merit List<br><br>";
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
echo "Marks: " . $row["marks"] . "<br><br>";
}
} else {
echo "No student records found.";
}
$conn->close();
?>
Sample Output
For the original five records:
Student Merit List
Name: Neha
Course: PHP
Marks: 95
Name: Priya
Course: Python
Marks: 90
Name: Rahul
Course: PHP
Marks: 85
Name: Karan
Course: Python
Marks: 80
Name: Amit
Course: Java
Marks: 75
Explanation
SELECT *retrieves all student records.ORDER BY markssorts the records using the marks column.DESCarranges records from highest to lowest.query()executes the SQL statement.fetch_assoc()retrieves one student at a time.
For ascending order, SQL uses ASC.
Concepts Covered
- SELECT query
- ORDER BY
- DESC sorting
- Retrieving records
- Merit list generation
Question 10: Build a Simple Student Management Program Using CRUD Operations
Problem Statement
A school wants a basic PHP program that can perform the four main database operations:
- Insert a new student.
- Display all students.
- Update a student’s marks.
- Delete a student.
Create a PHP program using MySQLi prepared statements to perform these operations.
For this practice example, the program will perform the operations in sequence using fixed sample values.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
// 1. INSERT: Add a new student
$sql = "INSERT INTO students (name, email, course, marks)
VALUES (?, ?, ?, ?)";
$stmt = $conn->prepare($sql);
$name = "Rohit";
$email = "rohit@gmail.com";
$course = "PHP";
$marks = 89;
$stmt->bind_param("sssi", $name, $email, $course, $marks);
if ($stmt->execute()) {
echo "1. Student inserted successfully.<br><br>";
}
$stmt->close();
// 2. SELECT: Display all students
$sql = "SELECT * FROM students";
$result = $conn->query($sql);
echo "2. Student Records:<br><br>";
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row["id"] . "<br>";
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
echo "Marks: " . $row["marks"] . "<br><br>";
}
// 3. UPDATE: Change Rohit's marks
$sql = "UPDATE students SET marks = ? WHERE email = ?";
$stmt = $conn->prepare($sql);
$newMarks = 95;
$email = "rohit@gmail.com";
$stmt->bind_param("is", $newMarks, $email);
if ($stmt->execute()) {
echo "3. Student marks updated successfully.<br><br>";
}
$stmt->close();
// 4. DELETE: Remove Rohit's record
$sql = "DELETE FROM students WHERE email = ?";
$stmt = $conn->prepare($sql);
$email = "rohit@gmail.com";
$stmt->bind_param("s", $email);
if ($stmt->execute()) {
echo "4. Student record deleted successfully.<br><br>";
}
$stmt->close();
$conn->close();
?>
Sample Output
The output below assumes the original five records are present before the program runs.
1. Student inserted successfully.
2. Student Records:
ID: 1
Name: Rahul
Course: PHP
Marks: 85
ID: 2
Name: Priya
Course: Python
Marks: 90
ID: 3
Name: Amit
Course: Java
Marks: 75
ID: 4
Name: Neha
Course: PHP
Marks: 95
ID: 5
Name: Karan
Course: Python
Marks: 80
ID: 6
Name: Rohit
Course: PHP
Marks: 89
3. Student marks updated successfully.
4. Student record deleted successfully.
Explanation
This program demonstrates all four CRUD operations.
1. INSERT
The INSERT query adds Rohit to the students table.
2. SELECT
The SELECT query retrieves all records and displays the student information.
3. UPDATE
The UPDATE query changes Rohit’s marks from 89 to 95.
4. DELETE
The DELETE query removes Rohit’s record from the table.
CRUD stands for:
| Operation | SQL Command | Purpose |
|---|---|---|
| Create | INSERT | Add new records |
| Read | SELECT | Retrieve records |
| Update | UPDATE | Modify existing records |
| Delete | DELETE | Remove records |
Important: This is a sequential practice program, not a complete user-facing management system. The SELECT operation happens before the UPDATE and DELETE operations. Running the program repeatedly will insert another Rohit record each time. In a real application, use validated user input, appropriate access controls and separate actions for each operation.
Concepts Covered
- CRUD operations
- INSERT query
- SELECT query
- UPDATE query
- DELETE query
- Prepared statements
- Database record in management
Key Takeaways
- INSERT adds new records to a database table.
- SELECT retrieves existing records.
- UPDATE modifies existing database information.
- DELETE removes unwanted records.
- WHERE identifies which records should be updated, deleted or retrieved.
- Prepared statements help protect database queries from SQL injection.
bind_param()attaches values to MySQLi placeholders.fetch_assoc()retrieves database records as associative arrays.ORDER BYsorts retrieved records.- CRUD operations are the foundation of database-driven PHP applications.
Frequently Asked Questions (FAQs)
1. What are PHP CRUD operations?
CRUD stands for Create, Read, Update and Delete. These operations allow PHP applications to add, retrieve, modify and remove database records.
2. Which SQL command is used to insert records?
The INSERT INTO command is used to add new records to a database table.
3. Which SQL command is used to retrieve records?
The SELECT command retrieves information from database tables.
4. How can we update a record in PHP?
Use the SQL UPDATE command with a WHERE condition to modify a particular database record.
5. How can we delete a particular record in PHP?
Use the SQL DELETE FROM command with a WHERE condition to remove a specific record.
6. Why is the WHERE condition important?
The WHERE condition identifies the records that should be affected by a query. Without it, UPDATE and DELETE queries can affect every record in the table.
7. What is a prepared statement in PHP?
A prepared statement separates SQL query structure from the values supplied to it. It helps prevent SQL injection when values are bound correctly.
8. What is the difference between INSERT and UPDATE?
INSERT creates a new record, whereas UPDATE modifies an existing record.
9. What is the difference between DELETE and SELECT?
SELECT retrieves records without removing them, while DELETE permanently removes matching records from the table.
10. Can we perform all CRUD operations using MySQLi?
Yes. MySQLi supports INSERT, SELECT, UPDATE and DELETE operations through SQL queries and prepared statements.
Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.
