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.$hostspecifies the database server.$usernameand$passwordcontain the database login details.$databasespecifies the database name.connect_errorchecks 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_ERRMODEsets how PDO handles database errors.PDO::ERRMODE_EXCEPTIONmakes PDO throw an exception when a database operation fails.catchhandles the database exception.$conn = nullreleases 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
| ID | Name | Course | Marks | |
|---|---|---|---|---|
| 1 | Rahul | rahul@gmail.com | PHP | 85 |
| 2 | Priya | priya@gmail.com | Python | 90 |
| 3 | Amit | amit@gmail.com | Java | 75 |
| 4 | Neha | neha@gmail.com | PHP | 95 |
| 5 | Karan | karan@gmail.com | Python | 80 |
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 studentsretrieves all records from the students table.$conn->query()executes the SQL query.num_rowschecks 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_rowsfetch_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.foreachprocesses 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.
| Name | Course | Marks | |
|---|---|---|---|
| Anjali | anjali@gmail.com | PHP | 88 |
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= Strings= Strings= Stringi= 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.
| Name | Course | Marks | |
|---|---|---|---|
| Mohit | mohit@gmail.com | Python | 92 |
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
:nameand:emailrepresent 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
UPDATEmodifies 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_rowsreports 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
UPDATEchanges the existing marks.:marksand:nameare 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:
- Delete the record using MySQLi in PHP.
- 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 studentsspecifies the table from which records will be deleted.WHERE name = ?orWHERE name = :nameidentifies the student.- MySQLi uses
bind_param()to attach the student name. - PDO passes the student name through
execute(). affected_rowsandrowCount()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_rowsrowCount()- 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
mysqliin PHP class. - Executes a simple query using
query(). - Retrieves records using
fetch_assoc().
PDO:
- Uses the
PDOclass. - Prepares and executes the query using
prepare()andexecute(). - 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.
| Feature | PDO | MySQLi |
|---|---|---|
| Full Form | PHP Data Objects | MySQL Improved |
| Database Support | Multiple database systems through available PDO drivers | MySQL only |
| Programming Style | Object-oriented | Object-oriented and procedural |
| Prepared Statements | Yes | Yes |
| Named Parameters | Yes | No |
| Question Mark Placeholders | Yes | Yes |
| Transaction Support | Yes | Yes |
| Stored Procedures | Supported, depending on driver | Supported |
| Error Handling | Exceptions and error information | Error 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.
