Converting database backup code to PDO

Beginners - Learning PHP
#1

Hello,

Below is a section of the backup code
I said turn it into PDO but I couldn’t,

Optimize tables before backup
Lock table before backup
Features

Notice: Undefined offset: 2 in if ($status[2] == ‘error’)

can you help me

if($_POST['optimize'] == 1){

		// Remove Ending of LockList
	   $lock_write = rtrim($lock_write,",").';';
    
    // Lock Tables
		if ($_POST['lock'] == 1)
		{
			$PDOdatabase->query($lock_write);
		}
    if($_POST['combine'] == 3)
    {
      $tables = $_POST['tablolar'];
    }
    
        // Loop Tables       
		foreach ($tables AS $table){
            $error = 0;
            $error2 = 1;
            $optimize = 1;

			// Check Table
			$check = $PDOdatabase->query("CHECK TABLE `$table`");
			while ($status = $check->fetchAll()){
				// Status
				if ($status[2] == 'error')
				{
					if ($status[3] == 'The handler for the table doesn\'t support check/repair')
					{
                        $optimize = 0;
					}
					else
					{
                        $error = 1;
					}
				}
			}

			// Check Table Error
			if ($error)
			{
				// Repair Table
				$repair = $PDOdatabase->query("REPAIR TABLE `$table`");

                // Status
				if ($repair[3] != 'OK')
				{
                    $error2 = 1;
				}
				else
				{
					$error2 = 0;
					$error = 0;
				}
			}

			// Check Optimize
			if (!$error && !$error2 && $optimize)
			{
				// Optimize Table
				$optimize = $PDOdatabase->query("OPTIMIZE TABLE `$table`");
				while ($status = $optimize->fetchAll())
				{
					// Status
					if ($status[2] == 'error')
					{
						$error = 1;
					}
				}
			}
		}
    // Unlock Tables
		if ($_POST['lock'] == 1)
		{
			$PDOdatabase->query('UNLOCK TABLES;');
		}       
} // if($_POST['optimize'] == 1){

		// Remove Ending of LockList
		 $lock_read = rtrim($lock_read,",").';';
    
    // Lock Tables
		if ($_POST['lock'] == 1)
		{
			$PDOdatabase->query($lock_read);
		}
#2

I see, Adem, that you posted this on other forums. Did you get an answer on any of them?

It appears that you run a query “OPTIMIZE TABLE tablename” and I tested that on one of my test tables.
It returned this information:

Table Op Msg_type Msg_text
test.table1 optimize note Table does not support optimize, doing recreate + …
test.table1 optimize status OK

As you see, it returns 2 rows of data. And, the [2] values are “note” and “status”.
Therefore, you would need to read the last record and look for the [3] value to see if it is “OK” or not.

I am not sure how you tested this, but, you can run an optimize table command in the admin panel for testing and reviewing what is returned. The items returned vary due to database engines, so your results may be different than the ones I posted.

Hope this helps.

#3

I got no answer from anyone.

Then this method is not correct?, So, optimizing the database with php and locking tables before backing up

Not necessarily this code
Optimizing tables before database backup with PHP and optional use of locking tables before backup

#4

No, you are correct. The best system is to lock the table, optimize it, back it up and then unlock it.
In that way, you protect it from being changed during the optimize/backup steps.

What I was talking about was the way you look for the value of the results to see if there was an error or not when you optimize it. In my test system, it shows the status on the second row of data returned from the OPTIMIZE TABLE command. You should run this command in your phpMyAdmin panel and see what your system returns as data. Then, check for the status in the correct row. I did not test your code in my panel, but, it appears it will not check the status correctly. Have you tested it and did it show the status as “OK”?

#5

I’ve seen the results from phpMyAdmin, but I don’t know what to do inside the backup code

I love this backup code
There are many options

Backup all
Backup up tables separately into a folder
Backup up selected tables
Backup up selected tables in fused or in folder separately
Provides very large database backups
No time out

Full backup code
Code is running, backing up
It only gives an error message when optimize and lock is active,
but it’s still backing up

<?php
###############################################################################
/*
// POST data sent for backup
Array
(
    [grup] => 1
    [prefix] => Full // option
    [gz] => 0
    [optimize] => 0
    [lock] => 0
    [combine] => 1
)
*/
###############################################################################
@ini_set('memory_limit', '-1');
@ignore_user_abort(1);
@ini_set("magic_quotes_runtime", 0);
@ini_set('max_execution_time', 60*60*24);

