Creating MySQL database backups with date range filtering in PHP (for specific months)

Other Questions MySQL
1

I have a working class that creates MySQL database backups, but I need to modify it to only backup data from a specific time range. For example, I want to backup only records from the last 6 months instead of the entire database. Can someone help me update this code to add date filtering?

class MySQLExporter {
    private $hostname;
    private $username;
    private $passwd;
    private $database;
    private $connection;
    private $tableList = array();
    private $sqlOutput;
    private $errors = array();
    
    public function __construct($config) {
        $this->hostname = $config['host'];
        $this->username = $config['user'];
        $this->passwd = $config['pass'];
        $this->database = $config['db'];
        
        $this->establishConnection();
        $this->fetchTables();
        $this->buildSQL();
    }
    
    public function export() {
        if(count($this->errors) > 0) {
            return array('success' => false, 'errors' => $this->errors);
        }
        return array('success' => true, 'data' => $this->sqlOutput);
    }
    
    private function establishConnection() {
        try {
            $dsn = "mysql:host={$this->hostname};dbname={$this->database}";
            $this->connection = new PDO($dsn, $this->username, $this->passwd);
        } catch (PDOException $ex) {
            $this->errors[] = $ex->getMessage();
        }
    }
    
    private function fetchTables() {
        $query = $this->connection->query('SHOW TABLES');
        $results = $query->fetchAll();
        
        foreach($results as $tbl) {
            $tableName = $tbl[0];
            $this->tableList[] = array(
                'table' => $tableName,
                'structure' => $this->getTableStructure($tableName),
                'records' => $this->getTableData($tableName)
            );
        }
    }
    
    private function getTableStructure($table) {
        $query = $this->connection->query("SHOW CREATE TABLE {$table}");
        $result = $query->fetch();
        return $result[1];
    }
    
    private function getTableData($table) {
        $query = $this->connection->query("SELECT * FROM {$table}");
        $rows = $query->fetchAll(PDO::FETCH_NUM);
        
        $insertSQL = '';
        foreach($rows as $row) {
            $values = array_map(function($val) {
                return addslashes($val);
            }, $row);
            $insertSQL .= "INSERT INTO {$table} VALUES ('" . implode("','", $values) . "');\n";
        }
        return $insertSQL;
    }
}

How can I modify the getTableData method to include a WHERE clause that filters records by date? I want to be able to specify a time period like the last 6 months.

2

The main issue is null values breaking when you add date filtering. I hit this same problem doing filtered backups for client data - the addslashes() approach fails hard with null datetime fields once filtering kicks in.

Fix your value mapping in getTableData with proper null handling:

private function getTableData($table, $dateColumn = null, $monthsBack = 6) {
    $sql = "SELECT * FROM {$table}";
    if ($dateColumn) {
        $sql .= " WHERE {$dateColumn} >= DATE_SUB(NOW(), INTERVAL {$monthsBack} MONTH)";
    }
    
    $query = $this->connection->query($sql);
    $rows = $query->fetchAll(PDO::FETCH_NUM);
    
    $insertSQL = '';
    foreach($rows as $row) {
        $values = array_map(function($val) {
            return $val === null ? 'NULL' : "'" . addslashes($val) . "'";
        }, $row);
        $insertSQL .= "INSERT INTO {$table} VALUES (" . implode(",", $values) . ");\n";
    }
    return $insertSQL;
}

This stops SQL syntax errors when your filtered results have null timestamps or dates. The original code wraps everything in quotes, which completely breaks null values.

3

You’ll need to modify your getTableData method to accept date filtering parameters and build WHERE clauses dynamically. The tricky part is figuring out which tables actually have date/datetime columns first. I hit this same problem backing up our order system monthly. I added a method to detect date columns automatically, then only apply filters where they exist. Here’s how to extend your approach: php private function getTableData($table, $dateFilter = null) { $sql = "SELECT * FROM {$table}"; if ($dateFilter && $this->hasDateColumn($table)) { $dateColumn = $this->getDateColumn($table); $sql .= " WHERE {$dateColumn} >= '{$dateFilter['start']}' AND {$dateColumn} <= '{$dateFilter['end']}'"; } $query = $this->connection->query($sql); // rest of your existing code } private function hasDateColumn($table) { $query = $this->connection->query("DESCRIBE {$table}"); $columns = $query->fetchAll(); foreach($columns as $col) { if (in_array(strtolower($col['Type']), ['datetime', 'date', 'timestamp'])) { return true; } } return false; } Just pass date parameters when calling the method. This avoids errors on tables without date columns while still getting your filtered backup.

4

honestly i’d just add a date range parameter to the constructor and modify getTableData to check for common date column names. something like $this->dateRange = $dateRange; in constructor, then in getTableData: $dateColumns = ['created_at', 'updated_at', 'date_created', 'timestamp']; and loop thru to see which one exists with DESCRIBE {$table}. if found, append WHERE {$foundColumn} >= DATE_SUB(NOW(), INTERVAL 6 MONTH) to your select query. way simpler than complex configs and handles 90% of cases without overthinking it.

5

Manual date filtering is a nightmare with different table structures and date column types. I’ve watched too many backup scripts die when someone adds a table or changes a timestamp field.

Skip modifying that PHP code. Automate the entire backup with built-in date filtering. Set up a workflow that connects to MySQL, finds all tables automatically, spots date columns, and applies your 6-month filter everywhere.

Best part? Schedule it monthly, weekly, whatever you need. No more PHP class maintenance or debugging weird date format edge cases.

I switched after wasting hours on manual script debugging. Now it handles new table detection and applies consistent date filters across our whole database.

Bonus: backup to different locations, compress files, get notifications on success/failure. Way more reliable than manual PHP scripts.

6

Your main issue is that different tables have different date column names and types. I fixed this by adding a config parameter to the constructor that maps each table to its date column. Way more control than trying to auto-detect everything.

Update your constructor to accept date filtering config:

public function __construct($config, $dateConfig = null) {
    // existing code
    $this->dateConfig = $dateConfig;
}

Then modify your getTableData method:

private function getTableData($table) {
    $sql = "SELECT * FROM {$table}";
    
    if ($this->dateConfig && isset($this->dateConfig['tables'][$table])) {
        $dateColumn = $this->dateConfig['tables'][$table]['column'];
        $startDate = $this->dateConfig['start_date'];
        $endDate = $this->dateConfig['end_date'];
        $sql .= " WHERE {$dateColumn} BETWEEN '{$startDate}' AND '{$endDate}'";
    }
    
    $query = $this->connection->query($sql);
    // rest of existing code
}

For 6-month filtering, pass config like ['tables' => ['orders' => ['column' => 'created_at'], 'users' => ['column' => 'registration_date']], 'start_date' => '2024-01-01', 'end_date' => '2024-06-30']. Works with mixed table structures and won’t break tables that don’t need date filtering.