Introduction
Search and pagination make database-driven PHP applications easier to use. Search allows users to find specific records, while pagination divides large results into smaller pages. In this chapter, you will practise building database search features, displaying records page by page, and creating practical applications using PHP, MySQLi, and PDO. Pagination and Database-Driven Applications 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) NOT NULL,
email VARCHAR(150) NOT NULL,
course VARCHAR(100) NOT NULL,
marks INT NOT NULL
);
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),
('Anjali', 'anjali@gmail.com', 'Java', 88),
('Mohit', 'mohit@gmail.com', 'PHP', 78),
('Pooja', 'pooja@gmail.com', 'Python', 92),
('Rohit', 'rohit@gmail.com', 'Java', 70),
('Simran', 'simran@gmail.com', 'PHP', 89);
Question 1: Display All Student Records from MySQL Database
Problem Statement
Create a PHP program that connects to the MySQL database and displays all student records stored in the students table.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$sql = "SELECT * FROM students ORDER BY id ASC";
$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><hr>";
}
} else {
echo "No student 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
The program displays all 10 records. The output above shows the first three.
Explanation
new mysqli()connects PHP to the MySQL database.SELECT * FROM studentsretrieves all student records.ORDER BY id ASCdisplays records in ascending ID order.fetch_assoc()retrieves one record at a time as an associative array.htmlspecialchars()safely displays text from the database.num_rowschecks whether any records were returned.
Concepts Covered
- MySQL database connection
- SELECT query
- Fetching database records
- Associative arrays
- Safe database output
Question 2: Search Students by Name Using MySQLi
Problem Statement
Create a PHP program that searches for a student by name. If the user enters Rahul, the program should display the matching student record.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$searchName = "Rahul";
$sql = "SELECT * FROM students WHERE name = ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("s", $searchName);
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "Student 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 Name: Rahul
Email: rahul@gmail.com
Course: PHP
Marks: 85
Explanation
$searchNamestores the name to search for.WHERE name = ?searches for an exact name match.prepare()creates a prepared SQL statement.bind_param("s", $searchName)supplies the search value safely.execute()runs the query.get_result()retrieves the matching records. It requires the MySQL Native Driver (mysqlnd).
Concepts Covered
- Database search
- WHERE condition
- MySQLi prepared statements
- Parameter binding
- Exact name matching
Question 3: Search Students Using Partial Name Matching
Problem Statement
Create a PHP program that searches student names using partial text. For example, if the search value is Rah, the program should find Rahul.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$searchName = "Rah";
$sql = "SELECT * FROM students WHERE name LIKE ?";
$stmt = $conn->prepare($sql);
$searchValue = "%" . $searchName . "%";
$stmt->bind_param("s", $searchValue);
$stmt->execute();
$result = $stmt->get_result();
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><hr>";
}
} else {
echo "No matching students found.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Name: Rahul
Course: PHP
Marks: 85
Explanation
LIKEsearches for records matching a text pattern.%is a wildcard that matches zero or more characters.%Rah%finds names containingRahanywhere.- Prepared statements safely pass the search value to SQL.
For example:
| Search Pattern | Matching Example |
|---|---|
Rah% | Rahul |
%iya | Priya |
%oh% | Mohit, Rohit |
Concepts Covered
- Partial text search
- SQL LIKE operator
- Wildcard characters
- Prepared statements
- Dynamic search values
Question 4: Search Students by Course
Problem Statement
Create a PHP program that searches students according to their course. If the selected course is PHP, display all students enrolled in PHP.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$course = "PHP";
$sql = "SELECT * FROM students WHERE course = ?";
$stmt = $conn->prepare($sql);
$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 "Course: " . htmlspecialchars($row['course']) . "<br>";
echo "Marks: " . $row['marks'] . "<br><hr>";
}
} else {
echo "No students found in this course.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Name: Rahul
Course: PHP
Marks: 85
Name: Neha
Course: PHP
Marks: 95
Name: Mohit
Course: PHP
Marks: 78
Name: Simran
Course: PHP
Marks: 89
Explanation
$coursestores the course name to search for.WHERE course = ?retrieves records belonging to that course.bind_param()safely passes the course name.fetch_assoc()reads each matching student record.
Concepts Covered
- Course-based database search
- Filtering records
- WHERE condition
- Prepared statements
- Multiple matching records
Question 5: Search Students Using Multiple Conditions
Problem Statement
Create a PHP program that searches students using two conditions: course name and minimum marks. Display students who belong to the PHP course and have marks greater than 80.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$course = "PHP";
$minimumMarks = 80;
$sql = "SELECT * FROM students
WHERE course = ? AND marks > ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("si", $course, $minimumMarks);
$stmt->execute();
$result = $stmt->get_result();
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><hr>";
}
} else {
echo "No matching students found.";
}
$stmt->close();
$conn->close();
?>
Sample Output
Name: Rahul
Course: PHP
Marks: 85
Name: Neha
Course: PHP
Marks: 95
Name: Simran
Course: PHP
Marks: 89
Explanation
$coursestores the required course.$minimumMarksstores the minimum marks.ANDrequires both conditions to be true.marks > ?selects students with marks strictly greater than 80.bind_param("si", ...)usessfor string andifor integer.
Concepts Covered
- Multiple search conditions
- SQL AND operator
- Numeric filtering
- MySQLi parameter binding
- Conditional database queries
Question 6: Display Database Records Using Pagination
Problem Statement
A database contains many student records. Display only 3 records per page instead of showing every record together.
Use the LIMIT and OFFSET clauses to retrieve the required records.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$recordsPerPage = 3;
$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;
if ($page < 1) {
$page = 1;
}
$offset = ($page - 1) * $recordsPerPage;
$sql = "SELECT * FROM students
ORDER BY id ASC
LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $recordsPerPage, $offset);
$stmt->execute();
$result = $stmt->get_result();
echo "<h3>Student Records - Page $page</h3>";
while ($row = $result->fetch_assoc()) {
echo "Name: " . htmlspecialchars($row['name']) . "<br>";
echo "Course: " . htmlspecialchars($row['course']) . "<br>";
echo "Marks: " . $row['marks'] . "<br><hr>";
}
echo '<a href="?page=' . max(1, $page - 1) . '">Previous</a> ';
echo '<a href="?page=' . ($page + 1) . '">Next</a>';
$stmt->close();
$conn->close();
?>
Sample Output
Page 1:
Student Records - Page 1
Name: Rahul
Course: PHP
Marks: 85
Name: Priya
Course: Python
Marks: 90
Name: Amit
Course: Java
Marks: 75
Previous Next
Page 2:
Student Records - Page 2
Name: Neha
Course: PHP
Marks: 95
Name: Karan
Course: Python
Marks: 80
Name: Anjali
Course: Java
Marks: 88
Previous Next
Explanation
Pagination means dividing a large collection of records into smaller groups called pages.
For example, if a database contains 10 records and we display 3 records per page, we get 4 pages.
The important calculation is:
$offset = ($page - 1) * $recordsPerPage;
| Page | Records Per Page | OFFSET |
|---|---|---|
| 1 | 3 | 0 |
| 2 | 3 | 3 |
| 3 | 3 | 6 |
| 4 | 3 | 9 |
LIMITcontrols how many records are displayed.OFFSETdetermines how many records to skip.$_GET['page']reads the selected page number from the URL.
This example demonstrates basic navigation. A production application should also calculate the total number of pages and disable navigation beyond the available records.
Concepts Covered
- Database pagination
- SQL LIMIT
- SQL OFFSET
- GET parameters
- Page number calculation
- Previous and Next navigation
Question 7: Calculate Total Pages for Database Pagination
Problem Statement
Create a PHP program that counts the total student records and calculates how many pages are required when displaying 3 records per page.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$recordsPerPage = 3;
$sql = "SELECT COUNT(*) AS total FROM students";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$totalRecords = (int) $row['total'];
$totalPages = (int) ceil($totalRecords / $recordsPerPage);
echo "Total Records: " . $totalRecords . "<br>";
echo "Records Per Page: " . $recordsPerPage . "<br>";
echo "Total Pages: " . $totalPages;
$conn->close();
?>
Sample Output
Total Records: 10
Records Per Page: 3
Total Pages: 4
Explanation
COUNT(*)counts all student records.$totalRecordsstores the total number of records.$recordsPerPagedefines how many records each page can display.ceil()rounds a decimal number upward to the next whole number.
For example:
$totalPages = ceil(10 / 3);
The result is 4 because 10 records need four pages when each page displays a maximum of 3 records.
Concepts Covered
- SQL COUNT()
- Total record calculation
- Pagination logic
- PHP ceil() function
- Total page calculation
Question 8: Create Complete Pagination with Page Numbers
Problem Statement
Create a PHP program that displays 3 student records per page and generates page-number links automatically. Users should be able to click page numbers to view different records.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$recordsPerPage = 3;
$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;
if ($page < 1) {
$page = 1;
}
// Count total records
$countResult = $conn->query(
"SELECT COUNT(*) AS total FROM students"
);
$countRow = $countResult->fetch_assoc();
$totalRecords = (int) $countRow['total'];
$totalPages = (int) ceil($totalRecords / $recordsPerPage);
if ($totalPages > 0 && $page > $totalPages) {
$page = $totalPages;
}
$offset = ($page - 1) * $recordsPerPage;
// Fetch current page records
$sql = "SELECT * FROM students
ORDER BY id ASC
LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("ii", $recordsPerPage, $offset);
$stmt->execute();
$result = $stmt->get_result();
echo "<h3>Student List</h3>";
while ($row = $result->fetch_assoc()) {
echo htmlspecialchars($row['name']) . " - ";
echo htmlspecialchars($row['course']) . "<br>";
}
echo "<br>Pages: ";
for ($i = 1; $i <= $totalPages; $i++) {
echo '<a href="?page=' . $i . '">' . $i . '</a> ';
}
$stmt->close();
$conn->close();
?>
Sample Output
Student List
Rahul - PHP
Priya - Python
Amit - Java
Pages: 1 2 3 4
When a user clicks page number 2, the URL becomes:
students.php?page=2
The program displays the next three records.
Explanation
COUNT(*)finds the total number of student records.ceil()calculates the number of pages.- The
forloop generates a link for each page. - Clicking a page number sends that number through the URL.
LIMITandOFFSETretrieve records for the selected page.- The page number is checked so users cannot request a negative or unnecessarily large page.
Concepts Covered
- Complete pagination
- Dynamic page links
- COUNT() query
- LIMIT and OFFSET
- For loop
- GET parameters
Question 9: Create a Searchable Student List Using PDO
Problem Statement
Create a PHP program that searches student records by name using PDO. The search should support partial matching, so entering Pri should find Priya.
PHP Solution
<?php
try {
$pdo = new PDO(
"mysql:host=localhost;dbname=school_db;charset=utf8mb4",
"root",
""
);
$pdo->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
$searchName = "Pri";
$sql = "SELECT * FROM students
WHERE name LIKE :search
ORDER BY name ASC";
$stmt = $pdo->prepare($sql);
$stmt->execute([
':search' => "%" . $searchName . "%"
]);
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (count($students) > 0) {
foreach ($students as $student) {
echo "Name: " .
htmlspecialchars($student['name']) . "<br>";
echo "Email: " .
htmlspecialchars($student['email']) . "<br>";
echo "Course: " .
htmlspecialchars($student['course']) . "<br>";
echo "<hr>";
}
} else {
echo "No matching students found.";
}
} catch (PDOException $e) {
error_log($e->getMessage());
echo "Unable to complete the search.";
}
?>
Sample Output
Name: Priya
Email: priya@gmail.com
Course: Python
Explanation
new PDO()connects PHP to the MySQL database.prepare()creates a prepared SQL statement.:searchis a named placeholder.execute()supplies the search value.%Pri%finds names containingPri.fetchAll()retrieves all matching records.htmlspecialchars()protects HTML output.error_log()records technical errors without showing database details to visitors.
Concepts Covered
- PDO database connection
- PDO prepared statements
- Named placeholders
- Partial name search
- fetchAll()
- Exception handling
Question 10: Build a Searchable and Paginated Student Application
Problem Statement
Create a database-driven student application that allows users to:
- Search students by name.
- Display only 3 records per page.
- Navigate using Previous, Next, and page-number links.
- Display a message when no matching records are found.
The application should maintain the search value while moving between pages.
PHP Solution
Save the following code as student_search.php.
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$recordsPerPage = 3;
// Get search value
$search = trim($_GET['search'] ?? '');
$search = substr($search, 0, 100);
// Get page number
$page = isset($_GET['page']) ? (int) $_GET['page'] : 1;
if ($page < 1) {
$page = 1;
}
// Prepare search pattern
$searchValue = "%" . $search . "%";
// Count matching records
$countSql = "SELECT COUNT(*) AS total
FROM students
WHERE name LIKE ?";
$countStmt = $conn->prepare($countSql);
$countStmt->bind_param("s", $searchValue);
$countStmt->execute();
$countResult = $countStmt->get_result();
$countRow = $countResult->fetch_assoc();
$totalRecords = (int) $countRow['total'];
$totalPages = (int) ceil($totalRecords / $recordsPerPage);
$countStmt->close();
// Correct page number
if ($totalPages > 0 && $page > $totalPages) {
$page = $totalPages;
}
$offset = ($page - 1) * $recordsPerPage;
// Fetch matching records
$sql = "SELECT * FROM students
WHERE name LIKE ?
ORDER BY id ASC
LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param(
"sii",
$searchValue,
$recordsPerPage,
$offset
);
$stmt->execute();
$result = $stmt->get_result();
?>
<h2>Student Search Application</h2>
<form method="GET">
<input
type="text"
name="search"
placeholder="Search student name"
value="<?php echo htmlspecialchars($search); ?>"
>
<button type="submit">Search</button>
</form>
<hr>
<?php
if ($result->num_rows > 0) {
echo "<h3>Student Records</h3>";
echo "Total Matching Records: " . $totalRecords . "<br><br>";
while ($row = $result->fetch_assoc()) {
echo "<strong>Name:</strong> " .
htmlspecialchars($row['name']) . "<br>";
echo "<strong>Email:</strong> " .
htmlspecialchars($row['email']) . "<br>";
echo "<strong>Course:</strong> " .
htmlspecialchars($row['course']) . "<br>";
echo "<strong>Marks:</strong> " .
(int) $row['marks'] . "<br>";
echo "<hr>";
}
// Previous page link
if ($page > 1) {
echo '<a href="?search=' .
urlencode($search) .
'&page=' . ($page - 1) .
'">Previous</a> ';
}
// Page number links
for ($i = 1; $i <= $totalPages; $i++) {
echo '<a href="?search=' .
urlencode($search) .
'&page=' . $i .
'">' . $i . '</a> ';
}
// Next page link
if ($page < $totalPages) {
echo '<a href="?search=' .
urlencode($search) .
'&page=' . ($page + 1) .
'">Next</a>';
}
} else {
echo "No matching student records found.";
}
$stmt->close();
$conn->close();
?>
Sample Output
When the user searches for a:
Student Search Application
[ a ] [Search]
Total Matching Records: 6
Name: Rahul
Email: rahul@gmail.com
Course: PHP
Marks: 85
Name: Amit
Email: amit@gmail.com
Course: Java
Marks: 75
Name: Karan
Email: karan@gmail.com
Course: Python
Marks: 80
1 2
The sample results are based on the alphabetical and ID ordering used in the query; the actual first page contains the first three matching records in ascending ID order.
When the user searches for xyz:
Student Search Application
[ xyz ] [Search]
No matching student records found.
Explanation
This application combines database search and pagination.
1. Search input
$search = trim($_GET['search'] ?? '');
It reads the search value from the URL and removes unnecessary spaces from the beginning and end.
2. Search pattern
$searchValue = "%" . $search . "%";
The wildcard % allows partial name matching.
3. Count matching records
SELECT COUNT(*) AS total
FROM students
WHERE name LIKE ?
This query counts only those records that match the search.
4. Calculate total pages
$totalPages = ceil($totalRecords / $recordsPerPage);
It calculates how many pages are needed for the matching records.
5. Retrieve records for the selected page
LIMIT ? OFFSET ?
LIMIT controls the number of records displayed, while OFFSET skips records from earlier pages.
6. Maintain search while navigating
urlencode($search)
This keeps the search value in the URL when the user clicks Previous, Next, or a page number.
7. Display safe output
htmlspecialchars($row['name'])
This prevents database text from being interpreted as HTML or executable script.
Concepts Covered
- Database-driven applications
- Search functionality
- Partial text matching
- PDO and MySQLi comparison
- Pagination
- LIMIT and OFFSET
- COUNT() query
- GET parameters
- Prepared statements
- Safe HTML output
- Dynamic page navigation
Key Takeaways
- Database search helps users find specific records quickly.
- SQL
WHEREfilters records according to conditions. - SQL
LIKEsupports partial text matching. - Prepared statements help protect database queries from SQL injection.
- Pagination divides large database results into smaller pages.
LIMITcontrols the number of records displayed.OFFSETdetermines how many records to skip.COUNT(*)calculates the total number of matching records.ceil()helps calculate the total number of pages.- PHP can combine search and pagination to create practical database-driven applications.
Frequently Asked Questions (FAQs)
1. What is database search in PHP?
Database search in PHP allows users to find specific records stored in a database. PHP receives the search value and uses an SQL query to retrieve matching records.
2. What is pagination in PHP?
Pagination is the process of dividing database records into smaller pages. Instead of displaying hundreds of records together, PHP displays a limited number on each page.
3. What is the purpose of LIMIT in MySQL?
LIMIT controls how many records a query returns.
For example:
SELECT * FROM students LIMIT 5;
This query retrieves a maximum of 5 records.
4. What is OFFSET in MySQL?
OFFSET tells MySQL how many records to skip before returning results.
For example:
SELECT * FROM students LIMIT 3 OFFSET 3;
This query skips the first 3 records and retrieves the next 3.
5. What is the difference between exact search and partial search?
Exact search finds records matching the complete search value. Partial search finds records containing the search value.
For example, an exact search for Rahul matches Rahul, while a partial search for Rah can also find Rahul.
6. What is the use of LIKE in MySQL?
LIKE is used to search for records matching a particular text pattern. It commonly works with wildcard characters such as % and _.
7. How can we calculate total pages in PHP?
We can divide the total matching records by the number of records displayed per page and round the result upward using ceil().
$totalPages = ceil($totalRecords / $recordsPerPage);
8. Why should we use prepared statements for database search?
Prepared statements separate SQL instructions from user-supplied values. They help prevent SQL injection attacks and make database queries safer.
9. Can we use search and pagination together in PHP?
Yes. We can combine a search condition with LIMIT and OFFSET. This allows users to search records and navigate through matching results page by page.
10. Which database technologies can be used for search and pagination in PHP?
PHP supports database operations using MySQLi and PDO. Both can execute prepared statements and retrieve records for search and pagination features.
Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.
