im very busy lately so my posts as you can see, decrease in number. Now i want to start in a new project (lol added to the lot i've already started). It's about mysql database, a simple web application that only show all databases structure in a web browser.
You need to download jquery to let it works.
The workflow is easy: it first put in a multidimensional php array all the information
array[dbs_name][tables_name][fields_name] = field_type.
then create div inside div to graphically render the structure, helped by jquery and putting information out with php.
In the future should be interesting improving it by adding a query box that helps the user in building the query by selecting the type of the query and filling it by clicking on the graphically structure, and obviously manually filling too.
Here the code:
"db_struct.php"
This file contains the function get_structure($mysql_connection_resource) which convert the databases into the array.
function get_structure($conn_res) {
$resdbs = null;
$restbls = null;
$resclmns = null;
$resdbs = mysql_list_dbs($conn_res);
$structure = array();
while($dbs = mysql_fetch_object($resdbs)) {
$dn = $dbs->Database;
$restbls = mysql_list_tables($dn, $conn_res);
while($tbls = mysql_fetch_array($restbls)) {
$tn = $tbls[0];
$resclmns = mysql_list_fields($dn, $tn, $conn_res);
$result = mysql_query("SHOW COLUMNS FROM $tn");
if (mysql_num_rows($result) > 0) {
while ($row = mysql_fetch_assoc($result)) {
$fn = $row['Field'];
$structure["$dn"]["$tn"]["$fn"] = $row["Type"];
}
}
}
}
return $structure;
}
"web gui"
This file is the web render. You need to put your connection data and a link to jquery (i used 2.6)
<?
require_once "db_struct.php";
$conn = mysql_connect("yourhost", "youruser", "yourpass");
$s = get_structure($conn);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<script type="text/javascript" src="jquery_source"></script>
<style>
#main {
width: auto;
}
.database {
float: left;
border: 1px solid darkred;
background: #CCCCCC;
margin: 5px;
padding: 2px;
width: 300px;
overflow: auto;
}
.table {
border: 1px solid darkgreen;
background: #DDDDDD;
margin-bottom: 4px;
padding: 3px;
width: 280px;
overflow: auto;
}
.field {
border: 1px solid darkblue;
background: #EEEEEE;
margin-bottom: 2px;
padding: 2px;
width: 260px;
overflow: auto;
}
span {
font-weight: bold;
}
span.Db {
color: darkred;
font-size: 150%;
}
span.Tb {
color: darkgreen;
font-size: 130%;
}
</style>
</head>
<body>
<div class="closer">
<p>Collapse: </p>
<button id="cdb">All DBS</button>
<button id="ctb">All TBS</button>
<button id="cfd">All FDS</button>
</div>
<div id="main"></div>
<script type="text/javascript">
var main = $("#main");
<? foreach($s as $db => $tbls): ?>
var td = $("<div>");
td.html("<span class='Tit Db'><?= $db ?></span>");
td.addClass("database");
<? foreach($tbls as $tbl => $flds): ?>
var tt = $("<div>");
tt.html("<span class='Tit Tb'><?= $tbl ?></span>");
tt.addClass("table");
<? foreach($flds as $fld => $type): ?>
var tf = $("<div>");
tf.html("<i><?= $fld ?></i> : <?= $type ?>");
tf.addClass("field");
tt.append(tf);
<? endforeach; ?>
td.append(tt);
<? endforeach; ?>
main.append(td);
<? endforeach; ?>
$("#cdb").click(function() {
$(".database").toggle();
});
$("#ctb").click(function() {
$(".table").toggle();
});
$("#cfd").click(function() {
$(".field").toggle();
});
</script>
</body>
</html>
<? mysql_close($conn); ?>
