FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


MySQL DB Backup script - minor problem





JustaMin
Hi,

I have a script set up so that I can click a link in my admin area to backup my db. here is it:

Code:

<?
ob_start();
function mysqlbackup($host,$dbname, $uid, $pwd, $output, $structure_only)
{

    if (strval($output)!="") $fptr=fopen($output,"w"); else $fptr=false;

    //connect to MySQL database
    $con=mysql_connect("localhost",$uid, $pwd);
    $db=mysql_select_db($dbname,$con);

    //open back-up file ( or no file for browser output)

    //set up database
    out($fptr, "create database $dbname;\n\n");

    //enumerate tables
    $res=mysql_list_tables($dbname);
    $nt=mysql_num_rows($res);

    for ($a=0;$a<$nt;$a++)
    {
        $row=mysql_fetch_row($res);
        $tablename=$row[0];

        //start building the table creation query
        $sql="create table $tablename\n(\n";

        $res2=mysql_query("select * from $tablename",$con);
        $nf=mysql_num_fields($res2);
        $nr=mysql_num_rows($res2);

        $fl="";

        //parse the field info first
        for ($b=0;$b<$nf;$b++)
        {
            $fn=mysql_field_name($res2,$b);
            $ft=mysql_fieldtype($res2,$b);
            $fs=mysql_field_len($res2,$b);
            $ff=mysql_field_flags($res2,$b);

            $sql.="    $fn ";

            $is_numeric=false;
            switch(strtolower($ft))
            {
                case "int":
                    $sql.="int";
                    $is_numeric=true;
                    break;

                case "blob":
                    $sql.="text";
                    $is_numeric=false;
                    break;

                case "real":
                    $sql.="real";
                    $is_numeric=true;
                    break;

                case "string":
                    $sql.="char($fs)";
                    $is_numeric=false;
                    break;

                case "unknown":
                    switch(intval($fs))
                    {
                        case 4:    //little weakness here...there is no way (thru the PHP/MySQL interface) to tell the difference between a tinyint and a year field type
                            $sql.="tinyint";
                            $is_numeric=true;
                            break;

                        default:    //we could get a little more optimzation here! (i.e. check for medium ints, etc.)
                            $sql.="int";
                            $is_numeric=true;
                            break; 
                    }
                    break;

                case "timestamp":
                    $sql.="timestamp"; 
                    $is_numeric=true;
                    break;

                case "date":
                    $sql.="date"; 
                    $is_numeric=false;
                    break;

                case "datetime":
                    $sql.="datetime"; 
                    $is_numeric=false;
                    break;

                case "time":
                    $sql.="time"; 
                    $is_numeric=false;
                    break;

                default: //future support for field types that are not recognized (hopefully this will work without need for future modification)
                    $sql.=$ft;
                    $is_numeric=true; //I'm assuming new field types will follow SQL numeric syntax..this is where this support will breakdown
                    break;
            }

            //VERY, VERY IMPORTANT!!! Don't forget to append the flags onto the end of the field creator

            if (strpos($ff,"unsigned")!=false)
            {
                //timestamps are a little screwy so we test for them
                if ($ft!="timestamp") $sql.=" unsigned";
            }

            if (strpos($ff,"zerofill")!=false)
            {
                //timestamps are a little screwy so we test for them
                if ($ft!="timestamp") $sql.=" zerofill";
            }

            if (strpos($ff,"auto_increment")!=false) $sql.=" auto_increment";
            if (strpos($ff,"not_null")!=false) $sql.=" not null";
            if (strpos($ff,"primary_key")!=false) $sql.=" primary key";

            //End of field flags

            if ($b<$nf-1)
            {
                $sql.=",\n";
                $fl.=$fn.", ";
            }
            else
            {
                $sql.="\n);\n\n";
                $fl.=$fn;
            }

            //we need some of the info generated in this loop later in the algorythm...save what we need to arrays
            $fna[$b]=$fn;
            $ina[$b]=$is_numeric;
             
        }

        out($fptr,$sql);

        if ($structure_only!=true)
        {
            //parse out the table's data and generate the SQL INSERT statements in order to replicate the data itself...
            for ($c=0;$c<$nr;$c++)
            {
                $sql="insert into $tablename ($fl) values (";

                $row=mysql_fetch_row($res2);

                for ($d=0;$d<$nf;$d++)
                {
                    $data=strval($row[$d]);
                 
                    if ($ina[$d]==true)
                        $sql.= intval($data);
                    else
                        $sql.="\"".mysql_escape_string($data)."\"";

                    if ($d<($nf-1)) $sql.=", ";
     
                }

                $sql.=");\n";

                out($fptr,$sql);

            }

            out($fptr,"\n\n");

        }

        mysql_free_result($res2);     

    }
     
    if ($fptr!=false) fclose($fptr);
    return 0;

}

function out($fptr,$s)
{
    if ($fptr==false) {
      $today = date("j-n-Y H:i:s");
      $file_name = "JAM_Database_Backup-".$today;
      Header('Content-type: application/octet-stream');
      Header('Content-Disposition: attachment; filename='.$file_name.'.sql');
      echo $s;
   }
   else{
      fputs($fptr,$s);
   }
}

mysqlbackup("localhost","*******","*******","*******","", false);

ob_end_flush();
?>


Trouble is, when I go to dl the file it doesnt add an extension despite me specifying .sql in the code. This is no biggie really as I can do it manually but out of interest I was wondering if anyone knows of a fix?

Cheers Smile
thnn
It could possibly be the . before the sql. Perhaps try \.sql That might work. But then it may break your script. Thats all I can think of.
JustaMin
Hi thnn,

no didnt seem to make any difference, it's just ignoring any extension I put. However, I changed:

Header('Content-type: application/octet-stream');

to

Header('Content-type: text/plain');

and that at least saves it as a txt file which is fine. Smile
Related topics
script backup database
Advanced MySQL db merging Tutorials
phpBB Error, seems to be a MYSQL DB error...
How do I make a MySQL dB ?
[RESOLVED] How do I make a MySQL dB ?
text formatting inside of textarea adds white space
How can I secure my MySQL connection script and config files
Error connecting to mysql ; please help
How to keep 2 mysql servers in sync
[Help] Problem installing phpBB2
Problem with Mybb :: Changing DB Username
a mysql db service
MySQL Database Backup Using PHP
Need help with Backup Script
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.