Processing and Presenting Imported Data

Query, process and present stored data as meaningful outputs that respond to user needs and success criteria.

Querying Data for a User Need

A query is meaningful when it answers a user question. Begin with the need, then choose fields, filters, calculations and order.

Example need

A participant wants to find the strongest fictional teams and inspect their performance.

SELECT team_name,
       SUM(wins) AS total_wins,
       SUM(losses) AS total_losses
FROM team_results
GROUP BY team_name
ORDER BY total_wins DESC, team_name ASC;

This processes many stored rows into one summary per team. It is stronger evidence of processing than simply displaying the imported table.

Plan the output

Document:

Check

Building a Leaderboard

A leaderboard converts stored results into a ranked, understandable output.

SELECT team_name,
       SUM(wins) AS wins,
       SUM(losses) AS losses,
       SUM(wins) * 3 AS points
FROM team_results
GROUP BY team_name
ORDER BY points DESC, wins DESC, team_name ASC;

The points rule is an example only. Use the rule justified by the chosen context.

Display the ranking

Iterate through the query result and create a table with rank, team, wins, losses and points. Increment rank inside the loop. Escape text and cast numbers.

Explain tie behaviour. A deterministic secondary sort prevents ranks from changing unpredictably.

Test

Use a small dataset where you can calculate the result manually. Include a tie, zero values and an empty dataset.

Check

Calculating Summary Statistics

Statistics reduce a dataset to information users can interpret.

SELECT
  COUNT(*) AS result_count,
  COUNT(DISTINCT team_name) AS team_count,
  SUM(wins) AS total_wins,
  AVG(wins) AS average_wins,
  MAX(wins) AS highest_wins
FROM team_results;

Choose statistics because they answer a need, not because functions are available. Label values clearly and round averages deliberately.

<p>Average wins:
  <?= number_format((float) $stats["average_wins"], 1) ?>
</p>

Interpretation

A number without context is weak. State what population and timeframe it represents. Do not imply cause from a descriptive statistic.

Check

Creating Match and Player Views

A detail view lets a user move from a summary to the record needed for a task.

Receive a validated identifier

$matchId = trim($_GET["match_id"] ?? "");
if ($matchId === "" || mb_strlen($matchId) > 20) {
    exit("Invalid match.");
}

$stmt = $pdo->prepare(
  "SELECT match_id, team_name, wins, losses
   FROM team_results
   WHERE match_id = :match_id"
);
$stmt->execute(["match_id" => $matchId]);
$match = $stmt->fetch();

if (!$match) {
    http_response_code(404);
    exit("Match not found.");
}

Display escaped values with headings that explain the record. Provide a logical way back to the summary.

For player views, use an appropriate relationship and do not invent personal data. All names and records must be fictional.

Check

Creating Charts from Processed SQL Data

A chart is useful only when it makes a comparison or pattern easier to understand. JavaScript is a supporting technology; the assessed reasoning lies in the data choice, processing and justified output.

Prepare data in PHP

$labels = array_column($rows, "team_name");
$values = array_map("intval", array_column($rows, "points"));

Pass encoded data safely:

<script>
const labels = <?= json_encode($labels) ?>;
const values = <?= json_encode($values) ?>;
</script>

A chart library can use these arrays. Provide a visible title, axis labels, sufficient contrast and a text/table alternative.

Choose the chart

Do not create a chart from raw unprocessed data when a grouped or calculated result is what users need.

Check

Exporting Processed Data to CSV

CSV export can support download and reuse of a filtered or processed result. It is optional when import already satisfies the data-transfer requirement, but can be valuable for a justified user need.

Send download headers

header("Content-Type: text/csv; charset=UTF-8");
header('Content-Disposition: attachment; filename="team-summary.csv"');

$output = fopen("php://output", "w");
fputcsv($output, ["Team", "Wins", "Losses", "Points"]);

foreach ($rows as $row) {
    fputcsv($output, [
        $row["team_name"],
        $row["wins"],
        $row["losses"],
        $row["points"]
    ]);
}
fclose($output);
exit;

Run authentication and authorisation before output headers. Build $rows with the same validated filters used by the visible report.

Test

Open the download in a text editor and spreadsheet. Check headings, quoted commas, character encoding, row count and filtered scope.

Check