Location: PHPKode > scripts > PHP MySQL Smart > php-mysql-smart/index.php
<?
session_start();
set_time_limit(120);

if($_REQUEST[clearsql]) $_SESSION[OLDSQL]='';
?>
<!--
**********************************************
* 2008 by Alexander Ottitzky
* http://www.powerweb99.at/
* for free use, no warranty at all
* please do not remove copyright
**********************************************
-->
<html>
<head>
<title>PHP MySQL Smart by PowerWeb99.at 3.0</title>
<style>
.dbs{
	font-weight: normal; 
	color: black;
	font-size: 11px; 
	font-family: Verdana; 
	text-decoration: none;
}
.dbs a{
	font-weight: normal; 
	color: black;
	font-size: 11px; 
	font-family: Verdana; 
	text-decoration: none;
}	
.tbs{
	font-weight: normal; 
	color: black;
	font-size: 10px; 
	font-family: Verdana; 
	text-decoration: none;
}
.tbs a{
	font-weight: normal; 
	color: black;
	font-size: 10px; 
	font-family: Verdana; 
	text-decoration: none;
}	
.fls{
	font-weight: normal; 
	color: black;
	font-size: 10px; 
	font-family: Verdana; 
	text-decoration: none;
}
.fls a{
	font-weight: normal; 
	color: black;
	font-size: 10px; 
	font-family: Verdana; 
	text-decoration: none;
}	
.buttons {
	font-family:Arial;
	font-size:10;
	border-style:solid;
	border-width:1px;
	border-color:black;
	background:#F2F2F2; 
	margin:1px;
	height:22px;
	width:100%;
	v-align:middle
}
select{
	font-family:Arial;
	font-size:10;
	width:100%;
	height:22px;
}
</style>


</head>
<body bgcolor=white>

<?
$user = $_REQUEST[user];
$pass = $_REQUEST[pass];
$host = $_REQUEST[host];

if($_REQUEST[disconnect]=="disconnect"){
	unset($_REQUEST);
	session_unset();
	session_destroy();	
}

if($_SESSION[LOGIN] || ( $user && $host)){
	if(!$user) $user = $_SESSION[LOGIN][USER];
	if(!$pass) $pass = $_SESSION[LOGIN][PASS];
	if(!$host) $host = $_SESSION[LOGIN][HOST];
	$con = @mysql_connect($host, $user, $pass) or die("<center><b>Connection Error</b></center>");
}

function usedtime() {
	list($msec,$sec)=explode(' ',microtime());
	return $msec+$sec;
}

$tstart=usedtime();

function dblist($db=false, $tb=false){
	global $con;	
	$result = mysql_list_dbs($con);
	while ($rs = mysql_fetch_object($result)) {
		$b="";
		$tmp = $rs->Database;
		if($db==$rs->Database) {
			$tmp="<b>$tmp</b>";
			$b.=tablelist($db, $tb);
		}
		$a.="<tr class=dbs><td colspan=3 width=100%   title='use Database $rs->Database' ><a href='javascript:add2board(\" $rs->Database \")' title='write dbname to sql-box'>&raquo;</a> <span style='cursor:pointer;'  onclick=\"choose('$rs->Database','');\">$tmp</span></td></tr>\n$b\n";		
	}
	mysql_free_result($result);	  	
	
	return $a;
}

function tablelist($db, $tb=false){
	global $con;
	mysql_select_db($db,$con) or die("select error!");

	$result = mysql_list_tables($db,$con);	
	for($i=0; $i < mysql_num_rows($result); $i++) {
		$b="";
		$table = mysql_tablename($result,$i);
		$tmp = $table;
		if($tb==$table) {
			$b.=fieldlist($db, $tb);
			$tmp="<b>$tmp</b>";
			
		}
		$a.="<tr class=tbs><td width=10%>&nbsp;</td><td width=90%  title='use table $table' colspan=2><a href='javascript:add2board(\" $table \")' title='write tablename to sql-box'>&raquo;</a> <span style='cursor:pointer;' onclick=\"choose('$db','$table');\">$tmp</span></td></tr>\n$b\n";
	}

	mysql_free_result($result);
	return $a;
}

