如何使用PHP在上传前验证CSV内容并保存至MySQL数据库？

Hey there! I’ve been right where you are—scouring the web for a solid way to validate CSV content before dumping it into a MySQL database with PHP and MySQLi. Let’s walk through a practical, step-by-step solution tailored to your three columns: Date, Email, and Phone Number.

Before we even look at the CSV content, we need to make sure we’re dealing with a legitimate file. Skip the lazy checks—validate the file type, extension, and size to avoid malicious uploads.

// Set allowed parameters
$allowedExtensions = ['csv'];
$maxFileSize = 5 * 1024 * 1024; // 5MB

// Check upload errors
if ($_FILES['csv_file']['error'] !== UPLOAD_ERR_OK) {
    die("Upload failed with error code: " . $_FILES['csv_file']['error']);
}

// Validate file extension
$fileExtension = strtolower(pathinfo($_FILES['csv_file']['name'], PATHINFO_EXTENSION));
if (!in_array($fileExtension, $allowedExtensions)) {
    die("Only CSV files are allowed.");
}

// Validate file size
if ($_FILES['csv_file']['size'] > $maxFileSize) {
    die("File is too large. Max size is 5MB.");
}

// Move uploaded file to a secure directory
$uploadPath = '/path/to/secure/uploads/' . uniqid() . '.csv';
if (!move_uploaded_file($_FILES['csv_file']['tmp_name'], $uploadPath)) {
    die("Failed to move uploaded file.");
}

Your database and CSV both use the same three headers—so first, confirm the CSV’s first row matches exactly. No typos, no wrong order.

$expectedHeaders = ['Date', 'Email', 'Phone Number'];
$file = fopen($uploadPath, 'r');

// Read first row (header)
$header = fgetcsv($file);
if (!$header) {
    die("CSV file is empty or invalid.");
}

// Trim whitespace from each header (in case of extra spaces)
$trimmedHeader = array_map('trim', $header);
if ($trimmedHeader !== $expectedHeaders) {
    die("CSV header does not match expected format. Expected: " . implode(', ', $expectedHeaders));
}

Now the fun part—checking every row to make sure each field meets your requirements. Let’s define clear rules for each column:

Date Validation #

Ensure it’s a valid date in YYYY-MM-DD format (adjust the format if your database uses something else):

function validateDate($date) {
    $dateObj = DateTime::createFromFormat('Y-m-d', trim($date));
    return $dateObj && $dateObj->format('Y-m-d') === trim($date);
}

Email Validation #

Use PHP’s built-in filter to ensure it’s a valid email address:

function validateEmail($email) {
    return filter_var(trim($email), FILTER_VALIDATE_EMAIL) !== false;
}

Phone Number Validation #

This depends on your needs—let’s say you want to allow international formats (digits, plus signs, parentheses, hyphens) and ensure there are at least 10 digits after cleaning:

function validatePhone($phone) {
    $cleanedPhone = preg_replace('/[^0-9+]/', '', trim($phone));
    // Check if we have at least 10 digits (adjust based on your region)
    return preg_match('/\+?[0-9]{10,}/', $cleanedPhone);
}

Put It All Together: Row-by-Row Check #

Loop through each row, validate, and collect errors if any:

$errors = [];
$validRows = [];

// Skip header (we already checked it)
while (($row = fgetcsv($file)) !== false) {
    $rowNumber = ftell($file); // Get approximate row number (adjust if needed)
    $trimmedRow = array_map('trim', $row);

    // Skip empty rows
    if (empty(array_filter($trimmedRow))) {
        continue;
    }

    // Validate each field
    $dateValid = validateDate($trimmedRow[0]);
    $emailValid = validateEmail($trimmedRow[1]);
    $phoneValid = validatePhone($trimmedRow[2]);

    if (!$dateValid || !$emailValid || !$phoneValid) {
        $errorMsg = "Row $rowNumber: ";
        if (!$dateValid) $errorMsg .= "Invalid date format (YYYY-MM-DD). ";
        if (!$emailValid) $errorMsg .= "Invalid email address. ";
        if (!$phoneValid) $errorMsg .= "Invalid phone number. ";
        $errors[] = rtrim($errorMsg, ' ');
    } else {
        // Clean data for database insertion
        $validRows[] = [
            'date' => $trimmedRow[0],
            'email' => $trimmedRow[1],
            'phone' => preg_replace('/[^0-9+]/', '', $trimmedRow[2]) // Clean phone for DB
        ];
    }
}

fclose($file);

// Handle errors
if (!empty($errors)) {
    echo "Validation failed. Errors:<br>";
    foreach ($errors as $error) {
        echo "- $error<br>";
    }
    // Delete the uploaded file since we're not using it
    unlink($uploadPath);
    exit;
}

Now that we have only valid data, use MySQLi prepared statements to safely insert into your database (prevents SQL injection):

// Connect to database (replace with your credentials)
$mysqli = new mysqli('localhost', 'username', 'password', 'database_name');
if ($mysqli->connect_error) {
    die("Database connection failed: " . $mysqli->connect_error);
}

// Prepare INSERT statement
$stmt = $mysqli->prepare("INSERT INTO your_table (Date, Email, `Phone Number`) VALUES (?, ?, ?)");
if (!$stmt) {
    die("Prepare failed: " . $mysqli->error);
}

// Bind parameters
$stmt->bind_param("sss", $date, $email, $phone);

// Insert each valid row
foreach ($validRows as $row) {
    $date = $row['date'];
    $email = $row['email'];
    $phone = $row['phone'];
    if (!$stmt->execute()) {
        die("Insert failed for row: " . $stmt->error);
    }
}

// Cleanup
$stmt->close();
$mysqli->close();
unlink($uploadPath); // Delete the uploaded CSV file

echo "Success! " . count($validRows) . " rows inserted into database.";

A few extra tips:

• Add more specific validation rules if needed (e.g., dates can’t be in the future, phone numbers must match a country code).
• For large CSVs, consider processing in chunks to avoid memory issues.
• Log errors to a file instead of just echoing them for production environments.

内容的提问来源于stack exchange，提问作者Saffron