|
|
|
|
|
|
| |
DBXML
This particular class is optimized for MySQL.As i dnt SQL2k or Oracle to test with.
If u find any problems please do mail me .
Mention in the subject "DBXML: Bug Mail".
Anyway i wll b optimizing this for SQL 2k and Oracle soon.
Any suggestions in class design are also welcome.
If u can help me just mail me a thanks .
This class tht exports data in a table into XML format
Supports currently 4 MYSQL & SQL Server.
Connect host as
hstconnect("mysql","foo","funame","fupass");
hstconnect("mssql","foo","funame","fupass");
hstconnect("oracle8","funame","fupass");
FUNCTION LIST
function ad_view($db)
function DBXML () The Constructor
function drop_db($db) Drops a database from the selected view
function drop_table($db,$tbl) Drops a table " "
function hstconnect($type,$host,$uname,$pass) Connects to Database Host
function mssql_db_xml() Database backup from SQL2K Host
function mssql_tbl_xml($db,$tbl,$fnme) Table " "
function mysql_auth($hst_name,$hst_unam) Checks mysql authentication
function mysql_db_xml($db) Database backup from SQL2K Host
function mysql_lst_bkup($db) Lists the last backup of databses
function mysql_lst_dbs($lnk,$dd) List of databases of MySQL
function mysql_lst_tbl($db) List of tables in MySQL Host
function mysql_mod_design($db,$tbl) Modify Design of DB/Table
function mysql_read_log() Reading the backup log
function mysql_tbl_bkup($tbl) backup of a single table
function mysql_tbl_xml($db,$tbl,$fnme) tbl backup including the dbname ,tblname
function mysql_write_log($type,$db,$tbl) write logs
|
<?php
class DBXML
{
var $glb_lnk,$hst,$uname,$pass,$type;
function DBXML()
{
print("Inside Constructor");
}
function hstconnect($type,$host,$uname,$pass)
{
switch($type)
{
case "mysql":
$link = @mysql_connect($host, $uname,$pass)or die(mysql_error());
$glb_lnk=$link;
Return $glb_lnk;
break;
case "mssql" :
if (!extension_loaded('mssql'))
{
if (!dl('php_mssql.dll'))
{
print("Couldn't load SQL Server Interface ");
exit;
}
else
{
print("Native Drivers Loaded<BR>");
$link = mssql_connect($host, $uname,$pass)or die("Could not connect");
$glb_lnk=$link;
Return $glb_lnk;
break; }
}
case "oracle8" :
if (!extension_loaded('oci8'))
{
if (!dl('php_oci8.dll'))
{
print("Couldn't load OCI 8 Interface!!!");
exit;
}
else
{
print("Native Drivers Loaded<BR>");
$link = OCIlogon($uname,$pass)or die("Could not connect"); $glb_lnk=$link;
Return $glb_lnk;
break;
}
}
}
}
function mysql_db_xml($db)
{
$wxml="";
mysql_select_db($db) or die("Could not select database");
$fb=$db.".lbf";
$tb_res = mysql_list_tables($db);
for($tbc=0;$tbc<mysql_num_rows($tb_res);$tbc++)
{
$fnme="C:\\".mysql_tablename($tb_res,$tbc).".xml";
print(mysql_tablename($tb_res,$tbc));
DBXML::mysql_tbl_xml($db,mysql_tablename($tb_res,$tbc),$fnme);
}
$dt=fopen($fb,"w");
$dat=date("F j, Y, g:i a");
$id="BACKUP"."FBK @".$dat."\n";
$tt=fwrite($dt,$id);
fclose($dt);
}
function mysql_tbl_xml($db,$tbl,$fnme)
{
$wxml="";
mysql_select_db($db) or die("Could not select database");
$fb=$db.".lbf";
$ft=fopen($fb,"w");
$dat=date("F j, Y, g:i a");
$id="BACKUP"."PBK @".$dat."\n";
$tt=fwrite($ft,$id);
fclose($ft);
$fnme=$fnme.$tbl."_bk.xml";
$ft=fopen($fnme,"w");
$bw=fwrite($ft,"<?xml version=\"1.0\"?".">");
$bw=fwrite($ft,"<xml>");
$bw=fwrite($ft,"<".$db.">");
$xm="SELECT * FROM ".$tbl."";
$xm_res=mysql_query($xm) or die(mysql_error());
$num_rows = mysql_num_rows($xm_res);
$bw=fwrite($ft,"<".$tbl." value=\"".$num_rows." records\">");
$sql = "SHOW KEYS FROM ".$db.".".$tbl."";
$fld= mysql_query($sql) or die(mysql_error());
$wxml=$wxml."<primary_key >";
while ($row = mysql_fetch_assoc($fld))
{
if ($row['Key_name']=='PRIMARY')
{
$wxml=$wxml.$row['Column_name'].",";
}
}
for($i=0;$i<mysql_num_rows($xm_res);$i++)
{
$row=mysql_fetch_assoc($xm_res);
$wxml=$wxml."<".mysql_field_name($xm_res,0)." value =\"".strip_tags(str_replace("<","",str_replace("\"","",str_replace("'","",$row[mysql_field_name($xm_res,0)]))))."\">";
for($j=1;$j<mysql_num_fields($xm_res);$j++)
{
$wxml=$wxml."<".mysql_field_name($xm_res,$j).">";
$wxml=$wxml.$row[mysql_field_name($xm_res,$j)];
$wxml=$wxml."</".mysql_field_name($xm_res,$j).">";
}
$wxml=$wxml."</".mysql_field_name($xm_res,0).">";
}
$wxml=$wxml."</primary_key>";
$wxml=$wxml."</".$tbl.">"."</".$db.">"."</xml>";
$bw=fwrite($ft,$wxml);
print($wxml);
$fb=$tbl.".tbf";
$ft=fopen($fb,"w");
$dat=date("F j, Y, g:i a");
$id="BACKUP"."PBK @".$dat."\n";
$tt=fwrite($ft,$id);
fclose($ft);
Return true;
}
/*function oci_db_xml() These 2 are for Oracle
{
}
function oci_tbl_xml()
{
}*/
function mssql_db_xml()
{
$wxml="";
mssql_select_db($db) or die("Could not select database");
$tb_res = msql_list_tables($db);
for($tbc=0;$tbc<mssql_num_rows($tb_res);$tbc++)
{
$fnme="C:\\".msql_tablename($tb_res,$tbc).".xml";
mssql_tbl_xml($db,msql_tablename($tb_res,$tbc),$fnme);
}
}
function mssql_tbl_xml($db,$tbl,$fnme)
{
$wxml="";
mssql_select_db($db) or die("Could not select database");
$ft=fopen($fnme,"a");
$bw=fwrite($ft,"<?xml version=\"1.0\"?".">");
$bw=fwrite($ft,"<xml>");
$bw=fwrite($ft,"<".$db.">");
$xm="SELECT * FROM ".$tbl."";
$xm_res=mssql_query($xm) or die("Query failed33");
$num_rows = mssql_num_rows($xm_res);
$bw=fwrite($ft,"<".$tbl." value=\"".$num_rows." records\">");
for($i=0;$i<mssql_num_rows($xm_res);$i++)
{
$row=mssql_fetch_assoc($xm_res);
$wxml=$wxml."<".mssql_field_name($xm_res,0)." value =\"".strip_tags(str_replace("<","",str_replace("\"","",str_replace("'","",$row[mssql_field_name($xm_res,0)]))))."\">";
for($j=1;$j<mssql_num_fields($xm_res);$j++)
{
$wxml=$wxml."<".mssql_field_name($xm_res,$j).">";
$wxml=$wxml.$row[mssql_field_name($xm_res,$j)];
$wxml=$wxml."</".mssql_field_name($xm_res,$j).">";
}
$wxml=$wxml."</".mssql_field_name($xm_res,0).">";
}
$wxml=$wxml."</".$tbl.">"."</".$db.">"."</xml>";
$bw=fwrite($ft,$wxml);
print($wxml);
Return true;
}
function mysql_lst_tbl($db)
{ mysql_select_db($db) or die("Could not select database");
$ss="<form method=\"post\"action=gen_xml.php><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\">BACKUP : </font><select size=\"1\" name=\"tbl_name\"><option value=\"all\" selected>ALL</option>";
$dbtb=mysql_list_tables($db);
for($tbc=0;$tbc<mysql_num_rows($dbtb);$tbc++)
{
$ss=$ss."<option value=\"".mysql_tablename($dbtb,$tbc)."\">".mysql_tablename($dbtb,$tbc)."</option>";
}
$ss=$ss."</select><BR><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\"> STRUCTURE : </font><select size=\"1\" name=\"struct\"> <option value=\"YES\" selected>YES</option><option value=\"NO\">NO</option></select><input type=\"Submit\" Value=\"Do It!!\"><BR><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\"> <BR>Last Backup on :</font><BR><font face=\"Lucida Console\" size=\"2\" color=\"#FF0033\">".DBXML::mysql_lst_bkup($db)."</font><input type=\"hidden\" name=\"dbase\" value=\"".$db."\"></form>";
return $ss;
}
function mysql_lst_dbs($lnk,$dd)
{
$db_list = mysql_list_dbs($lnk);
print("<html><body alink=#FFFFFF vlink=#FFFFFF link=#FFFFFF><p align=center><U><font face=\"Lucida Console\" size=\"4\" color=\"#0000FF\">DATABASE LISTING ON MYSQL SERVER \" ".strtoupper($dd)." \" </font></u></p><BR><p align=center><table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#FFFFFF\" width=\"75%\" height=\"71\"bgcolor=\"#C0C0C0\">");
while ($row = mysql_fetch_assoc($db_list))
{
print("<tr><td width=\"10%\" height=\"20\" bgcolor=#FFFFFF ><p align=right><img src=\"db3.gif\"></p></td><td width=\"15%\" height=\"20\"><form name=\"drpdb\" method=\"post\" action=\"ddl.php?tr=".$row["Database"]."\"><a href=\"ddl.php?dbname=".$row["Database"]."\" title=\"View Tables in ". $row["Database"]."\"><font face=\"Lucida Console\" size=\"4\">".$row["Database"]."</font></a><input type=\"Submit\" name=\"db_drp\" value=\"DROP\" title=\"DROP DATABASE\"></form></td><td width=\"20%\" height=\"20\">".DBXML::mysql_lst_tbl($row["Database"])."</td></tr>");
}
print("<input type=\"hidden\" name=\"act\" value=\"1\"></table></p><P align=left><b><font face=Garamond size=4 color=#333399>FBK --- FULL BACKUP ******* PBK --- PARTIAL BACKUP</font></p>");
print("<a href=\"ddl.php?act=viewlog\"><font face=\"Lucida Console\" size=\"3\" color=\"#3300FF\">VIEW BACKUP LOGS</font></a></b>");
}
function drop_db($db)
{
$qry="DROP DATABASE IF EXISTS ".$db." ";
if(mysql_query($qry))
{
print("<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" bordercolor=\"#FFFFFF\" width=\"100%\" height=\"45\" bgcolor=cyan><tr><td width=\"14%\" height=\"35\"><img src=\"tok.gif\" align=\"right\"></td><td width=\"86%\" height=\"35\"><font face=\"Lucida Console\" size=\"3\">THE DATABASE ".$db." HAS BEEN SUCCESSFULLY DROPPED</font></td></tr></table>");
}
else
{
print("<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" bordercolor=\"#FFFFFF\" width=\"100%\" height=\"45\" bgcolor=\"#FFFFFF\"><tr><td width=\"14%\" height=\"35\"><img src=\"crs.gif\" align=\"right\"></td><td width=\"86%\" height=\"35\"><font face=\"Lucida Console\" size=\"3\">OOPS!!!THE TABLE ".$db." COULDN'T BE DROPPED</font></td></tr></table>");
}
}
function drop_table($db,$tbl)
{
$qry="DROP TABLE `".$db."`.`".$tbl."`" ;
if(mysql_query($qry))
{
print("<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" bordercolor=\"#CCCCFF\" width=\"100%\" height=\"45\" bgcolor=cyan><tr><td width=\"14%\" height=\"35\"><img src=\"tok.gif\" align=\"right\"></td><td width=\"86%\" height=\"35\"><font face=\"Lucida Console\" size=\"3\">THE TABLE ".$tbl." HAS BEEN SUCCESSFULLY DROPPED</font></td></tr></table>");
}
else
{
print("<table border=\"1\" cellpadding=\"5\" cellspacing=\"0\" bordercolor=\"#CCCCFF\" width=\"100%\" height=\"45\" bgcolor=\"#FFFFFF\"><tr><td width=\"14%\" height=\"35\"><img src=\"crs.gif\" align=\"right\"></td><td width=\"86%\" height=\"35\"><font face=\"Lucida Console\" size=\"3\">OOPS!!!THE TABLE ".$tbl." COULDN'T BE DROPPED</font></td></tr></table>");
}
}
function ad_view($db)
{
mysql_select_db($db)or die(mysql_error());
print("<html><body alink=#FFFFFF vlink=#FFFFFF link=#FFFFFF><p align=center><U><font face=\"Lucida Console\" size=\"4\" color=\"#0000FF\">TABLE LISTING ON <img src=db3.gif> \"".strtoupper($db)."\" </font></u></p>");
print("<table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#FFFFFF\" width=\"75%\" height=\"71\"bgcolor=\"#C0C0C0\">");
print("<tr><td width=1% bgcolor=\"#FFFFFF\" bordercolor=\"#FFFFFF\"></td><td width=\"10%\" height=\"20\" bgcolor=#CCCCCC><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\">TABLE NAME :</font></td><td width=\"5%\" height=\"20\"><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\">RECORDS</font></td><td width=\"10%\" height=\"20\"><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\">LAST BACKUP</font></td><td width=\"20%\" height=\"20\"><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\">ACTION</font></td></tr>");
$tbstat="SHOW TABLE STATUS";
$xm_res=mysql_query($tbstat) or die(mysql_error());
$num_rows = mysql_num_rows($xm_res);
/*for($i=0;$i<$num_rows;$i++)
{*/
if($num_rows>0)
{
while($row = mysql_fetch_assoc($xm_res))
{
print("<tr><td width=\ 5%\ bgcolor=#FFFFFF ><img src= calbr1.gif ></td><td width=\"10%\" height=\"20\" bgcolor=#CCCCCC ><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\">".$row["Name"]."</font></td><td width=\"5%\" height=\"20\"><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\">".$row["Rows"]."</font></td><td width=\"10%\" height=\"20\"><font face=\"Lucida Console\" size=\"2\" color=\"#FF0000\">".DBXML::mysql_tbl_bkup($row["Name"])."</font></td><td width=\"20%\" height=\"20\"><font face=\"Lucida Console\" size=\"2\" color=\"#0000FF\"><!--<script language=\"JavaScript\" src=\"func.js\"></script>--><form name=\"far\" method=\"post\" action=\"ddl.php\"><select size=1 name=\"dbaction\"><option value=\"MODIFY\" selected>MODIFY DESIGN</option><option value=\"DROP\">DROP</option></select><input type=Submit value=\"OK\" name=\"dbagree\"><input type=\"hidden\" name=\"dbnam\" value=\"".$db."\"><input type=\"hidden\" name=\"tbnam\" value=\"".$row["Name"]."\"></form></font></td></tr>");
}
}
else
{
print("</table><p align =justify><b><font face=\"Lucida Console\" size=\"2\" color=\"#FF0000\">EMPTY DATABASE !!! NO TABLES FOUND!!!</font></b></p>");
}
}
function mysql_lst_bkup($db)
{
$bf=$db.".lbf";
if(file_exists($bf))
{
$ft=fopen($bf,"r");
$filecon=fgets($ft,filesize($bf));
$d=substr($filecon,strrpos($filecon,"BACKUP")-1);//(strlen($filecon)-strrpos($filecon,"BACKUP")
}
else
{
$d="<BR> NO BACKUP FILES !!!";
}
return $d;
}
function mysql_tbl_bkup($tbl)
{
$bf=$tbl.".tbf";
if(file_exists($bf))
{
$ft=fopen($bf,"r");
$filecon=fgets($ft,filesize($bf));
$d=substr($filecon,strrpos($filecon,"BACKUP")-1);//(strlen($filecon)-strrpos($filecon,"BACKUP")
}
else
{
$d="<BR> NO BACKUP FILES !!!";
}
return $d;
}
function mysql_auth($hst_name,$hst_unam)
{
$auth=$hst_unam."@".$hst_name;
$qry="SHOW GRANTS FOR ".$auth."";
$res=mysql_query($qry)or die(mysql_error());
while($row=mysql_fetch_assoc($res))
{
if(strchr($row["Grants for ".$auth.""],"GRANT ALL PRIVILEGES ON *.*"))
{
return 1;
}
else
{
return -1;
}
}
}
function mysql_write_log($type,$db,$tbl)
{
$dat=date("F j, Y, g:i a");
$bf="backup.fbl";
if(strcasecmp($type,"FBK")==0)
{
$str=$type." ".$db." @ ".$dat. "\n";
}
else
{
$str=$type." ".$db." . ".$tbl." @ ".$dat. "\n";
}
if(file_exists($bf))
{
$ft=fopen($bf,"a");
$tt=fwrite($ft,$str);
}
else
{
$ft=fopen($bf,"w");
$tt=fwrite($ft,$str);
}
$d=fclose($ft);
}
function mysql_read_log()
{
$bf="backup.fbl";
$tf=fopen($bf,"r");
$ft=file_get_contents($bf);
return $ft;
}
function mysql_mod_design($db,$tbl)
{
mysql_select_db($db) or die("Could not select database");
$qry="DESC ".$tbl."";
$res=mysql_query($qry)or die(mysql_error());
print("<U><font face=\"Lucida Console\" size=\"4\" color=\"#0000FF\">TABLE DESIGN OF \" ".strtoupper($tbl)." \" </font></u><table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" bordercolor=\"#C0C0C0\" width=\"75%\" height=\"71\"bgcolor=\"#C0C0C0\"><tr><td>FIELD</td><td>TYPE</td><td>LENGTH</td><td> NEW TYPE </td><td>PRIMARY KEY</td></tr>");
print("<form name=f1 action=mod.php method=post>");
while($rw=mysql_fetch_assoc($res))
{
if(strcasecmp($rw["Key"],"PRI")==0)
{
$t=split ('[()]',$rw["Type"]);
print("<tr><td><input type=text name=fld_name value=".$rw["Field"]."></td><td><input type=text name=fld_name value=".$t[0]."></td><td><input type=text name=fld_len#".$rw["Field"]." value=".@$t[1]."></td><td> ".DBXML::cmb_gen($rw["Field"])."</td><td><img src=tok.gif></td></tr><BR>");
}
else
{
$t=split ('[()]',$rw["Type"]);
print("<tr><td><input type=text name=fld_name value=".$rw["Field"]."></td><td><input type=text name=fld_name value=".$t[0]."></td><td><input type=text name=fld_len#".$rw["Field"]." value=".@$t[1]."></td><td> ".DBXML::cmb_gen($rw["Field"])."</td><td></td></tr><BR>");
}
}
print("<tr><td><input type=Submit name=md_dsgn value=Save></td><td><input type=reset name=md_res></td></tr><Form></table>");
}
function cmb_gen($ty)
{
$dd="";
$dd="<select size=1 name=new_type#".$ty.">";
$dd=$dd."<option value=TINYINT>TINYINT</option>";
$dd=$dd."<option value=BIT >BIT </option>";
$dd=$dd."<option value=BOOL>BOOL</option>";
$dd=$dd."<option value=MEDIUMINT>MEDIUMINT</option>";
$dd=$dd."<option value=INT>INT</option>";
$dd=$dd."<option value=INTEGER>INTEGER</option>";
$dd=$dd."<option value=BIGINT>BIGINT</option>";
$dd=$dd."<option value=FLOAT>FLOAT</option>";
$dd=$dd."<option value=DOUBLE>DOUBLE</option>";
$dd=$dd."<option value=REAL>REAL</option>";
$dd=$dd."<option value=DECIMAL>DECIMAL</option>";
$dd=$dd."<option value=NUMERIC>NUMERIC</option>";
$dd=$dd."<option value=DATE>DATE</option>";
$dd=$dd."<option value=TIMESTAMP>TIMESTAMP</option>";
$dd=$dd."<option value=CHAR >CHAR </option>";
$dd=$dd."<option value=TINYBLOB >TINYTEXT</option>";
$dd=$dd."<option value=BLOB>TEXT</option>";
$dd=$dd."<option value=MEDIUMBLOB>MEDIUMTEXT</option>";
$dd=$dd."<option value=LONGBLOB>LONGTEXT</option>";
$dd=$dd."<option value=ENUM>ENUM</option>";
$dd=$dd."<option value=SET>SET</option>";
$dd=$dd."</select>";
return $dd;
}
function ret_lng($ty)
{
for($i=0;$i<2;$i++)
{
$t=split('[#]',$ty);
print($t[$i]."<BR>");
}
}
}
?> | |
|
|
| TAB_STRUCT Class: Is supporting Class for the DBXML Class
Categories : PHP, PHP Classes, MySQL, XML, Databases | | | PostGreSQL and MySQL 2 in 1 db Manager
Categories : PHP, PHP Classes, Databases, PostgreSQL, MySQL | | | [PHP5] aDB PDO LIKE Database Abstraction. Switch easily from one db server to another, strong errors management, manage transactions, queries preparation and more.
Categories : PHP, PHP Classes, Databases, MS SQL Server, MySQL | | | Password reminder
Categories : PHP, PHP Classes, Databases, MySQL, Mail | | | MySQL Connection/Query Class
Categories : Databases, MySQL, PHP, PHP Classes | | | Specify your connection settings and create a link to a MySQL database.
Categories : PHP, PHP Classes, Databases, MySQL, Beginner Guides | | | Online Automatic Class Generator for MySQL Tables
Categories : PHP, PHP Classes, Classes and Objects, Databases, MySQL | | | Create and restore backup of MySQL databases
Categories : MySQL, Databases, PHP, PHP Classes, Complete Programs | | | This class splits the results of the query into multiple pages like what the search engine does.
Categories : PHP Classes, PHP, MySQL, Databases | | | MySQL Class to ease Database connectivity
Categories : MySQL, PHP Classes, Databases, PHP | | | phpFormGenerator for Dynamic Form Generation from MySQL
Categories : PHP, PHP Classes, MySQL, Databases, HTML and PHP | | | MySQL database class
Categories : PHP, MySQL, Databases, PHP Classes | | | Setting up InnoDB on MySQL and using Transactions Begin, Commit, Rollback in PHP.
Categories : PHP Classes, Databases, PHP, MySQL, InnoDB | | | Ajax PHP Tree (Left and Right) with MySQL
Categories : PHP, Databases, MySQL, AJAX, PHP Classes | | | YellowPages Content Grabber (PHP5 +)
Categories : PHP, PHP Classes, Regexps, Databases, MySQL | |
|
|