function fieldlist($db, $tb){
	global $con;
	mysql_select_db($db,$con) or die("select error!");
    $result = mysql_query("select * from $tb limit 1") or die("error!");
    $rs = mysql_fetch_object($result);
  
    for ($i=0;$i<mysql_num_fields($result); $i++){
        $feld = trim(mysql_field_name($result,$i));
        $len = mysql_field_len($result,$i);
        $type = mysql_field_type($result,$i);
        $flag = mysql_field_flags($result,$i);
        $a.=("<tr class=fls><td width=10%>&nbsp;</td><td width=10%>&nbsp;</td><td title='$type($len) $flag' width=80%><a href='javascript:add2board(\" $feld \")'>&raquo;</a> $feld</td></tr>\n");
    }
	mysql_free_result($result);
	return $a;
}

function describing($tb, $db){
		if($db) $sql = "SHOW TABLE STATUS";
		if($tb) $sql= " DESCRIBE $tb";
		$result = mysql_query($sql) or die("<div class=tbs align=left><b>SQL Error</b><br>".mysql_error()."</div>");
		$rs = mysql_fetch_array($result);
		$desc.="<tr style='font-weight:bold;color:white'>\n";
		for ($i=0;$i<mysql_num_fields($result); $i++){
			$feld = mysql_field_name($result,$i);
			$desc.="<td bgcolor=FF6637><b>$feld</b></td>\n";
		}
		$desc.="</tr>\n";
		mysql_free_result($result);	
		$result = mysql_query($sql) or die("<b>SQL Error</b><br>".mysql_error());
		while($rs = mysql_fetch_array($result)) {
			$rn++;
			$desc.="<tr>\n";
			for($j=0;$j<$i;$j++){
				$back="#E9EEF6";
				if (bcmod($rn,2)==0) $back="#f1f1f1";
				$value = $rs[$j];
				if(isset($value) && !is_null($value)) {
					$desc.="<td bgcolor=\"$back\">$value</td>";
				}else{
					$desc.="<td bgcolor=\"$back\">NULL</td>";
				}
			}
			$desc.="</tr>\n";
		}
		mysql_free_result($result);	
		if($desc) $desc = "<br><table cellspacing=1 cellpadding=2 class=tbs width=100%>$desc</table>";
	return $desc;
}

function get_table_structure($db,$tb){
	global $con;
	mysql_select_db($db,$con) or die("select error!");
    $a = "\ndrop table if exists $tb;\ncreate table $tb (";
	$sql = "describe $tb";
	$result = mysql_query($sql) or die("<b>SQL Error</b><br>".mysql_error());
	while($rs = mysql_fetch_array($result)) {
        $null = " NOT NULL";
		if($rs[2]) $null = " NULL";
		$default = "";
		if($rs[4]) $default = " default '$rs[4]'";
		if($rs[3]=="PRI") $primary = "\n\tPRIMARY KEY  (`$rs[0]`),";
		if($rs[3]=="MUL") $key.= "\n\tKEY `$rs[0]` (`$rs[0]`),";
        $a.="\n\t`$rs[0]` $rs[1] $null$default $rs[5],";
    }
	$a.="$primary$key";
	if($a) $a = substr($a,0,strlen($a)-1);
	$a.="\n);\n\n";
	mysql_free_result($result);
	return $a;
}

function backup($table, $structure){
	global $con,$current_database;				
	$a = get_table_structure($current_database, $table);
	if($structure!="structure"){
		$sql= ("select * from $table");
		$result = mysql_query($sql) or die("SQL ERROR:<hr>".$sql);
		while($rs = mysql_fetch_object($result)) {		         
			$b = ("insert into $table values (");
			foreach($rs as $tmp1 => $key){
				$key=str_replace("\n","<br>",$key);
				$key=str_replace("\r","<br>",$key);
				$b.= "'".addslashes($key)."',";
			}
			$a.= substr($b,0,strlen($b)-1).");\n";
		}
		mysql_free_result($result);
	}
	$a."\n";	
	return $a;			
}

