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; }
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); } }
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; }
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++; } }