header('Content-Type: text/html; charset=utf-8');
require_once('includes/pdodatabase.php');
require_once("includes/localdays.php");
$tarih = datetime;
################################################################################
if(!file_exists(BACKUPDIR)){
    if (!mkdir(BACKUPDIR, 0777, true)) {
        die('Failed to create folders...');
    }
}
$content = 'deny from all';
$file = new SplFileObject(BACKUPDIR . '/.htaccess', "w") ;
$file->fwrite($content);
################################################################################
if(!empty($_POST['prefix'])){
$prefix=strtolower($_POST['prefix'])."-";
$tarih_prefix = ucfirst($prefix).$tarih;
}else{
$tarih_prefix = datetime;    
}
################################################################################
$tables = array();
################################################################################
if($_POST['combine']=='1'){  // Tam tek dosya olarak yedekleme
$tabloadi='-Tam'; 
$tables = '*';
}
################################################################################
if($_POST['combine']=='2'){  // Tabloları Ayrı Ayrı yedekleme
$tables = '*';
define('SUBBACKUPDIR', './'.BACKUPDIR.'/'.$tarih_prefix ) ;
if(!file_exists(SUBBACKUPDIR)){
    if (!mkdir(SUBBACKUPDIR, 0777, true)) {
        die('Failed to create folders...');
    }
}
$content = 'deny from all';
$file = new SplFileObject(SUBBACKUPDIR . '/.htaccess', "w") ;
$file->fwrite($content) ;
}
################################################################################
if($_POST['combine']=='3' AND $_POST['elle']=='1'){  // Tabloları elle seçme
$toplam_tablo = count($_POST['tablolar']);
if($toplam_tablo==1){
$tables = $_POST['tablolar'];
sort($tables);
$tabloadi="-".ucfirst($tables[0]);
}else{
$tables = $_POST['tablolar'];
sort($tables);
$tabloadi='-Elle';
}
}
################################################################################
if($_POST['combine']=='3' AND $_POST['elle']=='2'){  // Tabloları elle seçme
define('SUBBACKUPDIR', './'.BACKUPDIR.'/'.$tarih_prefix ) ;
if(!file_exists(SUBBACKUPDIR)){
    if (!mkdir(SUBBACKUPDIR, 0777, true)) {
        die('Failed to create folders...');
    }
}
$content = 'deny from all';
$file = new SplFileObject(SUBBACKUPDIR . '/.htaccess', "w") ;
$file->fwrite($content) ;
$tables = $_POST['tablolar'];
sort($tables);
}
################################################################################  
if($_POST['grup']=='1'){

$return=null;

$mysql_version = $PDOdatabase->query('select version()')->fetchColumn();
$mysqlcharacter = $PDOdatabase->query("SHOW VARIABLES LIKE 'character_set_connection'");
$mysql_character = $mysqlcharacter->fetchColumn(1);
  
$return .= "\n-- Karakter Seti: ".$mysql_character."\n";
$return .= "-- PHP Sürümü: ".phpversion()."\n";
$return .= "-- Sunucu sürümü: ".$mysql_version."\n";
$return .= "-- Anamakine: ".$_SERVER['HTTP_HOST']."\n";
$return .= '-- Üretim Zamanı: ' . strftime("%d %B %Y, %H:%M:%S", time()) . "\n";
$return .= "-- Veritabanı: " . $db_name . "\n";
$return .= "--\n";
$return .= "-- --------------------------------------------------------\n";
$return .= 'SET AUTOCOMMIT = 0 ;' ."\n" ;
$return .= 'SET FOREIGN_KEY_CHECKS=0 ;';
$return .="\n-- --------------------------------------------------------\n";
$lock_write = 'LOCK TABLES';
$lock_read = 'LOCK TABLES';
// BASE TABLE SAVE  
// get all of the tables 

if($tables == '*'){

$tables = array();

$result = $PDOdatabase->query("SHOW TABLES");

while($row = $result->fetch(PDO::FETCH_NUM)){
$tables[] = $row[0];
$lock_write .= ' '.$row[0].' WRITE,';
$lock_read .= ' '.$row[0].' READ,';
}
}else{
$tables = is_array($tables) ? $tables : explode(',',$tables);

foreach ($tables AS $table){
$lock_write .= ' '.$table.' WRITE,';
$lock_read .= ' '.$table.' READ,';
}

}
$tablosayisi = count($tables);
    
// Yedek almadan önce onarma ve optimize etme
if($_POST['optimize']=='1'){

		// Remove Ending of LockList
	   $lock_write = rtrim($lock_write,",").';';
    
    // Lock Tables
		if ($_POST['lock']=='1')
		{
			$PDOdatabase->query($lock_write);
		}
    if($_POST['combine']=='3')
    {
      $tables = $_POST['tablolar'];
    }
    
        // Loop Tables       
		foreach ($tables AS $table){
            $error = 0;
            $error2 = 1;
            $optimize = 1;

			// Check Table
			$check = $PDOdatabase->query("CHECK TABLE `$table`");
			while ($status = $check->fetchAll()){
				// Status
				if ($status[2] == 'error')
				{
					if ($status[3] == 'The handler for the table doesn\'t support check/repair')
					{
                        $optimize = 0;
					}
					else
					{
                        $error = 1;
					}
				}
			}

			// Check Table Error
			if ($error)
			{
				// Repair Table
				$repair = $PDOdatabase->query("REPAIR TABLE `$table`");

                // Status
				if ($repair[3] != 'OK')
				{
                    $error2 = 1;
				}
				else
				{
					$error2 = 0;
					$error = 0;
				}
			}

			// Check Optimize
			if (!$error && !$error2 && $optimize)
			{
				// Optimize Table
				$optimize = $PDOdatabase->query("OPTIMIZE TABLE `$table`");
				while ($status = $optimize->fetchAll())
				{
					// Status
					if ($status[2] == 'error')
					{
						$error = 1;
					}
				}
			}
		}
    // Unlock Tables
		if ($_POST['lock']=='1')
		{
			$PDOdatabase->query('UNLOCK TABLES;');
		}       
}//if($_POST['optimize']=='1'){

		// Remove Ending of LockList
		 $lock_read = rtrim($lock_read,",").';';
    
    // Lock Tables
		if ($_POST['lock']=='1')
		{
			$PDOdatabase->query($lock_read);
		}

//cycle through
$t = 0;
foreach($tables as $table){
$t++; 
$sutun_sayisi = $PDOdatabase->query('SELECT * FROM '.$table);
$num_fields =  $sutun_sayisi->columnCount();
$numrow = $sutun_sayisi->rowCount();

################################################################################

// Tabloları ayrı ayrı yemekleme yolu ve dosya adı
if($_POST['combine']=='2' OR $_POST['combine']=='3' AND $_POST['elle']=='2'){
$handle = fopen(SUBBACKUPDIR.'/'.trim(ucfirst($table)).'.sql','a');
// GZip için dosya yolu ve dosya adı
$dosya = SUBBACKUPDIR.'/'.trim(ucfirst($table)).'.sql';
}
// Tek dosyada yedekleme yolu ve dosya adı
if($_POST['combine']=='1' OR $_POST['combine']=='3' AND $_POST['elle']!='2'){
$handle = fopen('./'.BACKUPDIR.'/'.$tarih_prefix.$tabloadi.'.sql','a');
// GZip için dosya yolu ve dosya adı
$dosya = './'.BACKUPDIR.'/'.$tarih_prefix.$tabloadi.'.sql';
}

################################################################################

$return .= "--\n" ;
$return .= '-- Tablonun yapısı `' . $table . '`' . "\n" ;
$return .= "--\n\n";

$return .= 'DROP TABLE IF EXISTS '.$table.';';

$pstm2 = $PDOdatabase->query('SHOW CREATE TABLE '.$table);
$row2 = $pstm2->fetch(PDO::FETCH_NUM);
$ifnotexists = str_replace('CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', $row2[1]);

$return .= "\n".$ifnotexists.";\n";
$return .= "\n--\n" ;
$return .= '-- Tablonun veri dökümü `' . $table . '`' . "\n" ;
$return .= "--\n\n" ;

    for ($i = 0; $i < $num_fields; $i++) {

        @set_time_limit(0);
        $s = 0;
        while ($row = $sutun_sayisi->fetch(PDO::FETCH_NUM)) {
        $s++;
            $return .= 'INSERT INTO `' . trim($table) . '` VALUES(';
            for ($j = 0; $j < $num_fields; $j++) {
                $row[$j] = addslashes($row[$j]);
                $row[$j] = preg_replace("/\r\n/", "\\r\\n", $row[$j]);
                if (isset($row[$j])) {
                    $return .= "'" . $row[$j] . "'";
                } else {
                    $return .= "''";
                }
                if ($j < ($num_fields - 1)) {
                    $return .= ',';
                }
            }
            $return .= ");\n";
            // Tablo verinin dökümün altına ekler
            if ( $s == ( $numrow - 0 ) ){
            $return .="\n-- --------------------------------------------------------\n";
            }
            // Okunan veriyi dosyaya yaz
            fwrite($handle, $return);
            $return=null;
}// end while
            // Tabloda veri yoksa tablo yapısını dosyaya yaz
            $satir = $sutun_sayisi->fetch(PDO::FETCH_NUM);
            if(!$satir[0]){
            fwrite($handle, $return);
            $return=null;        
            }       
}//end for
            // Dosyanın en sonuna ekle
            if ( $t == ( $tablosayisi - 0 ) AND $tablosayisi > 1){
            $return .= "\n";
            $return .= 'SET FOREIGN_KEY_CHECKS = 1 ; '  . "\n" ; 
            $return .= 'COMMIT ; '  . "\n" ;
            $return .= 'SET AUTOCOMMIT = 1 ; ' . "\n"  ;
            fwrite($handle, $return);
            $return=null;
            }
// Açılmış dosyayı kapat            
if($_POST['gz']=='0'){fclose($handle);}            
            // Tablo Tablo yedekleri GZip ile sıkıştırır
            if($_POST['gz']=='1' AND ($_POST['combine']=='2' OR @$_POST['elle']=='2')){
            fclose($handle);
            $input = $dosya;
            $output = $input.".gz";
            $basarili = file_put_contents("compress.zlib://$output", file_get_contents($input));                        
            if($basarili){@unlink($dosya);}
            } 
}//foreach($tables as $table){
            // Tek dosyada yedeği GZip ile sıkıştırır
            if($handle AND $_POST['gz']=='1' AND ($_POST['combine']=='1' OR @$_POST['elle']=='1')){
            fclose($handle);
            $input = $dosya;
            $output = $input.".gz";
            $basarili = file_put_contents("compress.zlib://$output", file_get_contents($input));                        
            if($basarili){@unlink($dosya);}
            }

// Yedekleme sırasında Tablolar kilitlendi ise kilitleri açar
if ($_POST['lock'] == 1){
    $PDOdatabase->query('UNLOCK TABLES;');
unset($lock_write,$lock_read);
}

if($handle != "" OR $basarili){
echo 'Veritabanı Başarıyla Yedeklendi.';
}else{
echo 'Veritabanı Bir Hatadan Dolayı Yedeklenemedi.';
}

}//if($_POST['grup']=='1'){
?>
#6

