PHP PDO Database Back-up Class

Question

I am building a database back-up class using PHP and PDO, but unfortunately I ran into some problems.

example class that I found

I get multiple error messages saying that the offset is not correct in my second for loop. Also when I use print_r on $sql I get the following result:

Backing up tbl_students table...

CREATE DATABASE IF NOT EXISTS plansysteem_keuzetrainingen;

USE plansysteem_keuzetrainingen;

DROP TABLE IF EXISTS tbl_students;

CREATE TABLE `tbl_students` (
  `idStudent` int(11) NOT NULL AUTO_INCREMENT,
  `studentUsualName` varchar(300) NOT NULL,
  `studentPrefix` varchar(200) DEFAULT NULL,
  `studentSurname` varchar(300) NOT NULL,
  `studentNumber` int(11) NOT NULL,
  `studentClass` varchar(300) NOT NULL,
  `studentEmailAddress` varchar(300) NOT NULL,
  PRIMARY KEY (`idStudent`)
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8mb4;

INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();

Backing up tbl_users table...

CREATE DATABASE IF NOT EXISTS plansysteem_keuzetrainingen;

USE plansysteem_keuzetrainingen;

DROP TABLE IF EXISTS tbl_students;

CREATE TABLE `tbl_students` (
  `idStudent` int(11) NOT NULL AUTO_INCREMENT,
  `studentUsualName` varchar(300) NOT NULL,
  `studentPrefix` varchar(200) DEFAULT NULL,
  `studentSurname` varchar(300) NOT NULL,
  `studentNumber` int(11) NOT NULL,
  `studentClass` varchar(300) NOT NULL,
  `studentEmailAddress` varchar(300) NOT NULL,
  PRIMARY KEY (`idStudent`)
) ENGINE=InnoDB AUTO_INCREMENT=261 DEFAULT CHARSET=utf8mb4;

INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();
INSERT INTO tbl_students VALUES();


DROP TABLE IF EXISTS tbl_users;

CREATE TABLE `tbl_users` (
  `idUser` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(300) NOT NULL,
  `userPassWord` varchar(300) NOT NULL,
  `userPassWordHash` varchar(300) NOT NULL,
  `userEmailAddress` varchar(300) NOT NULL,
  PRIMARY KEY (`idUser`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

INSERT INTO tbl_users VALUES();

I get the first table from my database twice, and I can't figure out why because my database only contains two tables tbl_students & tbl_users. Below you can find my database back-up class.

DatabaseBackup.class.php

<?php
    class DatabaseBackup {
        private $dbConnection;
        private $dbName;

        public function __construct($dbConnection, $dbName) {
            $this->dbConnection = $dbConnection->dbConnection;
            $this->dbName = $dbName;
        }

        public function backupDbTables($dbTables = "*", $outputDir = ".") {
            if($dbTables == "*") {
                $dbTables = array();
                $firstResult = $this->dbConnection->query("SHOW TABLES");
                while($firstResultRow = $firstResult->fetch(PDO::FETCH_BOTH)) {
                    $dbTables[] = $firstResultRow[0];
                }
            }
            else {
                $dbTables = is_array($dbTables) ? $dbTables : explode(',',$dbTables);
            }

            $sql = 'CREATE DATABASE IF NOT EXISTS '.$this->dbName.";\n\n";
            $sql .= 'USE '.$this->dbName.";\n\n";

            foreach($dbTables as $dbTable) {
                echo "Backing up ".$dbTable." table...";

                $selectNumFields = $this->dbConnection->query("SELECT COUNT(*) FROM ".$dbTable);
                $numFieldsArray = $selectNumFields->fetchColumn();
                $numFields = $numFieldsArray;

                $secondResult = $this->dbConnection->query("SELECT * FROM ".$dbTable);

                $sql .= 'DROP TABLE IF EXISTS '.$dbTable.';';
                $thirdResult = $this->dbConnection->query("SHOW CREATE TABLE ".$dbTable);
                $thirdResultRows = $thirdResult->fetch(PDO::FETCH_NUM);
                $sql.= "\n\n".$thirdResultRows[1].";\n\n";

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

                    while($row = $secondResult->fetch(PDO::FETCH_NUM)) {

                        $sql .= 'INSERT INTO '.$dbTable.' VALUES(';

                        for($j = 0; $j < $numFields; $j++) {
                            $row[$j] = addslashes($row[$j]);
                            $row[$j] = str_replace("\n","\\n",$row[$j]);
                            if(isset($row[$j])) {
                                $sql .= '"'.$row[$j].'"' ;
                            }
                            else {
                                $sql.= '""';
                            }
                            if($j < ($numFields-1)) {
                                $sql .= ',';
                            }
                        }

                        $sql.= ");\n";

                    }

                }

                $sql.="\n\n\n";
                echo "<pre>";
                print_r($sql);
                echo "</pre>";   
            }
        }

        public function saveBackUpFile() {

        }
    }
?>

I hope someone can help me fix this class so that it works properly, and if you have any suggestions for my code I would like to hear them.

Answer 1

You must check existence of the element in array before accessing it!

So you need to rewrite you cycle like this:

for($j = 0; $j < $numFields; $j++) {
    if(isset($row[$j])) {
        $row[$j] = addslashes($row[$j]);
        $row[$j] = str_replace("\n","\\n",$row[$j]);
        $sql .= '"'.$row[$j].'"' ;
    } else {
        $sql.= '""';
    }
    if($j < ($numFields-1)) {
        $sql .= ',';
    }
}