Database backup

gistfile1.php

        function backup_tables($host, $user, $pass, $db, $tables = '*')
        {
                set_time_limit(30);
            
                $mysqli = new mysqli($host,$user,$pass, $db);
                if ($mysqli->connect_errno)
                {
                    echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error;
                }
                
                $return = '';
                
                $return .= "--\n";
                $return .= "-- Database: `$db`\n";
                $return .= "--\n\n";
                $return .= "-- --------------------------------------------------------\n\n";

                $numtypes = array(
                    'tinyint', 
                    'smallint',
                    'mediumint',
                    'int',
                    'bigint',
                    'float',
                    'double',
                    'decimal',
                    'real'
                );

                // get all of the tables
                if ($tables == '*')
                {
                        $tables = array();
                        $result = $mysqli->query('SHOW TABLES');
                        while ($row = $result->fetch_row())
                        {
                                $tables[] = $row[0];
                        }

                        $result->close();
                }
                else
                {
                        $tables = is_array($tables) ? $tables : explode(',',$tables);
                }
                
                for ($z = 0; $z == 0; $z++)
                {
                    echo $z.'<br>';

                // cycle through tables
                foreach ($tables as $table)
                {
                        //
                        $typesarr = array();
                        $result = $mysqli->query("SHOW COLUMNS FROM `".$table."`");

                        while ($row = $result->fetch_assoc())
                        {
                                $typesarr[] = $row;
                        }
                        $result->close();

                        #echo '<h2>'.$table.'</h2>';
                        #print("<pre>" . print_r($typesarr, true). "</pre>");
                        
                        // table structure dump
                        $return .= "--\n";
                        $return .= "-- Table structure for table `$table`\n";
                        $return .= "--\n\n";                        
                        $return.= 'DROP TABLE IF EXISTS `'.$table.'`;'."\n\n";
                        $result = $mysqli->query("SHOW CREATE TABLE `".$table."`");
                        $row = $result->fetch_array();
                        $return.= $row[1].";\n\n";
                        $result->close();
                        
                        // table data dump
                        $return .= "--\n";
                        $return .= "-- Dumping data for table `$table`\n";
                        $return .= "--\n\n";
                        
                        $result = $mysqli->query("SELECT * FROM `".$table."`");
                        $num_fields = $result->field_count;
                        
                        if ($result->num_rows > 0)
                        {
                                // put field names in array and into sql insert for dump
                                $fields_str = '';
                                $fields =  array();
                                $finfo = $result->fetch_fields();
                                
                                foreach ($finfo as $val)
                                {
                                        $fields_str .= '`'.$val->name.'`, ';
                                        $fields[] = $val->name;
                                }                                

                                $fields_str = '('.rtrim($fields_str, ', ').')';
                                $return.= 'INSERT INTO `'.$table.'` '.$fields_str.' VALUES'."\n";
                                
                                // cycle through fields and check if int for later use
                                for ($i = 0; $i < $num_fields; $i++) 
                                {
                                        // strip brackets from type
                                        $acttype = trim(preg_replace('/\s*\([^)]*\)/', '', $typesarr[$i]['Type']));
                                        $acttype = explode(' ', $acttype);

                                        // build array, is field int or not
                                        if (is_numeric(array_search($acttype[0], $numtypes)))
                                        {
                                                $numflag[$i] = 1;
                                        }
                                        else
                                        {
                                                $numflag[$i] = 0;        
                                        }
                                }
                        }  

                        $x = 0;
                        $num_rows = $result->num_rows;

                        // cycle through table rows
                        while($row = $result->fetch_row())
                        {
                                $x++;

                                // cycle through rows fields
                                for($j=0; $j<$num_fields; $j++) 
                                {          
                                        if (isset($row[$j]) and $j === 0) { $return .= '('; }

                                        // field data has value or not NULL
                                        if (isset($row[$j]))
                                        { 
                                                // field data dump (INT)
                                                if ($numflag[$j]==1)
                                                {
                                                        #echo '(INT) '. $fields[$j].' = '.$row[$j].'<br>';
                                                        $return.= $mysqli->real_escape_string($row[$j]);
                                                } 
                                                else
                                                {
                                                        // field data dump values (empty string, NULL and INT)
                                                        $return.= "'".$mysqli->real_escape_string($row[$j])."'";
                                                        #echo $fields[$j]." = '".$mysqli->real_escape_string($row[$j])."'<br>";
                                                }
                                        }
                                        else
                                        {
                                                // field data dump (NULL)
                                                if (is_null($row[$j]))
                                                {
                                                        $row[$j] = 'NULL';
                                                        #echo '(NULL) '. $fields[$j].' = '.$row[$j].'<br>';
                                                        $return.= $row[$j]; 
                                                }
                                                else
                                                {
                                                        // field data dump (empty string)
                                                        $return.= "''";
                                                }
                                        }
                                        
                                        if ($j<($num_fields-1)) { $return.= ', '; }
                                }
                                
                                if ($x<$num_rows) { $return.= "),\n"; } else { $return .= ");\n"; }

                                #echo '<br>';
                        }
                        #echo 'Rows: '.$rows.'<br>';
                        #echo 'Iterations: '.$x.'<br>';
                        $return.="\n-- --------------------------------------------------------\n\n";
                }
                
                }

                $result->close();

                //save file
                $handle = fopen('/db-backup-'.time().'.sql','a');
                fwrite($handle,$return);
                fclose($handle);
        }