Fetching Data from MySQL in PHP: SELECT, Filters & Error Handling

Study Deep: Implementing Pagination

When a table has thousands of records, you shouldn't display them all at once. Pagination is the solution.

The SQL Logic:

-- Page 1 (Records 1-10)
SELECT * FROM students LIMIT 10 OFFSET 0;

-- Page 2 (Records 11-20)
SELECT * FROM students LIMIT 10 OFFSET 10;

The PHP Formula: $offset = ($currentPage - 1) * $recordsPerPage;

1. The SELECT Statement — Overview

SELECT is the most used SQL command. It retrieves data from one or more tables. The data returned is called a result set.

Basic Structure:

SELECT column1, column2 FROM table_name
WHERE condition
ORDER BY column ASC|DESC
LIMIT number;

Wildcards: Use * to select all columns (convenient but less efficient for large tables).

SELECT * FROM students;           -- All columns, all rows
SELECT name, marks FROM students; -- Only name & marks columns

2. Filtering with WHERE

SELECT * FROM students WHERE course = 'BCA';
SELECT * FROM students WHERE marks >= 60;
SELECT * FROM students WHERE name LIKE 'R%';    -- Names starting with R
SELECT * FROM students WHERE city IN ('Delhi','Mumbai');
SELECT * FROM students WHERE marks BETWEEN 50 AND 80;

3. Sorting and Limiting Results

-- Sort by marks descending (highest first)
SELECT * FROM students ORDER BY marks DESC;

-- Get only top 5 students
SELECT name, marks FROM students
ORDER BY marks DESC
LIMIT 5;

-- Pagination: skip first 10, show next 5
SELECT * FROM students LIMIT 5 OFFSET 10;

4. Executing a SELECT Query in PHP — Step by Step

<?php
$conn = new mysqli("localhost", "root", "", "CollegeDB");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

// Step 1: Define and execute the query
$sql    = "SELECT id, name, course, marks FROM students ORDER BY marks DESC";
$result = $conn->query($sql);

// Step 2: Check if any rows were returned
if ($result->num_rows > 0) {
  echo "<table border='1'>";
  echo "<tr><th>ID</th><th>Name</th><th>Course</th><th>Marks</th></tr>";

  // Step 3: Fetch rows one by one using fetch_assoc()
  while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row["id"]     . "</td>";
    echo "<td>" . $row["name"]   . "</td>";
    echo "<td>" . $row["course"] . "</td>";
    echo "<td>" . $row["marks"]  . "</td>";
    echo "</tr>";
  }

  echo "</table>";
} else {
  echo "No students found!";
}

$conn->close();
?>

5. Result Fetching Methods

After executing a SELECT query, the $result object holds the returned data. PHP provides several methods to extract rows:

// Using fetch_object()
while ($row = $result->fetch_object()) {
  echo $row->name . " — " . $row->marks . "<br>";
}

// Using fetch_all() to get all data at once
$allStudents = $result->fetch_all(MYSQLI_ASSOC);
foreach ($allStudents as $student) {
  echo $student['name'] . "<br>";
}

6. Prepared SELECT Statements (Safe Queries with User Input)

When the query includes user-provided data (e.g., from a search form), always use a prepared statement.

<?php
$searchCourse = $_GET['course'] ?? 'BCA'; // User input from URL

$stmt = $conn->prepare("SELECT name, marks FROM students WHERE course = ? ORDER BY marks DESC");
$stmt->bind_param("s", $searchCourse); // Bind the parameter safely
$stmt->execute();

$result = $stmt->get_result(); // Get the result set

while ($row = $result->fetch_assoc()) {
  echo $row['name'] . ": " . $row['marks'] . "<br>";
}
$stmt->close();
?>

7. Error Handling Best Practices

// Turn on MySQLi error reporting (Development only!)
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

// Check query errors explicitly
$result = $conn->query($sql);
if (!$result) {
  echo "Query Error: " . $conn->error;
  exit;
}

// Check specific error codes
if ($conn->errno === 1062) {
  echo "Duplicate entry — email already exists!";
}

