コード例 #1
0
function mysqlbackup($host, $dbname, $uid, $pwd, $output, $structure_only)
{
    //this function creates a text file (or output to a HTTP connection), that when parsed through MYSQL's telnet client, will re-create the entire database
    //Parameters:
    //    $host: usually "localhost" but depends on where the MySQL database engine is mounted
    //    $dbname : The MySQL database name
    //    $uid : the database's username (not your account's), leave blank if none is required
    //    $pwd : the database's password
    //    $output : this is the complete filespec for the output text file, or if you want the result SQL to be sent back to the browser, leave blank.
    //    $structure_only : set this to true if you want just the schema of the database (not the actual data) to be output.
    // **************
    // IMPORTANT: If you use this function, for personal or commercial use, AND you feel an overwhelming sense of gratitude that someone actually took the time and wrote it,
    // immediately go to your paypal account and send me $10 with a small comment of how and how much it helped! Set the payment recipient to woodystanford@yahoo.com .
    // **************
    if (strval($output) != "") {
        $fptr = fopen($output, "w");
    } else {
        $fptr = false;
    }
    //connect to MySQL database
    $con = mysql_connect($host, $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 = nuRunQuery("SELECT * FROM {$tablename} ");
        $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;
}
コード例 #2
0
ファイル: api.inc.php プロジェクト: bouchra012/PMB
function table_dump($table_name, $fp)
{
    global $dbh;
    fwrite($fp, "#" . $table_name . "\r\n");
    fwrite($fp, "drop table if exists " . $table_name . ";\r\n");
    //Get strucutre
    fwrite($fp, create_statement($table_name) . "\n");
    //enumerate tables
    $update_a_faire = 0;
    /* permet de gérer les id auto_increment qui auraient pour valeur 0 */
    //parse the field info first
    $res2 = mysql_query("select * from {$table_name} order by 1 ", $dbh);
    if ($res2) {
        $nf = mysql_num_fields($res2);
        $nr = mysql_num_rows($res2);
    }
    $fields = '';
    $values = '';
    if ($nf) {
        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);
            $is_numeric = false;
            switch (strtolower($ft)) {
                case "int":
                    $is_numeric = true;
                    break;
                case "blob":
                    $is_numeric = false;
                    break;
                case "real":
                    $is_numeric = true;
                    break;
                case "string":
                    $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
                            $is_numeric = true;
                            break;
                        default:
                            $is_numeric = true;
                            break;
                    }
                    break;
                case "timestamp":
                    // Afin de résoudre le pb des timestamp pas corrects en restauration $is_numeric=true;
                    $is_numeric = false;
                    break;
                case "date":
                    $is_numeric = false;
                    break;
                case "datetime":
                    $is_numeric = false;
                    break;
                case "time":
                    $is_numeric = false;
                    break;
                default:
                    //future support for field types that are not recognized
                    //(hopefully this will work without need for future modification)
                    $is_numeric = true;
                    //I'm assuming new field types will follow SQL numeric syntax..
                    // this is where this support will breakdown
                    break;
            }
            (string) $fields != "" ? $fields .= ', ' . $fn : ($fields .= $fn);
            $fna[$b] = $fn;
            $ina[$b] = $is_numeric;
        }
    }
    //parse out the table's data and generate the SQL INSERT statements in order to replicate the data itself...
    if ($nr) {
        for ($c = 0; $c < $nr; $c++) {
            $row = mysql_fetch_row($res2);
            $values = '';
            for ($d = 0; $d < $nf; $d++) {
                $data = strval($row[$d]);
                if ($ina[$d] == true) {
                    (string) $values != "" ? $values .= ', ' . $data : ($values .= $data);
                } else {
                    (string) $values != "" ? $values .= ", \"" . mysql_escape_string($data) . "\"" : ($values .= "\"" . mysql_escape_string($data) . "\"");
                }
            }
            fwrite($fp, "insert into {$table_name} ({$fields}) values ({$values});\r\n");
            if ($update_a_faire == 1) {
                $update_a_faire = 0;
                fwrite($fp, "update {$table_name} set " . $cle_update . "='0' where " . $cle_update . "='1';\r\n");
            }
        }
    }
    if ($res2) {
        mysql_free_result($res2);
    }
}
コード例 #3
0
ファイル: mysql_backup.class.php プロジェクト: aile54/akachan
 function _Mysqlbackup($host, $dbname, $uid, $pwd, $output, $structure_only)
 {
     if (strval($this->output) != "") {
         $this->fptr = fopen($this->output, "w");
     } else {
         $this->fptr = false;
     }
     //connect to MySQL database
     $con = mysql_connect($this->host, $this->user, $this->pass);
     $db = mysql_select_db($dbname, $con);
     //open back-up file ( or no file for browser output)
     //set up database
     //out($this->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;
         }
         $this->_Out($sql);
         if ($this->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";
                 $this->_Out($sql);
             }
             $this->_Out("\n\n");
         }
         mysql_free_result($res2);
     }
     if ($this->fptr != false) {
         fclose($this->fptr);
     }
     return 0;
 }
