Topic 7: Calculate, logical operation, table print

📖 13 min read · 🎯 intermediate · 🧭 Prerequisites: javascript-validation, joint-query-nested-query-filtering-data

Why this matters

Up until now, you've probably stared at raw data — a list of names, numbers, or entries — and thought, "How do I make sense of this?" Every real project eventually needs you to do three things at once: crunch some numbers, apply a rule or condition, and display the result in a way anyone can read at a glance. In this lesson, we'll pull data from a database, run PHP's arithmetic and logical operations on it, and render everything into a clean HTML table. By the end, you'll know how to go from messy rows to a readable report — a skill you'll use in almost every project you build.

What You'll Learn

• How to execute aggregate SQL queries (COUNT, AVG, LENGTH, GROUP BY) from PHP using mysqli
• How to store and iterate over grouped query results in a PHP associative array
• How to apply logical operators in PHP to make threshold-based decisions
• How to combine PHP and HTML to render a styled, data-driven table

The Analogy

Think of a city census office. The raw data lives in filing cabinets (the database). A clerk (PHP) pulls the drawers, counts the folders (COUNT(*)), sorts them by district (GROUP BY Track), and measures the average length of the ID numbers (AVG(LENGTH(...))). Once the clerk has those figures, a simple rulebook (logical operators) decides whether the ID format meets the legal standard. Finally, a typesetter (the HTML table) formats every result into a clean printed report the mayor can hand out at the summit. The filing cabinet doesn't care how the report looks; the typesetter doesn't know how to count — each layer does exactly one job.

Chapter 1: PHP Calculations

the trainer opened the session by connecting PHP to the InnovatorsSummit database and firing three aggregate queries: total participant count, participants grouped by track, and the average length of contact numbers.

Key SQL functions used

Full calculation script

<?php
// Database configuration
$servername = "localhost";
$username   = "root";
$password   = "";
$dbname     = "InnovatorsSummit";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Calculate total number of participants
$totalParticipantsQuery = "SELECT COUNT(*) AS total FROM Participants";
$totalResult            = $conn->query($totalParticipantsQuery);
$totalParticipants      = $totalResult->fetch_assoc()['total'];

// Calculate number of participants in each track
$tracksQuery  = "SELECT Track, COUNT(*) AS count FROM Participants GROUP BY Track";
$tracksResult = $conn->query($tracksQuery);
$tracks       = [];
while ($row = $tracksResult->fetch_assoc()) {
    $tracks[$row['Track']] = $row['count'];
}

// Calculate average contact number length
$contactLengthQuery   = "SELECT AVG(LENGTH(ContactNumber)) AS averageLength FROM Participants";
$contactLengthResult  = $conn->query($contactLengthQuery);
$averageContactLength = $contactLengthResult->fetch_assoc()['averageLength'];

$conn->close();
?>

Walk-through of the data flow

1. new mysqli(...) opens the connection; connect_error guards against misconfiguration.
2. The first query returns one row with one column (total). fetch_assoc()['total'] plucks that value directly.
3. The second query returns one row per track. The while loop builds an associative array $tracks keyed by track name, valued by participant count — perfect for iterating later.
4. The third query returns one row (averageLength) via AVG(LENGTH(ContactNumber)) — nesting a string function inside an aggregate function in a single SQL expression.
5. $conn->close() releases the connection before the page renders.

Chapter 2: Logical Operations

With $averageContactLength in hand, the trainer showed the class how PHP's comparison and boolean operators turn a raw number into a decision.

Concept: comparison operators in PHP

Threshold check script

<?php
// Define a threshold for the average contact number length
$threshold = 10;

// Check if the average contact number length exceeds the threshold
$isExceedingThreshold = $averageContactLength > $threshold;

if ($isExceedingThreshold) {
    echo "The average contact number length exceeds the threshold.";
} else {
    echo "The average contact number length is within the acceptable range.";
}
?>

$isExceedingThreshold stores the boolean result of $averageContactLength > $threshold. Assigning the result of a comparison to a named variable — rather than embedding it directly in the if — makes the intent readable at a glance and lets you reuse the value multiple times (as the table section does below).

Chapter 3: Printing Tables

The class's final task was rendering all three data points — total participants, the per-track breakdown, and the contact-length verdict — into an HTML page anyone could open in a browser. PHP is embedded directly inside the HTML using <?php ... ?> tags.

Complete PHP + HTML table output

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Innovators' Summit Data</title>
    <style>
        table {
            width: 50%;
            border-collapse: collapse;
            margin: 25px 0;
            font-size: 18px;
            text-align: left;
        }
        th, td {
            padding: 12px;
            border: 1px solid #ddd;
        }
        th {
            background-color: #f2f2f2;
        }
    </style>
