CODEWITHSUNDEEP
phpmysql
Jean-Paul
Jean-Paul

Reputation: 378

backup MySQL using PHP only (with server restrictions)

I am trying to backup my MySQL Database using PHP. I want to backup it to an .SQL so i can import it back in MySQL if anything goes wrong.

The only problem I found out when i tried to use Exec of System, that these functions are turned of by my host, and their not gonna activate it because of security reasons.

Also I cannot use : SELECT ... INTO OUTFILE.

What my thoughts are : Is it possible to use SELECT * FROM [Table] and get a php array from that query. And then push that data into an SQL file?

I cannot find information about this even though I tried some things. I know you can make a .txt file with the right text in it and call it a .sql but then i need to write everything by hand, thats gonna take a while.

Or is their a better way to do this?

Thanks in advance!

Upvotes: 1

Views: 78

Answers (1)

Rikudou_Sennin
Rikudou_Sennin

Reputation: 1375

$tables can be * or array of table names or comma separated list of table names

function backup_tables($host,$user,$pass,$name,$tables = '*') {

    $link = mysql_connect($host,$user,$pass); 
    mysql_select_db($name,$link); 
  mysql_query('SET CHARACTER SET cp1250');
    mysql_query('SET NAMES cp1250');


    if($tables == '*') { 
        $tables = array();
        $result = mysql_query('SHOW TABLES');
        while($row = mysql_fetch_row($result)) {
            $tables[] = $row[0];
        }
    }   else {
        $tables = is_array($tables) ? $tables : explode(',',$tables);
    }

    foreach($tables as $table) {
        $result = mysql_query('SELECT * FROM `'.$table.'`');
        $num_fields = mysql_num_fields($result);

        $return.= 'DROP TABLE IF EXISTS `'.$table.'`;';
        $row2 = mysql_fetch_row(mysql_query('SHOW CREATE TABLE `'.$table.'`'));
        $return.= "\n\n".$row2[1].";\n\n";

        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] = ereg_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";
    }

    $handle = fopen('db-backup-'.date('Y-m-d-H-i-s').'-'.(md5(implode(',',$tables))).'.sql','w+');
    fwrite($handle,$return);
    fclose($handle);
}

Upvotes: 1

Related Questions