- Class Db
- {
- var $conn;
- function Db ($host = "localhost", $user = "root", $pass = "root", $db = "Test")
- {
- if (! $this->conn=mysql_connect ($host, $user, $pass))
- Die ("can ' t connect to MySQL sever");
- mysql_select_db ($db, $this->conn);
- mysql_query ("SET NAMES ' UTF-8 '");
- }
- function Execute ($sql)
- {
- Return mysql_query ($sql, $this->conn);
- }
- function Findcount ($sql)
- {
- $result = $this->execute ($sql);
- Return mysql_num_rows ($result);
- }
- function Findbysql ($sql)
- {
- $array =array ();
- $result =mysql_query ($sql);
- $i = 0;
- while ($row =mysql_fetch_assoc ($result))
- {
- $array [$i]= $row;
- $i + +;
- }
- return $array;
- }
- Several cases of $con
- Empty: Return all records
- Array:eg. Array (' id ' = ' 1 ') returns record of id=1
- String:eg. ' Id=1 ' returns records of Id=1
- function Toextjson ($table, $start = "0", $limit = "Ten", $cons = "")
- {
- $sql = $this->generatesql ($table, $cons);
- $totalNum = $this->findcount ($sql);
- $result = $this->findbysql ($sql. " LIMIT ". $start.", ". $limit);
- $resultNum = count ($result);//Current number of results
- $str = "";
- $str. = "{";
- $str. = "' TotalCount ': ' $totalNum ',";
- $str. = "' Rows ':";
- $str. = "[";
- for ($i =0; $i < $resultNum; $i + +) {
- $str. = "{";
- $count =count ($result [$i]);
- $j = 1;
- foreach ($result [$i] as $key + = $val)
- {
- if ($j < $count)
- {
- $str. = "'". $key. "': '". $val. "',";
- }
- ElseIf ($j = = $count)
- {
- $str. = "'". $key. "': '". $val. "";
- }
- $j + +;
- }
- $str. = "}";
- if ($i! = $resultNum-1) {
- $str. = ",";
- }
- }
- $str. = "]";
- $str. = "}";
- return $str;
- }
- function GenerateSQL ($table, $cons)
- {
- $sql = "";//sql conditions
- $sql = "SELECT * from". $table;
- if ($cons! = "")
- {
- if (Is_array ($cons))
- {
- $k = 0;
- foreach ($cons as $key = $val)
- {
- if ($k ==0)
- {
- $sql. = "where";
- $sql. = $key;
- $sql. = "' = '";
- $sql. = $val. "'";
- }else
- {
- $sql. = "and";
- $sql. = $key;
- $sql. = "' = '";
- $sql. = $val. "'";
- }
- $k + +;
- }
- }else
- {
- $sql. = "where". $cons;
- }
- }
- return $sql;
- }
- function Toextxml ($table, $start = "0", $limit = "Ten", $cons = "")
- {
- $sql = $this->generatesql ($table, $cons);
- $totalNum = $this->findcount ($sql);
- $result = $this->findbysql ($sql. " LIMIT ". $start.", ". $limit);
- $resultNum = count ($result);//Current number of results
- Header ("Content-type:text/xml");
- $xml = " \ n";
- $xml. = " \ n";
- $xml. = "\ t ". $totalNum. " \ n ";
- $xml. = "\ t \ n";
- for ($i =0; $i < $resultNum; $i + +) {
- $xml. = "\t\t \ n";
- foreach ($result [$i] as $key + = $val)
- $xml. = "\t\t\t<". $key. " > ". $val." \ n ";
- $xml. = "\t\t\n";
- }
- $xml. = "\t\n";
- $xml. = "\ n";
- return $xml;
- }
- Output Word table
- function Toword ($table, $mapping, $fileName)
- {
- Header (' Content-type:application/doc ');
- Header (' content-disposition:attachment; Filename= '. $fileName. '. Doc "');
- Echo ' xmlns:w= "Urn:schemas-microsoft-com:office:word"
- xmlns= "HTTP://WWW.W3.ORG/TR/REC-HTML40" >
-
-
- '. $fileName. '
-
- ';
- Echo
- if (Is_array ($mapping))
- }
- $results = $this->findbysql (' select * from '. $table);
- foreach ($result as $key = $val)
- Echo
- }
- {
- foreach ($mapping as $key = $val)
- Echo
- Echo
- foreach ($results as $result)
- {
- Echo
- Echo
- Echo
';
'. $val. ' | ';
';
';
'. $val. ' | ';
';
';
- Echo ';
- Echo ';
- }
- function Toexcel ($table, $mapping, $fileName)
- {
- Header ("Content-type:application/vnd.ms-excel");
- Header ("Content-disposition:filename=". $fileName. ". XLS ");
- Echo ' xmlns:x= "Urn:schemas-microsoft-com:office:excel"
- xmlns= "HTTP://WWW.W3.ORG/TR/REC-HTML40" >
-
-
-
-
-
- ';
- Echo
- Echo
- if (Is_array ($mapping))
- }
- $results = $this->findbysql (' select * from '. $table);
- foreach ($result as $key = $val)
- Echo
- }
- {
- foreach ($mapping as $key = $val)
- Echo
- Echo
- foreach ($results as $result)
- {
- Echo
- Echo
- Echo
';
';
'. $val. ' | ';
';
';
'. $val. ' | ';
';
';
- Echo ';
- Echo ';
- }
- function Backup ($table)
- {
- if (Is_array ($table))
- {
- $str = "";
- foreach ($table as $tab)
- $str. = $this->get_table_content ($tab);
- return $str;
- }else{
- return $this->get_table_content ($table);
- }
- }
- function Backuptofile ($table, $file)
- {
- Header ("Content-disposition:filename= $file. sql");//the Saved file name
- Header ("Content-type:application/octetstream");
- Header ("Pragma:no-cache");
- Header ("expires:0");
- if (Is_array ($table))
- {
- $str = "";
- foreach ($table as $tab)
- $str. = $this->get_table_content ($tab);
- Echo $str;
- }else{
- echo $this->get_table_content ($table);
- }
- }
- function Restore ($table, $file = "", $content = "")
- {
- Exclude file,content are empty or both are not empty cases
- if ($file = = "" && $content = = "") | | ($file! = "" && $content! = ""))
- echo "Parameter Error";
- $this->truncate ($table);
- if ($file! = "")
- {
- if ($this->restorefromfile ($file))
- return true;
- Else
- return false;
- }
- if ($content! = "")
- {
- if ($this->restorefromcontent ($content))
- return true;
- Else
- return false;
- }
- }
- Clear the table to recover data
- function truncate ($table)
- {
- if (Is_array ($table))
- {
- $str = "";
- foreach ($table as $tab)
- $this->execute ("TRUNCATE TABLE $tab");
- }else{
- $this->execute ("TRUNCATE TABLE $table");
- }
- }
- function Get_table_content ($table)
- {
- $results = $this->findbysql ("SELECT * from $table");
- $temp = "";
- $crlf = "
";
- foreach ($results as $result)
- {
- /*(";
- foreach ($result as $key = $val)
- {
- $schema _insert. = "'". $key. " `,";
- }
- $schema _insert = Ereg_replace (", $", "", $schema _insert);
- $schema _insert. = ")
- */
- $schema _insert = "INSERT INTO $table VALUES (";
- foreach ($result as $key = $val)
- {
- if ($val! = "")
- $schema _insert. = "'". Addslashes ($val). "',";
- Else
- $schema _insert. = "NULL,";
- }
- $schema _insert = Ereg_replace (", $", "", $schema _insert);
- $schema _insert. = "); $crlf";
- $temp = $temp. $schema _insert;
- }
- return $temp;
- }
- function Restorefromfile ($file) {
- if (false!== ($fp = fopen ($file, ' R '))) {
- $sql _queries = Trim (Fread ($fp, FileSize ($file)));
- $this->splitmysqlfile ($pieces, $sql _queries);
- foreach ($pieces as $query) {
- if (! $this->execute (Trim ($query)))
- return false;
- }
- return true;
- }
- return false;
- }
- function Restorefromcontent ($content)
- {
- $content = Trim ($content);
- $this->splitmysqlfile ($pieces, $content);
- foreach ($pieces as $query) {
- if (! $this->execute (Trim ($query)))
- return false;
- }
- return true;
- }
- Function Splitmysqlfile (& $ret, $sql)
- {
- $sql = Trim ($sql);
- $sql =split ('; ', $sql);
- $arr =array ();
- foreach ($sql as $SQ)
- {
- if ($sq! = "");
- $arr []= $sq;
- }
- $ret = $arr;
- return true;
- }
- }
- $DB =new db ();
- Build Word
- $map =array (' No ', ' Name ', ' Email ', ' age ');
- echo $db->toword (' Test ', $map, ' archives ');
- Build Excel
- $map =array (' No ', ' Name ', ' Email ', ' age ');
- echo $db->toexcel (' Test ', $map, ' archives ');
- Generate XML
- echo $db->toextxml (' Test ', 0,20);
- Generate Json
- echo $db->toextjson (' Test ', 0,20);
- Backup
- echo $db->backuptofile (' Test ', ' backup ');
- ?>
Copy Code2. Full table backup 1 2 Next last page |