if($con){
	$_SESSION[LOGIN][USER] = $user;
	$_SESSION[LOGIN][PASS] = $pass;
	$_SESSION[LOGIN][HOST] = $host;
	$current_database = $_REQUEST[db];
	$current_table = $_REQUEST[tb];
	
?>
<script>
function add2board(a){
	document.me.sql.value = document.me.sql.value + a;
}

function clearSQL(){
	document.me.sql.value='';
	document.me.action='index.php?db=<?=$current_database?>&tb=<?=$current_table?>&clearsql=true';
	document.me.submit();
}

function choose(db,table){
	document.me.action='index.php?db='+db+'&tb='+table+'&selected=true';
	document.me.submit();
}

function copySQL(){	
	var myindex  = document.me.OLDSQL.selectedIndex;
    if(myindex>0){
		document.me.sql.value=document.me.OLDSQL[myindex].value;
	}
}

</script>
<?	
	$sql = stripslashes($_REQUEST[sql]);

	$sqlarray = split(";(\n|\r)",$sql);		
	$to_old = trim($sqlarray[0]);
	if(strlen($to_old)>1 && strpos($_SESSION[OLDSQL],">$to_old<")<1 && $_REQUEST[execute]=="execute sql query"){
		$_SESSION[OLDSQL]="<option value=\"$to_old \">$sqlarray[0]</option>".$_SESSION[OLDSQL];
	}
	$max = $_REQUEST[max];
	if(!$max || !is_numeric($max)) $max=50;
	if(!$maxrow || !is_numeric($maxrow)) $maxrow=200;
 
	echo("<table width=100%  height=99% style='border-style:solid; border-width:1px 1px 1px 1px; border-color:#FF6637;'>");   
    echo("<tr><td height=28 valign=middle colspan=2 bgcolor=#FF6637 valign=top class=dbs align=right><a href='http://www.powerweb99.at'><span style='color:white;'> <b>PHP MySQL Smart 3.5</b></span></a>&nbsp;</td></tr>");
	echo("<tr><td width=220 bgcolor=#E9EEF6 valign=top>");
	echo("<table border=0 cellspacing=1 cellpadding=2  width=220>".dblist($current_database, $current_table)."</table>");
	echo("</td><td width=* height=100% valign=top align=left class=tbs><form name=me method=post action='index.php?db=$current_database&tb=$current_table'>");
	
	if($current_database){
		if($_REQUEST[max]=='50')  $sqlsel[1] = "selected";
		if($_REQUEST[max]=='100')  $sqlsel[2] = "selected";
		if($_REQUEST[max]=='500')  $sqlsel[3] = "selected";
		if($_REQUEST[max]=='1000')  $sqlsel[4] = "selected";
		if($_REQUEST[max]=='1')  $sqlsel[5] = "selected";
		$backup = "<input type=submit value='dump database' name='backup' class=buttons> <input type=submit value='view database structure' name='backup' class=buttons><br>";
		if($current_table) $backup = "<input type=submit value='dump table' name='backup' class=buttons> <input type=submit value='view table structure' name='backup' class=buttons><br>";
		echo("
		<table width=100%  border=0 cellspacing=1 cellpadding=1 >
		<tr>
		<td width=120 >
		<input type=submit   value='execute sql query' name=execute class=buttons><br>
		<input type=submit value='help' name=help class=buttons><br>
		$backup <input type=submit value='disconnect' name=disconnect class=buttons><br>
		<select style='width:100%;' name=max>
			<option value='50' $sqlsel[1]>max 50 chars</option>
			<option value='100'  $sqlsel[2]>max 100 chars</option>
			<option value='500'  $sqlsel[3]>max 500 chars</option>
			<option value='1000'  $sqlsel[4]>max 1000 chars</option>
			<option value='1'  $sqlsel[5]>all chars</option>
		</select>
		</td>
		<td height=100%><textarea name=sql style='padding:2px;width:100%;height:100%;border-style:solid;border-color:#FF6637;border-width:1px;' rows=7>$sql</textarea></td>
		</tr>
		
		<tr>
		<td width=120><input type=button value='clearsql' onclick='clearSQL()' class=buttons name='clear old sql'></tD>
		<td><select onchange='copySQL()' name=OLDSQL style='width:100%;'><option value=''>-- choose one of your latest sql queries --</option>$_SESSION[OLDSQL]</select></td>
		</tR>
		
		</table>");	
	}
	
	//DESCRIBE

	if($sql && $_REQUEST[execute]=="execute sql query"){
	
		$sqlarray = split(";(\n|\r)",$sql);			

		echo("<br><b>$sql</b><br><br><table width=100% align=left cellspacing=1 cellpadding=2 class=tbs style='border-style:solid; border-width:1px 1px 1px 1px; border-color:#FF6637;'>");
		foreach($sqlarray as $sql){
			
			$firstword = strtolower(substr(trim($sql),0,strpos(trim($sql)," ")));
			//SELECT 
			if($firstword=="select" || $firstword=="show" || $firstword=="describe" || $firstword=="desc"){
				$result = mysql_query($sql) or die("<div style='color:red;'><b>SQL Error:</b><br>".mysql_error()."</div>");
				$rs = mysql_fetch_array($result);
				echo("<tr style='font-weight:bold;color:white'>\n<td bgcolor=#FF6637 width=5%><b>#</b></td>\n");
				for ($i=0;$i<mysql_num_fields($result); $i++){
					$feld = mysql_field_name($result,$i);
					echo("<td bgcolor=FF6637><b>$feld</b></td>\n");
				}
				echo("</tr>\n");
				mysql_free_result($result);	
				$result = mysql_query($sql) or die("<div style='color:red;'><b>SQL Error:</b><BR>".mysql_error()."</div>");
				while($rs = mysql_fetch_array($result)) {
					$rn++;
					echo("<tr><td bgcolor=#FF6637 style='font-weight:bold;color:white'>$rn</td>\n");
					for($j=0;$j<$i;$j++){
						$back="#f1f1f1";
						if (bcmod($rn,2)==0) $back="#f8f8f8";
						$value = $rs[$j];
						if($max!=1 && strlen($value)>$max) $value=substr($value,0,$max)."...";
						if (isset($value)  && !is_null($value)) {
							echo("<td bgcolor=\"$back\">$value</td>");
						}else{
							echo("<td bgcolor=\"$back\">NULL</td>");
						}
					}
					echo("</tr>\n");
				}
				mysql_free_result($result);	
					
			}else{
			//INSERT UPDATE
				if(trim($sql)){
					mysql_query($sql) or die("<div class=tbs align=left><b>SQL Error</b><br>".mysql_error()."</div>");
					echo("<div class=tbs align=left>$sql<br>Row(s) affected: ".mysql_affected_rows()."</div>");
				}			
			}
		}
		echo("</table>");
	}
	
	if($_REQUEST[backup]){	
		if(eregi("structure",$_REQUEST[backup])) $structure="structure";	
		
		if($structure){	
			if($current_table){
				$output = backup($current_table,$structure);
			}else{
				$trs = mysql_list_tables($current_database,$con);
				$tables=array();
				for($i=0; $i < mysql_num_rows($trs); $i++) {
					$output.= backup(mysql_tablename($trs,$i),$structure);
				}
			}
			
			echo("
			<table width=100% class=tbs  border=0 cellspacing=0 cellpadding=2 style='border-style:solid; border-width:1px 1px 1px 1px; border-color:#FF6637;'>
			<tr><td><textarea name=output style='width:100%' rows=20 wrap='off'>$output</textarea></td></tr>
			<table>
			");	
		}else{
			echo("
			<table width=100% class=tbs  border=0 cellspacing=0 cellpadding=2 style='border-style:solid; border-width:1px 1px 1px 1px; border-color:#FF6637;'>
			<tr><td><br><a target='_blank' href='dump.php?db=$current_database&tb=$current_table'><b>&nbsp;&nbsp;&raquo;&raquo; klick here to start the download ...</b></a><br><br></td></tr>
			<table>
			");	
		}
	}
	
	
	
	if($_REQUEST[help]){
		echo("
			<table width='100%' border=0 align = center class='dbs'>
			<tr><td>
			<ul>
			<b>DATABASE OR TABLE BROWSING</b><bR>
			<li>Just klick on a database and all tables are shown. By klicking a table, you will get a list of all fields.<br>
			<li>Klicking the '&raquo;' will write database/table or fieldname in the sql-textbox.
			<br><br>
			<b>EXECUTING SQL</b><bR>
			<li>Write a sql query into the textfield and klick 'execute sql query'.<br>
			<li>You can seperate a couple of statements by an ';'.
			<br><br>
			<b>BACKUP</b><bR>
			<li>select a database or a table and klick 'backup'. Then a new textfield with all delete and insert statements will appear. Copy the content of the textfield and save it to any file you want as a backup of your database or table.
			<br><br>
			<b>SQL TEMPLATES</b><bR>
			
			<li>Here are some examples of SQL Queries to copy and paste (modify):<br><br>
			<ul>
			<li>ADD FIELD: ALTER TABLE tablename ADD fieldname INT
			<br>Other types than int: varchar(255), text, char(1), bigint, date
			<br><li>DELETE FIELD: ALTER TABLE tablename DROP fieldname
			<br><li>CREATE NEW DATABASE: CREATE DATABASE dbname
			<br><li>DELETE DATABASE: DROP DATABASE dbname
			<br><li>CREATE TABLE (with id as autoincrement key): <br>
			CREATE TABLE tablename (<br>
			id INT UNSIGNED NOT NULL AUTO_INCREMENT,<br>
			person VARCHAR (255) DEFAULT '0',<br>
			PRIMARY KEY(id), UNIQUE(id), INDEX(id)<br>
			)
			<br><li>DELETE TABLE: DROP TABLE tablename
			</ul>
			</ul>
			</td>
			</table>
		");
	}
	
	if($_REQUEST[selected] && ($current_table || $current_database)) echo(describing($current_table, $current_database));
	
	echo("</form></td></tr></table>");
    mysql_close();
    $tend=usedtime();
	echo("<div align=center class=fls>".round($tend-$tstart,2)." sec.</div>");
	
} else {

	echo("
	<form name=me method=post action=index.php>
	<table width=300 class= dbs align=center border=0 bgcolor=#E9EEF6 style='border-style:solid; border-width:1px 1px 1px 1px; border-color:#FF6637;'>
	<tr>
	<td colspan=2 bgcolor=#FF6637 align=right><span style='color:white;'> <b>PHP MySQL Smart 3.5</b></span></a>&nbsp;><br><br></td>
	</tr>
	<tr>
	<td width=20%>Host</td><td><input type='text' name='host' maxlength=100 style='width:100%'></td>
	</tr>
	<tr>
	<td>User</td><td><input type='text' name='user' maxlength=100 size=20 style='width:100%'></td>
	</tr>
	<tr>
	<td>Pass</td><td><input type='password' name='pass' maxlength=100 size=20 style='width:100%'></td>
	</tr>
	<tr>
	<td align=center colspan=2><input type=submit value=connect name=connect class=buttons></td>
	</tr>
	</table>
	</form>
	<div class=fls align=center>2008 &copy; by <a href='http://www.powerweb99.at' target='_blank'>www.powerweb99.at</a></div>
	");

}

?>
</body>
</html>
Return current item: PHP MySQL Smart