8. Complete PHP MySQL Query Workflow Summary

1. Connect to DB      → new mysqli(...)
2. Check connection   → $conn->connect_error
3. Prepare query      → $conn->prepare(...) or just $sql = "..."
4. Execute            → $conn->query($sql) or $stmt->execute()
5. Process results    → while ($row = $result->fetch_assoc())
6. Handle errors      → $conn->error
7. Close connection   → $conn->close()

9. Aggregate Functions in PHP

Using MySQL aggregate functions within PHP to summarize and analyze data.

<?php
$conn = new mysqli("localhost", "root", "", "CollegeDB");

// Get summary statistics in a single query
$sql    = "SELECT COUNT(*) AS total,
                  AVG(marks) AS avg_marks,
                  MAX(marks) AS highest,
                  MIN(marks) AS lowest
           FROM students";
$result = $conn->query($sql);
$stats  = $result->fetch_assoc();

echo "Total students: "    . $stats['total']     . "<br>";
echo "Average marks: "     . round($stats['avg_marks'], 2) . "<br>";
echo "Highest marks: "     . $stats['highest']   . "<br>";
echo "Lowest marks: "      . $stats['lowest']    . "<br>";

// GROUP BY — count per course
$sql    = "SELECT course, COUNT(*) AS count, AVG(marks) AS avg
           FROM students
           GROUP BY course
           ORDER BY avg DESC";
$result = $conn->query($sql);

echo "<table border='1'>";
echo "<tr><th>Course</th><th>Students</th><th>Avg Marks</th></tr>";
while ($row = $result->fetch_assoc()) {
  echo "<tr><td>{$row['course']}</td><td>{$row['count']}</td><td>" . round($row['avg'],2) . "</td></tr>";
}
echo "</table>";

$conn->close();
?>

10. JOIN Queries in PHP

Executing JOIN queries in PHP to combine data from multiple related tables.

<?php
$conn = new mysqli("localhost", "root", "", "CollegeDB");

// INNER JOIN — students with their department name
$sql = "SELECT s.id, s.name, s.marks, d.dept_name
        FROM students s
        INNER JOIN departments d ON s.dept_id = d.dept_id
        ORDER BY s.marks DESC";

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

echo "<h3>Students with Department</h3>";
echo "<table border='1'>";
echo "<tr><th>ID</th><th>Name</th><th>Department</th><th>Marks</th></tr>";

if ($result->num_rows > 0) {
  while ($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row['id']        . "</td>";
    echo "<td>" . $row['name']      . "</td>";
    echo "<td>" . $row['dept_name'] . "</td>";
    echo "<td>" . $row['marks']     . "</td>";
    echo "</tr>";
  }
} else {
  echo "<tr><td colspan='4'>No data found</td></tr>";
}
echo "</table>";

// LEFT JOIN — show ALL departments including those with no students
$sql = "SELECT d.dept_name, COUNT(s.id) AS student_count
        FROM departments d
        LEFT JOIN students s ON d.dept_id = s.dept_id
        GROUP BY d.dept_name";

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

echo "<h3>Department Enrollment</h3>";
while ($row = $result->fetch_assoc()) {
  echo $row['dept_name'] . ": " . $row['student_count'] . " student(s)<br>";
}

$conn->close();
?>

11. Prepared Statements with GROUP BY — Filtered Aggregates

<?php
// Find students in a specific course with marks above a threshold
$course    = $_GET['course'] ?? 'BCA';
$threshold = intval($_GET['min'] ?? 60);

$stmt = $conn->prepare(
  "SELECT name, marks
   FROM students
   WHERE course = ? AND marks >= ?
   ORDER BY marks DESC"
);
$stmt->bind_param("si", $course, $threshold);
$stmt->execute();

$result = $stmt->get_result();
echo "<p>Found " . $result->num_rows . " students in $course with marks ≥ $threshold</p>";

while ($row = $result->fetch_assoc()) {
  echo $row['name'] . " — " . $row['marks'] . "<br>";
}
$stmt->close();
$conn->close();
?>