I did not study all of it, but, it appears to be a very complete backup system. I normally have a more simple routine. Unless it is a very large database. I usually use code like this which will back it all up to a file and then force it to start downloading. I put it into a ADMIN page so that the administer can back it up whenever he feels he needs to. You code is much more of a full system.

if (isset($_POST["backup_database"])) {  // A button for ADMIN to select
    //  First, erase any old backups as those are no longer needed...
    if (file_exists("*.sql")) { unlink("*.sql"); }
	//  Create a new full backujp
	$filename='Name of the backupfile - '.date('Y-m-d').'.sql';
	$result=exec('mysqldump DATABASENAME -uUSERNAME -pPASSWORD > ' . $filename);
    //  We now have a full backup of the database, start a download of the file to the local system...
	header('Content-Type: application/download');
	header('Content-Disposition: attachment; filename=' . $filename);
	header("Content-Length: " . filesize($filename));
	$fp = fopen($filename, "r");
	fpassthru($fp);
	fclose($fp);
}

It does not use the mysqli or pdo, it just sends the command directly to the database system directly.
I attach it to a button for the admin to press. You could alter the “mysqldump” command to use just one table if needed something like this to select just one table:
$result=exec('mysqldump -uUSERNAME -pPASSWORD DATABASENAME TABLENAME > ’ . $filename);
But, your code is more of a full backup library or system. Both work well for different needs.

#7

@ErnieAlex, Thank you, but I love my backup code

I solved the problem

while ($status = $check->fetchAll()){

while ($status = $optimize->fetchAll())

change to
fetch(PPDO::FETCH_BOTH)

Now he does not give an error

If the table gives an error, does it correspond to the following?
if ($status[2] == ‘error’)
if ($status[3] == ‘The handler for the table doesn’t support check/repair’)

#8

I did not see that! Nice you found it.

Well, I think it returns two rows of data. You would need to check the $status[2] for “status” and then, check $status[3] for “ok”. Or, you can look for a status NOT “ok” to indicate an error.

Glad you fixed it.

Sponsor our Newsletter | Privacy Policy | Terms of Service