Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    179
    Thanks
    39
    Thanked 2 Times in 2 Posts

    MYSQL table backup using PHP script error

    Hi All,

    i am using below script and I am getiing error while Fopne & Fwrite.

    Below is the code.

    PHP Code:
    <?php
    $DBhost
    ="localhost"// Host name 
    $DBuser="root"// Mysql username 
    $DBpass="test@123"// Mysql password 
    $DBName="testdb"// Database name 
    $table="*";

        
    // Set the suffix of the backup filename
        
    if ($table == '*') {
            
    $extname 'all';
        }else{
            
    $extname str_replace(",""_"$table);
            
    $extname str_replace(" ""_"$extname);
        }
     
        
    // Call the backup function for all tables in a DB
        
    backup_tables($DBhost,$DBuser,$DBpass,$DBName,$table,$extname);
     
        
    // Backup the table and save it to a sql file
        
    function backup_tables($host,$user,$pass,$name,$tables,$bckextname)
        {
            
    $link mysql_connect($host,$user,$pass);
            
    mysql_select_db($name,$link);
            
    $return "";
     
            
    // Get all of the tables
            
    if($tables == '*') {
                
    $tables = array();
                
    $result mysql_query('SHOW TABLES');
                while(
    $row mysql_fetch_row($result)) {
                    
    $tables[] = $row[0];
                }
            } else {
                if (
    is_array($tables)) {
                
    $tables explode(','$tables);
            }
        }
     
        
    // Cycle through each provided table
        
    foreach($tables as $table) {
            
    $result mysql_query('SELECT * FROM '.$table);
            
    $num_fields mysql_num_fields($result);
     
            
    // First part of the output – remove the table
            
    $return .= 'DROP TABLE ' $table ';<|||||||>';
     
            
    // Second part of the output – create table
            
    $row2 mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table));
            
    $return .= "\n\n" $row2[1] . ";<|||||||>\n\n";
     
            
    // Third part of the output – insert values into new table
            
    for ($i 0$i $num_fields$i++) {
                while(
    $row mysql_fetch_row($result)) {
                    
    $return.= 'INSERT INTO '.$table.' VALUES(';
                    for(
    $j=0$j<$num_fields$j++) {
                        
    $row[$j] = addslashes($row[$j]);
                        
    $row[$j] = preg_replace("\n","\\n",$row[$j]);
                        if (isset(
    $row[$j])) {
                            
    $return .= '"' $row[$j] . '"';
                        } else {
                            
    $return .= '""';
                        }
                        if (
    $j<($num_fields-1)) {
                            
    $return.= ',';
                        }
                    }
                    
    $return.= ");<|||||||>\n";
                }
            }
            
    $return.="\n\n\n";
        }
     
        
    // Generate the filename for the sql file
        
    date_default_timezone_set('Asia/Kolkata');
        
    $date=date('Y:m:d H:i:s'time());
        
         
    $filess 'temp/dbbackup_'.$date.'.sql';
     
        
    // Save the sql file
        
    $handle fopen($filess,'w+');
        
    fwrite($handle,$return);
        
    fclose($handle);
     
        
    // Print the message
        
    print("The backup has been created successfully. You can get the file <a href='$filess'>here</a>.<br>\n");
     
        
    // Close MySQL Connection
        
    mysql_close();
    }
    ?>
    errors I am getting while executing above script is:

    PHP Code:
    Warningpreg_replace() [function.preg-replace]: Empty regular expression in C:xampphtdocstestindex.php on line 57

    Warning
    preg_replace() [function.preg-replace]: Empty regular expression in C:xampphtdocstestindex.php on line 57

    Warning
    fopen(temp/dbbackup_2013:04:02 13:05:10.sql) [function.fopen]: failed to open streamInvalid argument in C:xampphtdocstestindex.php on line 80

    Warning
    fwrite() expects parameter 1 to be resourceboolean given in C:xampphtdocstestindex.php on line 81

    Warning
    fclose() expects parameter 1 to be resourceboolean given in C:xampphtdocstestindex.php on line 82 
    Last edited by nani_nisha06; 04-03-2013 at 04:35 PM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    The first is that your pattern is completely wrong: $row[$j] = preg_replace("\n","\\n",$row[$j]); . Regexp is not required for this. Use str_replace since there is no required logic in order to evaluate a string for a pattern.
    The second is simply your filename; Windows systems cannot have colons in their filenames.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    nani_nisha06 (04-02-2013)

  • #3
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    179
    Thanks
    39
    Thanked 2 Times in 2 Posts
    Thanks Fou-lu,

    Issue resolved.....

  • #4
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    179
    Thanks
    39
    Thanked 2 Times in 2 Posts
    Fou-lU,

    I need some information I dont know if this is a right corner for discussion but below question is related to above posted script.

    Now I have migrated above script from windows environment to linux but file is not getting created in the desired folder when I googled for the solution I found folder permissions are not permitting to created the new file via script so, how can i override this permission to solve the problem.

    any suggestions please.

    Regards,
    nani
    Last edited by nani_nisha06; 04-03-2013 at 04:46 PM.

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    From an ssh you can login with a user that has privilege to chmod, and you simply either use chmod to change the permissions to allow all write, or you can set the group ownership to that of your apache user and allow just the group to write. You can also use php's mkdir() function to construct the directory for you which should assign the ownership of the apache user to it.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • #6
    Regular Coder
    Join Date
    Oct 2012
    Location
    mother land --india
    Posts
    179
    Thanks
    39
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Fou-Lu View Post
    From an ssh you can login with a user that has privilege to chmod, and you simply either use chmod to change the permissions to allow all write, or you can set the group ownership to that of your apache user and allow just the group to write. You can also use php's mkdir() function to construct the directory for you which should assign the ownership of the apache user to it.
    Fou-lu,

    Yes now I have set chmod as 777 but still the above script does not create any file.

    I have also observed one particular difference here, if I keep above script & new file generating path to same folder it is successfully creating new file.

    but, if I am giving another dir as a path it doesn't give me any output.

    code I am using is below.

    PHP Code:
    <?php 
    $DBhost
    ="localhost"// Host name  
    $DBuser="root"// Mysql username  
    $DBpass="test@123"// Mysql password  
    $DBName="testdb"// Database name  
    $table="*"

        
    // Set the suffix of the backup filename 
        
    if ($table == '*') { 
            
    $extname 'all'
        }else{ 
            
    $extname str_replace(",""_"$table); 
            
    $extname str_replace(" ""_"$extname); 
        } 
      
        
    // Call the backup function for all tables in a DB 
        
    backup_tables($DBhost,$DBuser,$DBpass,$DBName,$table,$extname); 
      
        
    // Backup the table and save it to a sql file 
        
    function backup_tables($host,$user,$pass,$name,$tables,$bckextname
        { 
            
    $link mysql_connect($host,$user,$pass); 
            
    mysql_select_db($name,$link); 
            
    $return ""
      
            
    // Get all of the tables 
            
    if($tables == '*') { 
                
    $tables = array(); 
                
    $result mysql_query('SHOW TABLES'); 
                while(
    $row mysql_fetch_row($result)) { 
                    
    $tables[] = $row[0]; 
                } 
            } else { 
                if (
    is_array($tables)) { 
                
    $tables explode(','$tables); 
            } 
        } 
      
        
    // Cycle through each provided table 
        
    foreach($tables as $table) { 
            
    $result mysql_query('SELECT * FROM '.$table); 
            
    $num_fields mysql_num_fields($result); 
      
            
    // First part of the output – remove the table 
            
    $return .= 'DROP TABLE ' $table ';<|||||||>'
      
            
    // Second part of the output – create table 
            
    $row2 mysql_fetch_row(mysql_query('SHOW CREATE TABLE '.$table)); 
            
    $return .= "\n\n" $row2[1] . ";<|||||||>\n\n"
      
            
    // Third part of the output – insert values into new table 
            
    for ($i 0$i $num_fields$i++) { 
                while(
    $row mysql_fetch_row($result)) { 
                    
    $return.= 'INSERT INTO '.$table.' VALUES('
                    for(
    $j=0$j<$num_fields$j++) { 
                        
    $row[$j] = addslashes($row[$j]); 
                        
    $row[$j] = preg_replace("\n","\\n",$row[$j]); 
                        if (isset(
    $row[$j])) { 
                            
    $return .= '"' $row[$j] . '"'
                        } else { 
                            
    $return .= '""'
                        } 
                        if (
    $j<($num_fields-1)) { 
                            
    $return.= ','
                        } 
                    } 
                    
    $return.= ");<|||||||>\n"
                } 
            } 
            
    $return.="\n\n\n"
        } 
      
        
    // Generate the filename for the sql file 
        
    date_default_timezone_set('Asia/Kolkata'); 
        
    $date=date('Y:m:d H:i:s'time()); 
         
         
    $filess '/temp/dbbackup_'.$date.'.sql'
      
        
    // Save the sql file 
        
    $handle fopen($filess,'w+'); 
        
    fwrite($handle,$return); 
        
    fclose($handle); 
      
        
    // Print the message 
        
    print("The backup has been created successfully. You can get the file <a href='$filess'>here</a>.<br>\n"); 
      
        
    // Close MySQL Connection 
        
    mysql_close(); 

    ?>

  • #7
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Its still your permissions then.
    You need to check both the chmod and the umask on a directory. Or add the apache group as a part of the group on the directory and ensure its set for rwx.
    Keep in mind that /temp/ likely doesn't exist. That is hinged off of filesystem root, and wouldn't make any sense since it would be sibling to the /tmp directory anyway. Your first code used temp/ which could exist depending on the pwd of the working file.
    Bad files paths or permission errors shouldn't prevent output though. fopen documentation specifies it only throws an E_WARNING, so the script should continue along happily. Since you don't have any error handling in here, it should make it to the end and indicate its been created regardless of if it actually wrote a file or not.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •