PDO and MySQLi in PHP Practice Questions with Solutions

Introduction

PHP provides two popular ways to communicate with MySQL databases: PDO (PHP Data Objects) and MySQLi (MySQL Improved). Both allow you to connect to databases, execute SQL queries, insert records, update information and retrieve data. In this chapter, you will practise both methods and understand when to use each one. PDO and MySQLi in PHP Practice Questions with Solutions to help you to understand the concepts.

Question 1: Connect to MySQL Using MySQLi in PHP

Problem Statement

Create a PHP program that connects to a MySQL database named school_db using MySQLi and displays a success message when the connection is established.

PHP Solution

<?php

$host = "localhost";
$username = "root";
$password = "";
$database = "school_db";

$conn = new mysqli($host, $username, $password, $database);

if ($conn->connect_error) {
    die("Connection failed");
}

echo "MySQLi Connection Successful!";

$conn->close();

?>

Sample Output

MySQLi Connection Successful!

Explanation

  • new mysqli() creates a connection with the MySQL database.
  • $host specifies the database server.
  • $username and $password contain the database login details.
  • $database specifies the database name.
  • connect_error checks whether the connection failed.
  • close() closes the database connection.

Concepts Covered

  • MySQLi connection
  • Database credentials
  • Connection error checking
  • Closing a connection

Question 2: Connect to MySQL Using PDO

Problem Statement

Create a PHP program that connects to the school_db database using PDO and displays a success message.

PHP Solution

<?php

$host = "localhost";
$database = "school_db";
$username = "root";
$password = "";

try {

    $conn = new PDO(
        "mysql:host=$host;dbname=$database;charset=utf8mb4",
        $username,
        $password
    );

    $conn->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

    echo "PDO Connection Successful!";

} catch (PDOException $e) {

    echo "Database connection failed.";

}

$conn = null;

?>

Sample Output

PDO Connection Successful!

Explanation

  • new PDO() creates a database connection.
  • The connection string specifies the database server, database name and character encoding.
  • PDO::ATTR_ERRMODE sets how PDO handles database errors.
  • PDO::ERRMODE_EXCEPTION makes PDO throw an exception when a database operation fails.
  • catch handles the database exception.
  • $conn = null releases the PDO connection.

Concepts Covered

  • PDO connection
  • PDO constructor
  • Exception handling
  • Connection attributes
  • Closing a PDO connection

Question 3: Retrieve All Student Records Using MySQLi

Problem Statement

A school stores student information in a MySQL table named students.

Create a PHP program using MySQLi to retrieve and display all student records.

Table: students

IDNameEmailCourseMarks
1Rahulrahul@gmail.comPHP85
2Priyapriya@gmail.comPython90
3Amitamit@gmail.comJava75
4Nehaneha@gmail.comPHP95
5Karankaran@gmail.comPython80

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("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 records found.";

}

$conn->close();

?>

Sample Output

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 records from the students table.
  • $conn->query() executes the SQL query.
  • num_rows checks how many records were returned.
  • fetch_assoc() retrieves one record at a time as an associative array.
  • htmlspecialchars() safely displays text in HTML.

The while loop continues until all records have been retrieved.

Concepts Covered

  • MySQLi SELECT query
  • Query execution
  • num_rows
  • fetch_assoc()
  • Displaying database records

Question 4: Retrieve All Student Records Using PDO

Problem Statement

Create a PHP program using PDO to retrieve all student records from the students table and display their names, courses and marks.

PHP Solution

<?php

try {

    $conn = new PDO(
        "mysql:host=localhost;dbname=school_db;charset=utf8mb4",
        "root",
        ""
    );

    $conn->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

    $sql = "SELECT * FROM students";

    $stmt = $conn->prepare($sql);

    $stmt->execute();

    $students = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($students as $student) {

        echo "Name: " .
            htmlspecialchars($student["name"]) . "<br>";

        echo "Course: " .
            htmlspecialchars($student["course"]) . "<br>";

        echo "Marks: " . $student["marks"] . "<br><br>";

    }

} catch (PDOException $e) {

    echo "Unable to retrieve student records.";

}

?>

Sample Output

Name: Rahul
Course: PHP
Marks: 85

Name: Priya
Course: Python
Marks: 90

Name: Amit
Course: Java
Marks: 75

Name: Neha
Course: PHP
Marks: 95