</head>
<body>

    <h1>Innovators' Summit Data</h1>

    <h2>Total Participants</h2>
    <p><?php echo $totalParticipants; ?></p>

    <h2>Participants by Track</h2>
    <table>
        <thead>
            <tr>
                <th>Track</th>
                <th>Number of Participants</th>
            </tr>
        </thead>
        <tbody>
            <?php foreach ($tracks as $track => $count) : ?>
            <tr>
                <td><?php echo $track; ?></td>
                <td><?php echo $count; ?></td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>

    <h2>Average Contact Number Length</h2>
    <p><?php echo $averageContactLength; ?></p>

    <h2>Contact Length Check</h2>
    <p>
        <?php echo $isExceedingThreshold
            ? "The average contact number length exceeds the threshold."
            : "The average contact number length is within the acceptable range.";
        ?>
    </p>

</body>
</html>

Key patterns in this template

• border-collapse: collapse — merges adjacent cell borders into a single line instead of doubling them.
• <?php foreach ($tracks as $track => $count) : ?> ... <?php endforeach; ?> — the alternative foreach syntax (:/endforeach) keeps HTML readable inside loops; it is equivalent to foreach (...) { } but avoids nested braces cluttering the markup.
• Ternary operator in output — $isExceedingThreshold ? "..." : "..." is PHP's inline if/else, used here directly inside echo to keep the display logic tight.

flowchart LR
    DB[(InnovatorsSummit DB)] -->|COUNT * | A[totalParticipants]
    DB -->|GROUP BY Track| B[$tracks array]
    DB -->|AVG LENGTH | C[averageContactLength]
    C --> D{> threshold?}
    D -- yes --> E[Exceeds threshold message]
    D -- no --> F[Within range message]
    A --> G[HTML Page]
    B --> G
    C --> G
    E --> G
    F --> G

🧪 Try It Yourself

Task: Create a PHP page that connects to a local school database containing a students table with columns id, name, grade (A/B/C/D/F), and phone. The page should:

1. Display the total number of students.
2. Display a table of grade counts (how many students per grade letter).
3. Check whether the average phone number length is at least 10 characters, and print a message accordingly.

Success criterion: When you open the page in a browser you should see three sections — a total count, a two-column grade breakdown table, and one of the two threshold messages — all populated from live database rows.

Starter snippet (connection + first query):

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

$total = $conn->query("SELECT COUNT(*) AS total FROM students")
              ->fetch_assoc()['total'];

// Add grade breakdown and AVG(LENGTH(phone)) queries here
?>

🔍 Checkpoint Quiz

Q1. What does the SQL expression AVG(LENGTH(ContactNumber)) calculate, and why are two functions nested together?

Q2. Given the following snippet, what is printed when $averageContactLength is 9.5 and $threshold is 10?

<?php
$threshold = 10;
$isExceedingThreshold = $averageContactLength > $threshold;
echo $isExceedingThreshold
    ? "Exceeds threshold."
    : "Within range.";
?>

A) Exceeds threshold. B) Within range. C) Nothing — the ternary syntax is invalid in PHP. D) A PHP warning about an undefined variable.

Q3. What is the purpose of border-collapse: collapse in the table CSS, and what would the table look like without it?

Q4. How would you modify the $tracksQuery to only include tracks with more than 5 participants?

A) Add WHERE count > 5 before GROUP BY B) Add HAVING COUNT(*) > 5 after GROUP BY C) Add LIMIT 5 after GROUP BY D) Use FILTER BY count > 5 after GROUP BY

A1. LENGTH(ContactNumber) computes the character length of each contact number string; AVG(...) then averages those lengths across all rows. The two functions are nested because SQL evaluates the inner function per row first, then the outer aggregate collapses all row-level results into one summary value.

A2. B — 9.5 > 10 evaluates to false, so $isExceedingThreshold is false and the ternary prints "Within range."

A3. border-collapse: collapse merges adjacent cell borders so each edge between cells appears as a single line. Without it, the default separate model renders a gap between every pair of cells, making the table look double-bordered and visually heavier.

A4. B — HAVING filters on aggregated values (counts, averages) after GROUP BY groups the rows. WHERE filters before aggregation and cannot reference COUNT(*) results; LIMIT restricts the number of output rows; FILTER BY is not valid SQL.

🪞 Recap

• PHP's mysqli lets you fire aggregate SQL queries (COUNT, AVG, LENGTH, GROUP BY) and capture their results with fetch_assoc().
• Grouped query results are best collected into a PHP associative array with a while loop, then iterated in HTML with foreach.
• Comparison operators (>, <, ===) return booleans you can store in named variables for readable, reusable logical checks.
• PHP's alternative foreach (...) : ... endforeach; syntax keeps HTML templates clean when looping inside markup.
• Combining PHP calculations, logical operators, and HTML tables in a single .php file is the standard pattern for rendering dynamic, database-driven reports.

📚 Further Reading

• PHP mysqli documentation — the source of truth on mysqli connections, queries, and result fetching
• PHP Operators — full reference for comparison, logical, and ternary operators in PHP
• MySQL Aggregate Functions — official docs for COUNT, AVG, SUM, GROUP BY, and HAVING
• MDN: CSS border-collapse — explains separate vs. collapse table border models with visual examples
• ⬅️ Previous: Joint Query, Nested Query, Filtering DATA
• ➡️ Next: JavaScript Cookies