Importing CSV into MySQL with PHP

Build a secure administrator-only workflow that validates fictional CSV data and stores acceptable rows in MySQL.

Understanding CSV Data

CSV means comma-separated values. Each line is a record and each position represents a field.

match_id,team_name,wins,losses
M001,Fictional Falcons,4,1
M002,Sample Sharks,3,2

The first row is a header. Quoted fields may contain commas, so do not process CSV with explode(","); use fgetcsv().

Inspect before importing

Identify headers, data types, required fields, allowed ranges, unique identifiers, missing values and any fields that relate to other tables.

Treat every upload as untrusted input. A file being supplied with a task does not remove the need for validation.

Check

Designing a MySQL Table from a CSV

Design the destination table from the meaning of the data, not merely its appearance in a spreadsheet.

CSV field MySQL type Rule
match_id VARCHAR(20) unique and required
team_name VARCHAR(100) required
wins INT zero or greater
losses INT zero or greater 
CREATE TABLE team_results (
  result_id INT AUTO_INCREMENT PRIMARY KEY,
  match_id VARCHAR(20) NOT NULL UNIQUE,
  team_name VARCHAR(100) NOT NULL,
  wins INT NOT NULL,
  losses INT NOT NULL
);

A surrogate primary key supports database operations; the unique source identifier prevents accidental duplicates.

Design questions

Document and justify the chosen policy.

Check

Creating an Admin-Only CSV Upload Form

A CSV upload changes stored data and should be restricted to administrators. Apply session and role checks before any output.

<?php
session_start();
if (!isset($_SESSION["user_id"])) {
    header("Location: login.php");
    exit;
}
if (($_SESSION["role"] ?? "") !== "admin") {
    http_response_code(403);
    exit("Permission denied.");
}
?>
<form method="post" enctype="multipart/form-data">
  <label for="dataset">CSV dataset</label>
  <input id="dataset" name="dataset" type="file" accept=".csv,text/csv" required>
  <button type="submit">Validate and import</button>
</form>

The multipart/form-data encoding is required. The accept attribute guides file selection but does not provide server-side security.

Check

Validating an Uploaded CSV File

Validate the upload before reading its rows.

$file = $_FILES["dataset"] ?? null;
$errors = [];

if (!$file || $file["error"] !== UPLOAD_ERR_OK) {
    $errors[] = "Choose a CSV file that uploaded successfully.";
}
if ($file && $file["size"] > 2 * 1024 * 1024) {
    $errors[] = "The file must be 2 MB or smaller.";
}
$extension = $file ? strtolower(pathinfo($file["name"], PATHINFO_EXTENSION)) : "";
if ($extension !== "csv") {
    $errors[] = "The file must use the .csv extension.";
}

A filename or MIME type alone can be misleading. Combine upload status, size, extension, readable content, header and row validation.

Never use the original filename as a server path. Process the temporary upload and store only what the application requires.

Check

Test missing files, oversized files, wrong extensions, empty files and malformed content. Every failure should produce a clear message and must not partially import data.

Reading CSV Rows with fgetcsv

Use PHP’s CSV parser so quoted commas and escaped values are handled correctly.

$handle = fopen($file["tmp_name"], "r");
if ($handle === false) {
    exit("The uploaded file could not be read.");
}

$headers = fgetcsv($handle);
$expected = ["match_id", "team_name", "wins", "losses"];

if ($headers !== $expected) {
    fclose($handle);
    exit("The CSV headings do not match the expected format.");
}

$rowNumber = 1;
while (($row = fgetcsv($handle)) !== false) {
    $rowNumber++;
    // Validate and store this row.
}
fclose($handle);

This loop demonstrates iteration. The header comparison prevents values being assigned to the wrong fields.

Check

Validating CSV Rows

Validate every row before storing it.

if (count($row) !== 4) {
    $rowErrors[] = "Row $rowNumber has the wrong number of fields.";
    continue;
}

[$matchId, $teamName, $winsRaw, $lossesRaw] = array_map("trim", $row);

$wins = filter_var($winsRaw, FILTER_VALIDATE_INT);
$losses = filter_var($lossesRaw, FILTER_VALIDATE_INT);

if ($matchId === "" || $teamName === "") {
    $rowErrors[] = "Row $rowNumber has a missing required value.";
    continue;
}
if ($wins === false || $losses === false || $wins < 0 || $losses < 0) {
    $rowErrors[] = "Row $rowNumber has invalid results.";
    continue;
}

The loop is iteration; each validation decision is selection. Preserve row numbers so an administrator can correct the source.

Define whether one bad row rejects the whole file or only that row. For assessed work, justify the policy from integrity and user needs.

Check

Test blank, extra, missing, non-numeric, negative and duplicate values.

Inserting Imported Rows Safely

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,
    "team_name" => $teamName,
    "wins" => $wins,
    "losses" => $losses
]);

Use a transaction when the intended policy is all-or-nothing:

$pdo->beginTransaction();
try {
    // Read, validate and insert every row.
    $pdo->commit();
} catch (Throwable $error) {
    $pdo->rollBack();
    throw $error;
}

Transactions prevent a failed whole-file import from leaving a partial update. If valid rows may be retained while invalid rows are rejected, count both outcomes and report them clearly.

Check

Reporting CSV Import Results

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

$summary = [
  "read" => $rowsRead,
  "inserted" => $rowsInserted,
  "rejected" => count($rowErrors)
];

Totals must reconcile: rows read equals all outcome categories combined. Never display passwords, server paths or raw SQL errors.

Accessible output

Use a clear heading and a list or table. Do not communicate success through colour alone. Make corrective messages specific: include the CSV row number and violated rule.

Check

Building a Complete Admin CSV Import

A complete import combines access control, upload checks, parsing, row validation, prepared statements and a result summary.

Processing sequence

  1. Start the session and require the administrator role.
  2. Accept a POST upload.
  3. Validate upload status, size and extension.
  4. Open the temporary file and verify headers.
  5. Begin the chosen transaction policy.
  6. Iterate through rows.
  7. Validate fields and store valid data.
  8. Commit or roll back.
  9. Close the file and display a summary.

Pseudocode

BEGIN
  REQUIRE administrator
  INPUT CSV
  IF file is invalid THEN OUTPUT error; STOP
  READ and CHECK header
  FOR EACH row
    VALIDATE fields
    IF valid THEN STORE with prepared statement
    ELSE RECORD row error
    ENDIF
  ENDFOR
  OUTPUT summary
END

This provides evidence of input, selection, iteration, storage and output. Separate repeated checks into functions when it improves readability.

Check

Trace one valid and one invalid row through every step. Explain the duplicate and partial-import policy rather than leaving either to chance.

Testing an Admin CSV Import

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 stored
Wrong heading Changed column File rejected
Invalid number Text or negative result Stated rejection policy
Duplicate ID Existing identifier Stated duplicate policy
Empty file No usable rows Clear rejection
Oversized file Above limit Rejected before parsing

Record actual result, pass/fail and refinement. Verify the database rather than trusting the on-screen message.

Use fictional accounts and data. Hide credentials and unrelated information in screenshots.

Completion checklist