Name: Karan
Course: Python
Marks: 80

Explanation

  • prepare() prepares the SQL query.
  • execute() executes the prepared query.
  • fetchAll(PDO::FETCH_ASSOC) retrieves all records as associative arrays.
  • foreach processes each student record.
  • htmlspecialchars() protects HTML output from unsafe characters.

In PDO, you can use fetch() to retrieve one record at a time or fetchAll() to retrieve all matching records.

Concepts Covered

  • PDO SELECT query
  • Prepared statements
  • execute()
  • fetchAll()
  • Associative arrays

Question 5: Insert a Student Record Using MySQLi Prepared Statements

Problem Statement

A school wants to add a new student to its database.

Insert the following information into the students table using MySQLi prepared statements.

NameEmailCourseMarks
Anjalianjali@gmail.comPHP88

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("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

  • ? represents a placeholder for a value.
  • prepare() prepares the SQL statement.
  • bind_param() attaches PHP variables to the placeholders.
  • "sssi" specifies the data types of the four values:
    • s = String
    • s = String
    • s = String
    • i = Integer
  • execute() runs the prepared statement.

Prepared statements help protect queries from SQL injection when handling user-supplied values.

Concepts Covered

  • MySQLi INSERT query
  • Prepared statements
  • bind_param()
  • Data type identifiers
  • SQL injection prevention

Question 6: Insert a Student Record Using PDO Prepared Statements

Problem Statement

Insert a new student record into the students table using PDO prepared statements.

NameEmailCourseMarks
Mohitmohit@gmail.comPython92

PHP Solution

<?php

try {

    $conn = new PDO(
        "mysql:host=localhost;dbname=school_db;charset=utf8mb4",
        "root",
        ""
    );

    $conn->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

    $sql = "INSERT INTO students (name, email, course, marks)
            VALUES (:name, :email, :course, :marks)";

    $stmt = $conn->prepare($sql);

    $stmt->execute([
        ":name" => "Mohit",
        ":email" => "mohit@gmail.com",
        ":course" => "Python",
        ":marks" => 92
    ]);

    echo "Student record inserted successfully!";

} catch (PDOException $e) {

    echo "Unable to insert student record.";

}

?>

Sample Output

Student record inserted successfully!

Explanation

  • prepare() prepares the INSERT query.
  • Named placeholders such as :name and :email represent values.
  • execute() receives an associative array containing the values for those placeholders.
  • PDO binds the supplied values to the prepared statement.
  • The database inserts the new student record when execution succeeds.

Unlike MySQLi’s bind_param(), PDO allows you to pass values directly through an array in execute().

Concepts Covered

  • PDO INSERT query
  • Named placeholders
  • Prepared statements
  • Passing values through execute()
  • Secure database operations

Question 7: Update Student Marks Using MySQLi

Problem Statement

A teacher wants to update Amit’s marks from 75 to 85.

Create a PHP program using MySQLi prepared statements to update the student’s marks.

PHP Solution

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection failed");
}

$sql = "UPDATE students SET marks = ? WHERE name = ?";

$stmt = $conn->prepare($sql);

$marks = 85;
$name = "Amit";

$stmt->bind_param("is", $marks, $name);

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 modifies an existing database record.
  • SET marks = ? identifies the field that needs updating.
  • WHERE name = ? identifies the student whose marks should change.
  • bind_param("is", ...) binds an integer followed by a string.
  • affected_rows reports how many rows were changed.

If Amit already has 85 marks, the query may execute successfully but report zero changed rows.

Concepts Covered

  • MySQLi UPDATE query
  • Prepared statements
  • WHERE condition
  • affected_rows
  • Updating database records

Question 8: Update Student Marks Using PDO

Problem Statement

The school wants to update Priya’s marks from 90 to 95 using PDO.

Create a PHP program that updates her marks using a prepared statement.

PHP Solution

<?php

try {

    $conn = new PDO(
        "mysql:host=localhost;dbname=school_db;charset=utf8mb4",
        "root",
        ""
    );

    $conn->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

    $sql = "UPDATE students
            SET marks = :marks
            WHERE name = :name";

    $stmt = $conn->prepare($sql);

    $stmt->execute([
        ":marks" => 95,
        ":name" => "Priya"
    ]);

    if ($stmt->rowCount() > 0) {

        echo "Student marks updated successfully!";

    } else {

        echo "No record was changed.";

    }

} catch (PDOException $e) {

    echo "Unable to update student marks.";

}

