checker.php

<?php
/**
 * 
 * Database Integrity Checker
 * Seyyed Ali Mohammadiyeh (Max Base)
 * https://github.com/BaseMax/php-database-integrity-checker
 * - Validates connection
 * - Checks database exists
 * - Reads table list
 * - Detects duplicate values in PRIMARY / UNIQUE indexes
 * 
 */

// =============================
// 1. READ INPUT PARAMETERS
// =============================
$host     = $_POST['host']     ?? 'localhost';
$port     = $_POST['port']     ?? '3306';
$user     = $_POST['user']     ?? '';
$pass     = $_POST['pass']     ?? '';
$dbName   = $_POST['dbname']   ?? '';

if (!$user || !$dbName) {
    exit(json_encode([
        "status" => false,
        "error" => "Missing required parameters: user, dbname"
    ]));
}

// =============================
// 2. CONNECT TO MYSQL
// =============================
$dsn = "mysql:host=$host;port=$port;charset=utf8mb4";

try {
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
} catch (Exception $e) {
    exit(json_encode([
        "status" => false,
        "error" => "Connection failed: " . $e->getMessage()
    ]));
}

// =============================
// 3. CHECK DATABASE EXISTS
// =============================
$stmt = $pdo->prepare("SHOW DATABASES LIKE :dbname");
$stmt->execute(["dbname" => $dbName]);

if ($stmt->rowCount() === 0) {
    exit(json_encode([
        "status" => false,
        "error" => "Database '$dbName' does not exist."
    ]));
}

// Select DB
$pdo->exec("USE `$dbName`");

// =============================
// 4. LOAD LIST OF TABLES
// =============================
$tables = $pdo->query("SHOW TABLES")->fetchAll(PDO::FETCH_COLUMN);

if (!$tables) {
    exit(json_encode([
        "status" => false,
        "error" => "Database contains no tables."
    ]));
}

$result = [
    "status" => true,
    "database" => $dbName,
    "tables_checked" => count($tables),
    "issues" => [],
];

// =============================
// 5. ANALYZE EACH TABLE
// =============================
foreach ($tables as $table) {

    $indexQuery = "
        SELECT
            COLUMN_NAME,
            NON_UNIQUE,
            INDEX_NAME
        FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = :db
          AND TABLE_NAME = :tbl
        ORDER BY INDEX_NAME
    ";

    $indexes = $pdo->prepare($indexQuery);
    $indexes->execute([
        "db" => $dbName,
        "tbl" => $table
    ]);

    $uniqueColumns = [];

    foreach ($indexes as $idx) {
        if ($idx['NON_UNIQUE'] == 0) { // 0 = UNIQUE or PRIMARY
            $uniqueColumns[$idx['INDEX_NAME']][] = $idx['COLUMN_NAME'];
        }
    }

    if (empty($uniqueColumns)) continue;

    foreach ($uniqueColumns as $indexName => $cols) {

        $colList = implode("`, `", $cols);

        $dupQuery = "
            SELECT `$colList`, COUNT(*) AS cnt
            FROM `$table`
            GROUP BY `$colList`
            HAVING cnt > 1
        ";

        try {
            $duplicates = $pdo->query($dupQuery)->fetchAll();
        } catch (Exception $e) {
            $result['issues'][] = [
                "table" => $table,
                "index" => $indexName,
                "columns" => $cols,
                "error" => "Query failed: " . $e->getMessage()
            ];
            continue;
        }

        if (!empty($duplicates)) {
            $result['issues'][] = [
                "table" => $table,
                "index" => $indexName,
                "columns" => $cols,
                "duplicates" => $duplicates
            ];
        }
    }
}

// =============================
// 6. OUTPUT RESULTS
// =============================
header("Content-Type: application/json");
echo json_encode($result, JSON_PRETTY_PRINT);
exit;