PHP read json files and Mysql Database operations on data

gistfile1.txt

<?php
/**
 * DB config — change these
 */
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);
const DB_HOST = '127.0.0.1';
const DB_PORT = 3306;
const DB_NAME = 'db_name';
const DB_USER = 'db_user';
const DB_PASS = 'xxxxxx';
const DB_CHARSET = 'utf8mb4';
function readFilesFromFolder($folderPath) {
    // Check if the folder exists
    if (!is_dir($folderPath)) {
        return ["error" => "Folder does not exist."];
    }

    // Scan the folder
    $files = scandir($folderPath);

    // Filter out . and ..
    $files = array_diff($files, ['.', '..']);

    // Optionally filter to only include files (exclude subfolders)
    $files = array_filter($files, function($file) use ($folderPath) {
        return is_file($folderPath . DIRECTORY_SEPARATOR . $file);
    });

    // Re-index array and return
    return array_values($files);
}

/**
 * Get a shared PDO instance
 */
function db(): PDO {
    static $pdo = null;
    if ($pdo) return $pdo;

    $dsn = "mysql:host=".DB_HOST.";port=".DB_PORT.";dbname=".DB_NAME.";charset=".DB_CHARSET;
    $opts = [
        PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];
    return $pdo = new PDO($dsn, DB_USER, DB_PASS, $opts);
}

/**
 * Very small identifier validator (table/column names).
 * Only allows letters, numbers, and underscores.
 */
function id_ok(string $id): bool {
    return (bool) preg_match('/^[A-Za-z0-9_]+$/', $id);
}

/**
 * INSERT: $data = ['col' => 'val', ...]
 * Returns last insert id.
 */
function db_insert(string $table, array $data): string {
    if (!id_ok($table) || empty($data)) throw new InvalidArgumentException("Bad table or empty data");

    // Validate columns
    foreach (array_keys($data) as $col) {
        if (!id_ok($col)) throw new InvalidArgumentException("Bad column: $col");
    }

    $cols = array_keys($data);
    $place = array_map(fn($c) => ":$c", $cols);

    $sql = "INSERT INTO `$table` (`" . implode("`,`", $cols) . "`) VALUES (" . implode(",", $place) . ")";
    $stmt = db()->prepare($sql);
    $stmt->execute($data);
    return db()->lastInsertId();
}

/**
 * SELECT:
 * - $columns: ['*'] or ['id','name']
 * - $where: ['id' => 5, 'status' => 'active']  (AND logic)
 * - $opts: ['order' => 'id DESC', 'limit' => 50, 'offset' => 0]
 */
function db_select(string $table, array $columns = ['*'], array $where = [], array $opts = []): array {
    if (!id_ok($table)) throw new InvalidArgumentException("Bad table");

    // Validate columns (unless '*')
    if (!($columns === ['*'])) {
        foreach ($columns as $c) if (!id_ok($c)) throw new InvalidArgumentException("Bad column: $c");
    }

    $colsSql = ($columns === ['*']) ? '*' : ('`' . implode('`,`', $columns) . '`');

    $params = [];
    $whereSql = '';
    if ($where) {
        $pairs = [];
        foreach ($where as $col => $val) {
            if (!id_ok($col)) throw new InvalidArgumentException("Bad where column: $col");
            $param = "w_$col";
            $pairs[] = "`$col` = :$param";
            $params[$param] = $val;
        }
        $whereSql = " WHERE " . implode(' AND ', $pairs);
    }

    $order = '';
    if (!empty($opts['order'])) {
        // VERY light check; for stricter safety, build an allowlist
        if (!preg_match('/^[A-Za-z0-9_`,\s]+(ASC|DESC)?(\s*,\s*[A-Za-z0-9_`]+\s+(ASC|DESC))*$/i', $opts['order'])) {
            throw new InvalidArgumentException("Bad order clause");
        }
        $order = " ORDER BY " . $opts['order'];
    }

    $limit = '';
    if (isset($opts['limit'])) {
        $limit = " LIMIT " . (int)$opts['limit'];
        if (isset($opts['offset'])) $limit .= " OFFSET " . (int)$opts['offset'];
    }

    $sql = "SELECT $colsSql FROM `$table`$whereSql$order$limit";
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->fetchAll();
}

/**
 * UPDATE:
 * - $data: columns to update
 * - $where: filters (AND)
 * Returns affected rows.
 */
function db_update(string $table, array $data, array $where): int {
    if (!id_ok($table) || empty($data) || empty($where)) {
        throw new InvalidArgumentException("Bad table / empty data / empty where");
    }

    foreach (array_keys($data) as $c) if (!id_ok($c)) throw new InvalidArgumentException("Bad column: $c");
    foreach (array_keys($where) as $c) if (!id_ok($c)) throw new InvalidArgumentException("Bad where column: $c");

    $setParts = [];
    $params = [];
    foreach ($data as $col => $val) {
        $p = "s_$col";
        $setParts[] = "`$col` = :$p";
        $params[$p] = $val;
    }

    $whereParts = [];
    foreach ($where as $col => $val) {
        $p = "w_$col";
        $whereParts[] = "`$col` = :$p";
        $params[$p] = $val;
    }

    $sql = "UPDATE `$table` SET " . implode(', ', $setParts) . " WHERE " . implode(' AND ', $whereParts);
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt->rowCount();
}


// Example usage
$folderPath = __DIR__ . "/upload"; // Change to your folder path
$fileList = readFilesFromFolder($folderPath);
$index = $_GET['page'] ?? 0;
 echo "Processing files....";
foreach($fileList as $key => $file) {
    if($key < $index) {
            continue; // Skip to the next iteration if the index is less than the current key
    }
    if($key < count($fileList) - 1) {
            header("Refresh: 2;?page=" . ($key + 1)); // Refresh the page every 2 seconds with the next index
            exit;
    }
}
 echo "All files processed successfully.";

/* ----------------- Example usage ----------------- */
// Insert
// $id = db_insert('users', ['name' => 'Gulshan', 'email' => '[email protected]']);

// Select
// $rows = db_select('users', ['id','name','email'], ['email' => '[email protected]'], ['order' => 'id DESC', 'limit' => 10]);

// Update
// $count = db_update('users', ['name' => 'G. Sharma'], ['id' => 1]);