?>

Sample Output

Student marks updated successfully!

Explanation

  • UPDATE changes the existing marks.
  • :marks and :name are named placeholders.
  • prepare() prepares the query.
  • execute() supplies the new marks and student name.
  • rowCount() reports the number of affected rows for this UPDATE operation.

Remember that rowCount() reports changed rows for this type of query. If the new value is the same as the old value, it may return zero.

Concepts Covered

  • PDO UPDATE query
  • Named placeholders
  • Prepared statements
  • rowCount()
  • Updating records

Question 9: Delete a Student Record Using MySQLi and PDO

Problem Statement

A school wants to remove a student named Karan from the students table.

Write two PHP solutions:

  1. Delete the record using MySQLi in PHP.
  2. Delete the record using PDO.

PHP Solution

Solution A: Using MySQLi

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection failed");
}

$sql = "DELETE FROM students WHERE name = ?";

$stmt = $conn->prepare($sql);

$name = "Karan";

$stmt->bind_param("s", $name);

if ($stmt->execute()) {

    if ($stmt->affected_rows > 0) {
        echo "Student deleted using MySQLi!";
    } else {
        echo "Student record not found.";
    }

} else {

    echo "Unable to delete student.";

}

$stmt->close();
$conn->close();

?>

Solution B: Using PDO

<?php

try {

    $conn = new PDO(
        "mysql:host=localhost;dbname=school_db;charset=utf8mb4",
        "root",
        ""
    );

    $conn->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

    $sql = "DELETE FROM students WHERE name = :name";

    $stmt = $conn->prepare($sql);

    $stmt->execute([
        ":name" => "Karan"
    ]);

    if ($stmt->rowCount() > 0) {

        echo "Student deleted using PDO!";

    } else {

        echo "Student record not found.";

    }

} catch (PDOException $e) {

    echo "Unable to delete student.";

}

?>

Sample Output

MySQLi:

Student deleted using MySQLi!

PDO:

Student deleted using PDO!

Explanation

Both solutions perform the same database operation.

  • DELETE FROM students specifies the table from which records will be deleted.
  • WHERE name = ? or WHERE name = :name identifies the student.
  • MySQLi uses bind_param() to attach the student name.
  • PDO passes the student name through execute().
  • affected_rows and rowCount() help determine whether a record was deleted.

Important: Always use a WHERE condition when you intend to delete a specific record. Without it, the query can delete every record in the table.

Concepts Covered

  • DELETE query
  • MySQLi in PHP prepared statements
  • PDO prepared statements
  • affected_rows
  • rowCount()
  • Safe record deletion

Question 10: Compare PDO and MySQLi in a Student Management Program

Problem Statement

A school wants to display student information using both PDO and MySQLi.

Create two separate PHP programs that retrieve student names, courses and marks from the students table.

The purpose is to understand how the same database task can be completed using both database extensions.

PHP Solution

Solution A: Using MySQLi

<?php

$conn = new mysqli("localhost", "root", "", "school_db");

if ($conn->connect_error) {
    die("Connection failed");
}

$sql = "SELECT name, course, marks FROM students";

$result = $conn->query($sql);

echo "Student Records Using MySQLi\n\n";

while ($row = $result->fetch_assoc()) {

    echo "Name: " . $row["name"] . "\n";
    echo "Course: " . $row["course"] . "\n";
    echo "Marks: " . $row["marks"] . "\n\n";

}

$conn->close();

?>

Solution B: Using PDO

<?php

try {

    $conn = new PDO(
        "mysql:host=localhost;dbname=school_db;charset=utf8mb4",
        "root",
        ""
    );

    $conn->setAttribute(
        PDO::ATTR_ERRMODE,
        PDO::ERRMODE_EXCEPTION
    );

    $sql = "SELECT name, course, marks FROM students";

    $stmt = $conn->prepare($sql);

    $stmt->execute();

    echo "Student Records Using PDO\n\n";

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        echo "Name: " . $row["name"] . "\n";
        echo "Course: " . $row["course"] . "\n";
        echo "Marks: " . $row["marks"] . "\n\n";

    }

} catch (PDOException $e) {

    echo "Unable to retrieve student records.";

}

?>

Sample Output

The exact records depend on which previous insert, update and delete exercises have been executed. For the original five records, the MySQLi version displays:

Student Records Using MySQLi

Name: Rahul
Course: PHP
Marks: 85

Name: Priya
Course: Python
Marks: 90

Name: Amit
Course: Java
Marks: 75

Name: Neha
Course: PHP
Marks: 95

Name: Karan
Course: Python
Marks: 80

The PDO version displays the same records, with the heading:

Student Records Using PDO

Explanation

Both programs retrieve the same information, but their connection and query-handling methods are different.

MySQLi:

  • Uses the mysqli in PHP class.
  • Executes a simple query using query().
  • Retrieves records using fetch_assoc().

PDO:

  • Uses the PDO class.
  • Prepares and executes the query using prepare() and execute().
  • Retrieves records using fetch(PDO::FETCH_ASSOC).

Both extensions support prepared statements and can be used to build database-driven PHP applications.

Concepts Covered

  • PDO and MySQLi comparison
  • Database connection methods
  • SELECT queries
  • Fetching records
  • Database programming

PDO vs MySQLi: Key Differences

PDO and MySQLi are both valid choices for PHP database programming. The main difference is the range of database systems they support and the programming interfaces they provide.

FeaturePDOMySQLi
Full FormPHP Data ObjectsMySQL Improved
Database SupportMultiple database systems through available PDO driversMySQL only
Programming StyleObject-orientedObject-oriented and procedural
Prepared StatementsYesYes
Named ParametersYesNo
Question Mark PlaceholdersYesYes
Transaction SupportYesYes
Stored ProceduresSupported, depending on driverSupported
Error HandlingExceptions and error informationError information and exceptions

Which One Should You Learn?

Choose PDO when:

  • You may work with different database systems.
  • You prefer named placeholders.
  • You want a consistent database interface across supported database drivers.

Choose MySQLi when:

  • Your application uses MySQL or MariaDB.
  • You want to use MySQL-specific features.
  • You prefer its object-oriented or procedural interface.

Both are suitable for professional PHP applications. The right choice depends on your project requirements.

Key Takeaways

  • PDO and MySQLi are two ways to connect PHP with MySQL databases.
  • PDO supports multiple database systems through drivers.
  • MySQLi is specifically designed for MySQL.
  • Both support prepared statements to help prevent SQL injection.
  • MySQLi supports object-oriented and procedural programming.
  • PDO uses named placeholders as well as question mark placeholders.
  • MySQLi uses bind_param() to bind values to placeholders.
  • PDO can pass values through an array in execute().
  • Both can perform INSERT, SELECT, UPDATE and DELETE operations.
  • Always validate input and safely escape output when displaying database information.

Frequently Asked Questions (FAQs)

1. What is PDO in PHP?

PDO stands for PHP Data Objects. It provides a consistent interface for connecting and working with different database systems through available drivers.

2. What is MySQLi in PHP?

MySQLi in PHP stands for MySQL Improved. It is a PHP extension used to connect and communicate with MySQL databases.

3. What is the main difference between PDO and MySQLi?

PDO supports multiple database systems, while MySQLi supports MySQL databases only. Both support prepared statements and database operations.

4. Which is better: PDO or MySQLi?

Neither is universally better. PDO is useful when database portability matters, while MySQLi is a good choice for applications specifically built around MySQL.

5. Can PDO and MySQLi in PHP prevent SQL injection?

Both support prepared statements, which help prevent SQL injection when values are bound correctly. They do not replace input validation or safe output handling.

6. Can we use both PDO and MySQLi in the same PHP project?

Yes, technically you can use both in one project. However, it is usually easier to maintain a project by choosing one database interface and using it consistently.

7. What is a prepared statement in PHP?

A prepared statement is an SQL query prepared separately from the values supplied to it. It allows PHP to bind values safely instead of directly joining user input into SQL query strings.

8. What is the difference between fetch() and fetchAll() in PDO?

fetch() retrieves one record at a time, while fetchAll() retrieves all remaining matching records as an array.

9. What is the difference between bind_param() and execute()?

MySQLi in PHP uses bind_param() to attach PHP variables to placeholders before executing a statement. PDO can receive parameter values directly through the execute() method.

10. Do we need to install PDO and MySQLi separately?

Both are PHP extensions. They are commonly included in PHP installations, but the required extensions and PDO MySQL driver must be enabled in your PHP configuration.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Scroll to Top