Insert, Select, Update and Delete Records in PHP Practice Questions with Solutions

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.

NameEmailCourseMarks
Anjalianjali@gmail.comPHP88

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 INTO adds 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.
  • s represents a string, and i represents 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.

NameEmailCourseMarks
Mohitmohit@gmail.comPython92
Poojapooja@gmail.comPHP87
Arjunarjun@gmail.comJava78

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.

  • $students stores three student records in a multidimensional array.
  • prepare() creates the SQL statement once.
  • bind_param() connects the variables to the placeholders.
  • foreach processes 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
  • foreach loop
  • 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 students retrieves all columns and records.
  • $conn->query() executes the SELECT query.
  • num_rows checks how many records were returned.
  • fetch_assoc() retrieves one record in PHP as an associative array.
  • while repeats until all records have been retrieved.
  • htmlspecialchars() safely displays text in HTML.

Concepts Covered

  • SELECT query
  • Retrieving database record in PHP
  • query()
  • num_rows
  • fetch_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 students identifies 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_rows reports 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.

FieldOld ValueNew Value
Emailpriya@gmail.compriya.sharma@gmail.com
CoursePythonData 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 students removes 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_rows checks 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_rows checks 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 marks sorts the records using the marks column.
  • DESC arranges 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:

  1. Insert a new student.
  2. Display all students.
  3. Update a student’s marks.
  4. 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:

OperationSQL CommandPurpose
CreateINSERTAdd new records
ReadSELECTRetrieve records
UpdateUPDATEModify existing records
DeleteDELETERemove 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 BY sorts 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.

Scroll to Top