Introduction
PHP and MySQL work together to build dynamic websites and database-driven applications. PHP handles application logic, while MySQL stores information such as student records, employee details, and product data. In this chapter, you will practise connecting PHP to MySQL, executing queries, retrieving records, and handling database errors using MySQLi. PHP and MySQL Database Connection practice questions with solutions to help you to understand the concepts.
Before Starting: Database Setup
For these practice questions, you can use XAMPP, WAMP, or another PHP and MySQL environment.
Step 1: Start the required services
- Start Apache.
- Start MySQL.
- Open phpMyAdmin in your browser.
Step 2: Create a database
Create a database named:
school_db
Step 3: Create a students table
Run this SQL query in phpMyAdmin:
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL,
course VARCHAR(100) NOT NULL,
marks INT NOT NULL
);
Step 4: 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);
You will use this database and table throughout the practice questions.
Question 1: Connect PHP to MySQL Using MySQLi
Problem Statement
Create a PHP program that connects to a MySQL database named school_db using MySQLi. Display 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: " . $conn->connect_error);
}
echo "Database Connected Successfully!";
$conn->close();
?>
Sample Output
Database Connected Successfully!
Explanation
The mysqli class creates a connection between PHP and MySQL.
The connection requires four important values:
$host: MySQL server address. In a typical local XAMPP setup, it islocalhost.$username: MySQL username. The default XAMPP username is commonlyroot.$password: MySQL password. It is commonly blank in a default local XAMPP installation.$database: Name of the database you want to access.
The following statement creates the connection:
$conn = new mysqli($host, $username, $password, $database);
The connect_error property checks whether the connection failed.
Finally, close() closes the database connection.
Concepts Covered
- PHP and MySQL connection
- MySQLi
- Database credentials
- Connection error handling
- Closing database connections
Question 2: Connect PHP to MySQL Using PDO
Problem Statement
Create a PHP program that connects to the school_db database using PDO. Display a success message if the connection is established and handle connection errors using try-catch.
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 "Database Connected Successfully!";
} catch (PDOException $e) {
echo "Connection Failed: " . $e->getMessage();
}
?>
Sample Output
Database Connected Successfully!
Explanation
PDO stands for PHP Data Objects. It provides a consistent way to connect PHP applications to different database systems.
The following statement creates a PDO connection:
$conn = new PDO(
"mysql:host=$host;dbname=$database;charset=utf8mb4",
$username,
$password
);
The connection string specifies the database driver, host, database name, and character encoding.
The try block contains the connection code. If a database error occurs, the catch block handles the exception.
The following setting enables exception-based error reporting:
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
Concepts Covered
- PDO connection
- Database connection string
- Exception handling
try-catch- MySQL database connectivity
Question 3: Display All Records from a MySQL Database
Problem Statement
Create a PHP program that connects to school_db, retrieves all records from the students table, and displays student names, email addresses, courses, and marks.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Connection Failed: " . $conn->connect_error);
}
$sql = "SELECT * FROM students";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Email: " . htmlspecialchars($row["email"]) . "<br>";
echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
echo "Marks: " . (int) $row["marks"] . "<br><br>";
}
} else {
echo "No Records Found.";
}
$conn->close();
?>
Sample Output
Name: Rahul
Email: rahul@gmail.com
Course: PHP
Marks: 85
Name: Priya
Email: priya@gmail.com
Course: Python
Marks: 90
Name: Amit
Email: amit@gmail.com
Course: Java
Marks: 75
Name: Neha
Email: neha@gmail.com
Course: PHP
Marks: 95
Name: Karan
Email: karan@gmail.com
Course: Python
Marks: 80
Explanation
The SQL query retrieves all records from the students table:
SELECT * FROM students;
The query() method executes the SQL statement.
The num_rows property checks how many records were returned.
The fetch_assoc() method retrieves one record at a time as an associative array.
For example:
$row["name"]
retrieves the student’s name from the current record.
The htmlspecialchars() function protects HTML output by escaping special characters. This is important when displaying database values on a webpage.
Concepts Covered
- SELECT query
- Retrieving database records
query()methodfetch_assoc()methodnum_rowsproperty- Safe HTML output
Question 4: Display a Specific Student Record Using an ID
Problem Statement
Create a PHP program that retrieves the details of a student whose ID is 2 from the students table. Display the student’s name, email, course, and marks.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Connection Failed: " . $conn->connect_error);
}
$studentId = 2;
$stmt = $conn->prepare(
"SELECT id, name, email, course, marks FROM students WHERE id = ?"
);
$stmt->bind_param("i", $studentId);
$stmt->execute();
$result = $stmt->get_result();
if ($row = $result->fetch_assoc()) {
echo "Student ID: " . (int) $row["id"] . "<br>";
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Email: " . htmlspecialchars($row["email"]) . "<br>";
echo "Course: " . htmlspecialchars($row["course"]) . "<br>";
echo "Marks: " . (int) $row["marks"];
} else {
echo "Student Not Found.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Student ID: 2
Name: Priya
Email: priya@gmail.com
Course: Python
Marks: 90
Explanation
The program retrieves a particular student using the ID.
The SQL query uses a question mark as a placeholder:
SELECT id, name, email, course, marks FROM students WHERE id = ?
The prepare() method creates a prepared statement.
The following statement binds the student ID to the placeholder:
$stmt->bind_param("i", $studentId);
Here, i means the parameter is an integer.
Prepared statements help protect applications against SQL injection by separating SQL instructions from supplied values.
Concepts Covered
- SELECT with WHERE
- Prepared statements
prepare()methodbind_param()method- Fetching a single record
- SQL injection prevention
Question 5: Insert a New Student Record into MySQL
Problem Statement
Create a PHP program that inserts a new student into the students table with the following details:
| Field | Value |
|---|---|
| Name | Anjali |
| anjali@gmail.com | |
| Course | Java |
| Marks | 88 |
Display a success message after inserting the record.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Connection Failed: " . $conn->connect_error);
}
$name = "Anjali";
$email = "anjali@gmail.com";
$course = "Java";
$marks = 88;
$stmt = $conn->prepare(
"INSERT INTO students (name, email, course, marks)
VALUES (?, ?, ?, ?)"
);
$stmt->bind_param("sssi", $name, $email, $course, $marks);
if ($stmt->execute()) {
echo "Student Record Inserted Successfully!";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>
Sample Output
Student Record Inserted Successfully!
Explanation
The INSERT INTO query adds a new record to the students table.
The prepare() method creates a prepared statement with four placeholders.
The bind_param() method connects the PHP variables to those placeholders.
The type string is:
"sssi"
It represents:
s: String for student name.s: String for email.s: String for course.i: Integer for marks.
The execute() method runs the prepared statement.
Concepts Covered
- INSERT query
- Adding database records
- Prepared statements
- Parameter binding
execute()method
Question 6: Update an Existing Student Record
Problem Statement
Create a PHP program that updates the marks of the student whose ID is 3.
Change the student’s marks from 75 to 82.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Connection Failed: " . $conn->connect_error);
}
$studentId = 3;
$newMarks = 82;
$stmt = $conn->prepare(
"UPDATE students SET marks = ? WHERE id = ?"
);
$stmt->bind_param("ii", $newMarks, $studentId);
if ($stmt->execute()) {
if ($stmt->affected_rows > 0) {
echo "Student Record Updated Successfully!";
} else {
echo "No Record Changed.";
}
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>
Sample Output
Student Record Updated Successfully!
Explanation
The UPDATE query modifies an existing database record.
UPDATE students SET marks = ? WHERE id = ?
It updates the marks of the student whose ID matches the supplied value.
The bind_param("ii", ...) statement binds two integer values.
The affected_rows property tells us how many rows were changed by the statement.
If the supplied marks are already the same as the stored marks, or the ID does not match a record, no rows may be changed.
Concepts Covered
- UPDATE query
- Modifying database records
- WHERE condition
- Prepared statements
affected_rowsproperty
Question 7: Delete a Student Record from MySQL
Problem Statement
Create a PHP program that deletes the student record whose ID is 5 from the students table. Display a confirmation message when the record is deleted.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Connection Failed: " . $conn->connect_error);
}
$studentId = 5;
$stmt = $conn->prepare(
"DELETE FROM students WHERE id = ?"
);
$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 "Error: " . $stmt->error;
}
$stmt->close();
$conn->close();
?>
Sample Output
Student Record Deleted Successfully!
Explanation
The DELETE query removes a record from the database.
DELETE FROM students WHERE id = ?
The WHERE condition ensures that only the student with the matching ID is deleted.
The bind_param() method safely binds the student ID.
The affected_rows property checks whether a record was actually deleted.
Important: Always use a suitable WHERE condition when deleting records. Without it, a DELETE query can remove every record from the table.
Concepts Covered
- DELETE query
- Removing database records
- WHERE condition
- Prepared statements
affected_rowsproperty
Question 8: Count Total Students in MySQL
Problem Statement
Create a PHP program that counts the total number of student records in the students table and displays the result.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Connection Failed: " . $conn->connect_error);
}
$sql = "SELECT COUNT(*) AS total_students FROM students";
$result = $conn->query($sql);
if ($result) {
$row = $result->fetch_assoc();
echo "Total Students: " . $row["total_students"];
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
Sample Output
Total Students: 4
Explanation
The SQL COUNT() function counts the number of records in a table.
SELECT COUNT(*) AS total_students FROM students;
The alias total_students gives a readable name to the calculated value.
The fetch_assoc() method retrieves the result as an associative array.
The output depends on which records currently exist in your database. If you have completed the previous insert and delete questions in order, the original five records become four after Question 7, and Question 8 displays four.
Concepts Covered
- SQL COUNT function
- Aggregate queries
- Column aliases
- Fetching query results
Question 9: Search Students by Course Using Prepared Statements
Problem Statement
Create a PHP program that displays all students enrolled in the PHP course.
Use a prepared statement to search for students whose course is PHP.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Connection Failed: " . $conn->connect_error);
}
$course = "PHP";
$stmt = $conn->prepare(
"SELECT id, name, email, marks FROM students WHERE course = ?"
);
$stmt->bind_param("s", $course);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "Name: " . htmlspecialchars($row["name"]) . "<br>";
echo "Email: " . htmlspecialchars($row["email"]) . "<br>";
echo "Marks: " . (int) $row["marks"] . "<br><br>";
}
} else {
echo "No Students Found in PHP Course.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Name: Rahul
Email: rahul@gmail.com
Marks: 85
Name: Neha
Email: neha@gmail.com
Marks: 95
Explanation
The program searches for students based on their course.
The SQL query uses a placeholder:
SELECT id, name, email, marks FROM students WHERE course = ?
The following statement binds the course name:
$stmt->bind_param("s", $course);
The letter s indicates that the parameter is a string.
The while loop retrieves and displays every matching student record.
If you have changed or deleted records while completing earlier questions, your output may be different.
Concepts Covered
- SELECT with WHERE
- Searching database records
- Prepared statements
- Fetching multiple records
- Displaying database results
Question 10: Build a Student Management System Using PHP and MySQL
Problem Statement
Create a simple Student Management System that performs the following operations:
- Connect to the
school_dbdatabase. - Insert a new student record.
- Retrieve all student records.
- Display student details in an HTML table.
Use the following student information:
| Field | Value |
|---|---|
| Name | Mohit |
| mohit@gmail.com | |
| Course | Python |
| Marks | 92 |
PHP Solution
Save the following code as student_management.php.
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Connection Failed: " . $conn->connect_error);
}
// Insert Student Record
$name = "Mohit";
$email = "mohit@gmail.com";
$course = "Python";
$marks = 92;
$stmt = $conn->prepare(
"INSERT INTO students (name, email, course, marks)
VALUES (?, ?, ?, ?)"
);
$stmt->bind_param("sssi", $name, $email, $course, $marks);
if ($stmt->execute()) {
echo "Student Added Successfully!<br><br>";
} else {
echo "Error: " . $stmt->error;
}
$stmt->close();
// Retrieve Student Records
$sql = "SELECT id, name, email, course, marks FROM students";
$result = $conn->query($sql);
?>
<h2>Student Management System</h2>
<table border="1" cellpadding="8" cellspacing="0">
<tr>
<th>ID</th>
<th>Name</th>
<th>Email</th>
<th>Course</th>
<th>Marks</th>
</tr>
<?php
if ($result && $result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
?>
<tr>
<td><?php echo (int) $row["id"]; ?></td>
<td><?php echo htmlspecialchars($row["name"]); ?></td>
<td><?php echo htmlspecialchars($row["email"]); ?></td>
<td><?php echo htmlspecialchars($row["course"]); ?></td>
<td><?php echo (int) $row["marks"]; ?></td>
</tr>
<?php
}
} else {
echo "<tr><td colspan='5'>No Records Found.</td></tr>";
}
?>
</table>
<?php
$conn->close();
?>
Sample Output
<text color=”secondary” size=”xs”>Illustrative output after running the program with the original sample records and completing the earlier insert and delete exercises.</text>
Student Added Successfully!
Student Management System
ID Name Email Course Marks
1 Rahul rahul@gmail.com PHP 85
2 Priya priya@gmail.com Python 90
3 Amit amit@gmail.com Java 82
4 Neha neha@gmail.com PHP 95
6 Mohit mohit@gmail.com Python 92
Explanation
This project combines the main database operations covered in this chapter.
1. Database connection
The new mysqli() statement connects PHP to MySQL.
2. Insert student record
A prepared statement inserts Mohit’s information into the students table.
3. Retrieve records
The SELECT query retrieves all student records.
4. Display records
The while loop processes each record and displays it inside an HTML table.
5. Protect displayed values
The htmlspecialchars() function escapes text before displaying it in HTML.
The program demonstrates how PHP can communicate with MySQL and display database information on a webpage.
Important: This is a learning example. If you refresh the page, Mohit’s record will be inserted again. A production application should use a form submission flow with validation and prevent accidental duplicate submissions.
Concepts Covered
- PHP and MySQL connection
- INSERT query
- SELECT query
- Prepared statements
- Fetching database records
- HTML table
- Safe output using
htmlspecialchars() - Basic student management system
Key Takeaways
- PHP can connect to MySQL using MySQLi or PDO.
- MySQLi and PDO both support prepared statements.
- Use
SELECTto retrieve database records. - Use
INSERTto add new records. - Use
UPDATEto modify existing records. - Use
DELETEto remove records. - Use
WHEREto target specific records. - Use
fetch_assoc()to retrieve records as associative arrays. - Use
COUNT()to count database records. - Prepared statements help protect against SQL injection.
- Use
htmlspecialchars()when displaying database values in HTML. - Always handle connection errors and avoid exposing sensitive database details in production.
Frequently Asked Questions (FAQs)
1. What is PHP and MySQL database connectivity?
PHP and MySQL database connectivity allows a PHP application to communicate with a MySQL database. PHP can insert, retrieve, update, and delete stored information.
2. How can we connect PHP to MySQL?
You can connect PHP to MySQL using MySQLi or PDO. Both provide methods for creating database connections and executing SQL queries.
3. What is the difference between MySQLi and PDO?
MySQLi is designed specifically for MySQL databases, while PDO supports multiple database systems through different drivers. Both support prepared statements.
4. What is the use of new mysqli() in PHP?
The new mysqli() statement creates a connection between a PHP application and a MySQL database.
5. What is a prepared statement in PHP?
A prepared statement separates an SQL query from the values supplied to it. It helps prevent SQL injection when parameters are bound correctly.
6. What is the use of bind_param() in PHP?
The bind_param() method binds PHP variables to placeholders in a prepared MySQLi statement.
For example:
$stmt->bind_param("si", $name, $marks);
Here, s represents a string and i represents an integer.
7. What is the use of fetch_assoc() in PHP?
The fetch_assoc() method retrieves a database result row as an associative array. Column names are used as array keys.
8. How can we insert data into MySQL using PHP?
You can use an SQL INSERT INTO statement with a prepared statement and execute it using the execute() method.
9. How can we update and delete records using PHP?
Use the SQL UPDATE statement to modify existing records and the DELETE statement to remove records. Prepared statements should be used when values come from application input.
10. How can we prevent SQL injection in PHP?
Use prepared statements with bound parameters instead of directly joining user input into SQL queries. Also validate input and use a database account with only the permissions the application requires.
Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.