コード例 #4
0
ファイル: mysql_backup.class.php プロジェクト: bouchra012/PMB
 function fetch_data()
 {
     global $dbh;
     //enumerate tables
     $res = mysql_list_tables(DATA_BASE);
     $i = 0;
     while ($i < mysql_num_rows($res)) {
         $update_a_faire = 0;
         /* permet de gérer les id auto_increment qui auraient pour valeur 0 */
         $table_name = mysql_tablename($res, $i);
         bzwrite($this->fptr, "delete from {$table_name};\n");
         $this->dump .= "delete from {$table_name};\n";
         //parse the field info first
         $res2 = mysql_query("select * from {$table_name} order by 1 ", $dbh);
         $nf = mysql_num_fields($res2);
         $nr = mysql_num_rows($res2);
         $fields = '';
         $values = '';
         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);
             $is_numeric = false;
             switch (strtolower($ft)) {
                 case "int":
                     $is_numeric = true;
                     break;
                 case "blob":
                     $is_numeric = false;
                     break;
                 case "real":
                     $is_numeric = true;
                     break;
                 case "string":
                     $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
                             $is_numeric = true;
                             break;
                         default:
                             $is_numeric = true;
                             break;
                     }
                     break;
                 case "timestamp":
                     $is_numeric = true;
                     break;
                 case "date":
                     $is_numeric = false;
                     break;
                 case "datetime":
                     $is_numeric = false;
                     break;
                 case "time":
                     $is_numeric = false;
                     break;
                 default:
                     //future support for field types that are not recognized
                     //(hopefully this will work without need for future modification)
                     $is_numeric = true;
                     //I'm assuming new field types will follow SQL numeric syntax..
                     // this is where this support will breakdown
                     break;
             }
             $fields ? $fields .= ', ' . $fn : ($fields .= $fn);
             $fna[$b] = $fn;
             $ina[$b] = $is_numeric;
         }
         //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++) {
             $row = mysql_fetch_row($res2);
             $values = '';
             for ($d = 0; $d < $nf; $d++) {
                 $data = strval($row[$d]);
                 if ($d == 0 && strval($row[$d]) == 0) {
                     /* traiter ici l'insertion avec valeur 1 pour id autoincrement et update à suivre */
                     $values ? $values .= ', ' . '1' : ($values .= '1');
                     $cle_update = mysql_field_name($res2, 0);
                     $update_a_faire = 1;
                 } else {
                     if ($ina[$d] == true) {
                         $values ? $values .= ', ' . intval($data) : ($values .= intval($data));
                     } else {
                         $values ? $values .= ", \"" . mysql_escape_string($data) . "\"" : ($values .= "\"" . mysql_escape_string($data) . "\"");
                     }
                 }
             }
             bzwrite($this->fptr, "insert into {$table_name} ({$fields}) values ({$values});\n");
             $this->dump .= "insert into {$table_name} ({$fields}) values ({$values});\n";
             if ($update_a_faire == 1) {
                 $update_a_faire = 0;
                 bzwrite($this->fptr, "update {$table_name} set " . $cle_update . "='0' where " . $cle_update . "='1';\n");
                 $this->dump .= "update {$table_name} set " . $cle_update . "='0' where " . $cle_update . "='1';\n";
             }
         }
         mysql_free_result($res2);
         $i++;
     }
 }