Tutorial: Import CSV into MySQL using PHP

Whether you’re wrangling Open PageRank scores or prepping domain data for analysis, importing a CSV into MySQL shouldn’t feel like wizardry. This hands-on PHP tutorial walks you through a clean, reproducible ingestion pipeline—perfect for editorial QA, schema hygiene, and rookie onboarding. No frameworks, no fluff—just a straight-up script that gets the job done and teaches by example. Let’s turn raw rows into queryable gold.

🧩 Prerequisites

• PHP 7.x or higher
• MySQL database with a table seed_opr_raw
• CSV file with columns: rank_in_file, domain, openpagerank_score, batch_tag, ingested_at

🛠️ Step 1: Create the PHP Import Script

<?php
// config
$dbHost = 'localhost';
$dbUser = 'db_user';
$dbPass = 'your_password';
$dbName = 'my_DB';
$tableName = 'seed_opr_raw';
$csvFile = 'opr_batch.csv';
$batchTag = '2025-09-06_100k_OPR_first_batch';

// connect
$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);

// open file
if (($handle = fopen($csvFile, 'r')) !== FALSE) {
    $rowCount = 0;
    while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
        // skip header
        if ($rowCount === 0) { $rowCount++; continue; }

        // sanitize
        $rank = (int)$data[0];
        $domain = $conn->real_escape_string($data[1]);
        $score = (float)$data[2];
        $ingestedAt = date('Y-m-d H:i:s');

        // insert
        $sql = "INSERT INTO `$tableName` 
                (`rank_in_file`, `domain`, `openpagerank_score`, `batch_tag`, `ingested_at`) 
                VALUES ($rank, '$domain', $score, '$batchTag', '$ingestedAt')";
        if ($conn->query($sql) === TRUE) {
            if ($rowCount % 1000 === 0) echo "✅ Inserted $rowCount rows...\n";
        } else {
            echo "❌ Error at row $rowCount: " . $conn->error . "\n";
        }
        $rowCount++;
    }
    fclose($handle);
    echo "🎉 Done. Total rows inserted: " . ($rowCount - 1) . "\n";
} else {
    echo "❌ Failed to open CSV file.\n";
}

$conn->close();
?>

📂 Step 2: Prepare Your CSV File

Ensure your file opr_batch.csv looks like this:

"1","facebook.com","10.00"
"2","fonts.googleapis.com","10.00"
"3","instagram.com","10.00"
"4","googletagmanager.com","10.00"
"5","google.com","10.00"
"6","youtube.com","10.00"
"7","twitter.com","10.00"
"8","linkedin.com","10.00"
"9","fonts.gstatic.com","10.00"
"10","gmpg.org","10.00"
"11","ajax.googleapis.com","10.00"
"12","maps.google.com","9.94"
"13","play.google.com","9.86"
"14","youtu.be","9.80"
"15","cdnjs.cloudflare.com","9.64"
"16","drive.google.com","9.62"
"17","github.com","9.54"
"18","wordpress.org","9.54"
"19","x.com","9.49"
"20","support.google.com","9.43"
"21","docs.google.com","9.42"
"22","pinterest.com","9.37"
"23","en.wikipedia.org","9.36"
"24","policies.google.com","9.36"
"25","tiktok.com","9.34"
"26","amazon.com","9.29"
"27","bit.ly","9.28"
"28","creativecommons.org","9.20"
"29","developers.google.com","9.18"
"30","apps.apple.com","9.15"
"31","goo.gl","9.12"
"32","itunes.apple.com","9.06"
"33","nytimes.com","9.00"
"34","reddit.com","8.97"
"35","medium.com","8.96"
"36","accounts.google.com","8.95"
"37","open.spotify.com","8.95"
"38","microsoft.com","8.93"
"39","gstatic.com","8.92"
"40","cdn.jsdelivr.net","8.89"
"41","vimeo.com","8.89"
"42","secure.gravatar.com","8.84"
"43","plus.google.com","8.84"
"44","soundcloud.com","8.80"
"45","lh3.googleusercontent.com","8.74"
"46","sites.google.com","8.73"
"47","ec.europa.eu","8.72"
"48","vk.com","8.60"
"49","t.me","8.60"
"50","theverge.com","8.58"

You can add batch_tag and ingested_at dynamically in the script.

🧪 Step 3: Run the Script

From terminal:

php import_opr.php

Make sure the script and CSV file are in the same directory, or adjust the path accordingly.

The structure of the table looks like below in this case:

🧼 Optional Enhancements

• Add CLI flags for --file, --tag, --dry-run
• Log errors to a file for audit trail
• Wrap in a shell script for cron automation
• Add duplicate check before insert

🎉 Final Words

Congrats! You’ve just built a clean, scalable ingestion pipeline using nothing but PHP, MySQL, and a bit of rookie-friendly logic. You’ve learned how to sanitize inputs, tag batches, and log inserts with clarity—skills that scale from solo scripts to community-grade publishing systems. Whether you’re prepping data for SEO audits, dashboards, or open-source tools, this foundation is solid. Now go ahead and teach it forward. The next coder is watching.