# 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.

~~~sql
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:

- user and question
- required source fields
- processing or calculation
- useful order/filter
- output form
- success criterion supported

## Check

- [ ] The query answers a stated need.
- [ ] Only required fields are used.
- [ ] Grouping and calculations are correct.
- [ ] Empty results are handled.
- [ ] Output can be tested against known fictional data.

# Building a Leaderboard

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

~~~sql
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

- [ ] Ranking rule is stated.
- [ ] SQL aggregation matches the rule.
- [ ] Ties are handled predictably.
- [ ] Headers and a visible caption explain the table.
- [ ] Manual expected results match actual output.

# Calculating Summary Statistics

Statistics reduce a dataset to information users can interpret.

~~~sql
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.

~~~php
<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

- [ ] Each statistic has a purpose.
- [ ] Null or empty data is handled.
- [ ] Units and labels are visible.
- [ ] Rounding is consistent.
- [ ] Results are checked manually with a known dataset.

# 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

~~~php
$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

- [ ] Identifier is validated.
- [ ] Query is prepared.
- [ ] Missing record has a 404 response.
- [ ] Output supports the user’s journey.
- [ ] Text is escaped and data is fictional.

# 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

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

Pass encoded data safely:

~~~html
<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

- bar chart: compare teams or categories
- line chart: change over ordered time
- avoid pie charts when many categories or close values impede comparison

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

## Check

- [ ] Chart answers a stated question.
- [ ] SQL/PHP processing is explainable.
- [ ] Labels and units are present.
- [ ] Equivalent values are available without relying only on colour or graphics.
- [ ] Empty and extreme datasets are tested.

# 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

~~~php
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

- [ ] Export serves a real user need.
- [ ] Access rules match the data.
- [ ] `fputcsv()` handles escaping.
- [ ] Output contains no unintended personal or sensitive data.
- [ ] Download matches the on-screen processed result.