Search for | Mr Napper

Inserting Imported Rows Safely

Working with Data Importing CSV into MySQL with PHP

Prepare the insert once, then execute it for each valid row. $insert = $pdo->prepare( "INSERT INTO team_results (match_id, team_name, wins, losses) VALUES (:match_id, :team_name, :wins, :losses)" ); $insert->execute([ "match_id" => $matchId, "...

New Page

Working with Data Importing CSV into MySQL with PHP

Reporting CSV Import Results

Working with Data Importing CSV into MySQL with PHP

An administrator needs evidence of what happened, not just “upload complete”. Report useful totals Show the escaped filename, rows read, inserted, updated, skipped and rejected, plus row-specific validation messages and whether a transaction committed or rolle...

Building a Complete Admin CSV Import

Working with Data Importing CSV into MySQL with PHP

A complete import combines access control, upload checks, parsing, row validation, prepared statements and a result summary. Processing sequence Start the session and require the administrator role. Accept a POST upload. Validate upload status, size and extens...

Testing an Admin CSV Import

Working with Data Importing CSV into MySQL with PHP

Testing must cover permissions, validation, database effects and feedback. Test Condition Expected result Logged out Direct import URL Redirect to login Standard role Direct URL or POST 403; nothing imported Valid CSV Correct headings and rows Expected records...

Querying Data for a User Need

Working with Data Processing and Presenting Imported Data

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

Building a Leaderboard

Working with Data Processing and Presenting Imported Data

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

Calculating Summary Statistics

Working with Data Processing and Presenting Imported Data

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

Creating Match and Player Views

Working with Data Processing and Presenting Imported Data

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( "S...

Creating Charts from Processed SQL Data

Working with Data Processing and Presenting Imported 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_n...

Exporting Processed Data to CSV

Working with Data Processing and Presenting Imported Data

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; charse...