<?php
require_once('../Db/dbconfig.php');
$tables = array();
$sql = "SHOW TABLES";
$result = mysqli_query($con, $sql);
while ($row = mysqli_fetch_row($result)) {
    $tables[] = $row[0];
}
$sqlScript = "";
foreach ($tables as $table) {
        // Prepare SQLscript for creating table structure
    $query = "SHOW CREATE TABLE $table";
    $result = mysqli_query($con, $query);
    $row = mysqli_fetch_row($result);
    $sqlScript .= "\n\n" . $row[1] . ";\n\n";
    $query = "SELECT * FROM $table";
    $result = mysqli_query($con, $query);
    $columnCount = mysqli_num_fields($result);
    // Prepare SQLscript for dumping data for each table
    for ($i = 0; $i < $columnCount; $i ++) {
        while ($row = mysqli_fetch_row($result)) {
            $sqlScript .= "INSERT INTO $table VALUES(";
            for ($j = 0; $j < $columnCount; $j ++) {
                /*$row[$j] = $row[$j];*/
                if (isset($row[$j])) {
                    if(is_string($row[$j])){
                        $sqlScript .= "'" . str_replace('"', '\"', $row[$j]) ."'";
                    }else{
                        $sqlScript .= $row[$j];
                    }
                } else {
                    $sqlScript .= 'NULL';
                }
                if ($j < ($columnCount - 1)) {
                    $sqlScript .= ',';
                }
            }
            $sqlScript .= ");\n";
        }
    }
    
    $sqlScript .= "\n"; 
}

if(!empty($sqlScript))
{
    // Save the SQL script to a backup file
    $targetdir = '../petrixv2_db_backup/';
    $backup_file_name = $targetdir.'backup_' . time() . '.sql';
    $fileHandler = fopen($backup_file_name, 'w+');
    $number_of_lines = fwrite($fileHandler, $sqlScript);
    fclose($fileHandler); 

   // Sort in descending order
    $files = preg_grep('/^([^.])/', scandir($targetdir,1));

    if(count($files) > 3){
        for($i = 3; $i < count($files);$i++){
            if (!unlink($targetdir.$files[$i])) { 
                echo ("$files[$i] cannot be deleted due to an error"); 
            } 
            else { 
                echo ("$files[$i] has been deleted"); 
            } 
        }
    }
}

//cron job command:-php -q /home/gugve8xqu7uo/public_html/controller/auto_backup_controller.php
?>