Blog This!

Lee Geistlinger's Web Log

Blog Home
Blog Archives
LittleGhost Home

E-mail: geistlinger AT gmail.com

Pic 'O the Day

Gallery Index | All Pictures

Top 10 Lists

Everyone loves lists

Reviews

Books, Movies and so on

Recent Posts

• Bush and Broadband According to a recent Reuters'...

• Tunnel Vision In a recent entry, I remarked that ...

• Text vs. Not Text Many of the powerful features s...

• Religion Wars Kevin Drum - formerly of Calpundit,...

• Java Jive I mean the Java language, not the bever...

• Reflections on Scripting Languages The Web contin...

• Today, Everyone's Irish OK, it's St. Paddy's Day ...

• Economy of Scale (NOT!) There are a lot of import...

• Jobs As Commodities There are a lot of issues sur...

• Tools of the Trade I've spent the last eight or s...

Tuesday, March 30, 2004

PHP and RAD

By PHP I mean, of course, the language. (I'm using v4.1.2, but that's not the point).

By RAD, I mean Rapid Application Development - and not in the way VB seems to have co-opted the concept and promise. Getting something up and running.

Yesterday - don't ask why - I had the need to have a type of tool that I had used (helped build) in ColdFusion at another job. Basically, a Web interface to a given application's database, with a view of the database/table schema.

This ColdFusion tool - against a MS SQL Server - had a couple other bells and whistles, including the ability to set comments for a given table ("This stores all user info....") or column ("article_id is a unique key for each article entered in the system...").

Cool.

I needed both more and less.

I didn't need the comments, but I did need the ability to see - via a Web browser - the following:

A list of all databases for a given mySQL database server

For each of those databases, the tables said database

For each table, the schema (column name, type, nullable, default) for each

Now, there are tools out there (like PHPmyAdmin) that can do whole browser-based DB management, but I didn't need that much.

I just needed what I listed above: Database, drill to tables and schema. Very simple.

And - as it turns out - it was relatively simple. Yes, took some work to get it somewhat organized, but really not that big a deal.

Highly functional (read-only, as desired), totally self-contained in one file, and compact (less than 200 lines).

Cool. And this includes a little (very little, but some) error-trapping, comments, CSS style and so on. I think this is a very simple code file to follow and modify as desired. Do so.

Here's an example screenshot - notice the following functionalites:

Echos host name (set in file as an ini param)

Database selection - defaults (first hit) to first database on server (alpha); an onChange event refreshes the page upon new database selection; maintains state of selected DB

The Change DB button is there so a user can keep hitting this button to see any changes to the currently selected database (like a Window F5 keystroke)

Selection of a database will reload page with full detail of all tables in said database. Could do differently, to allow drill to tables and then drill to table detail. Coder choice

Not pictured: If the selected DB has no tables, only an error message will display, not a non-app error message or other nonsense. Fails gracefully.

And here's the code; it works for mySQL 3.2.3 on both Linux and Windows (all you have to do is change the host/username/password params).

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<meta name="description" content="Database List">
<title>Database List</title>
</head>
<style>
body,html,tr
{
font-family : verdana,arial,helvetica,sans-serif;
font-size : 12px;
font-style : normal;
background-color: FFFFFF;
color: #000000; /* black */
margin-top: 0;
margin-bottom: 0;
margin-left: 0;
margin-right: 0;
}
/* FORM DIV - for pulldown of databases */
#formDiv {
border: 1px dashed #000000;
background-color: #f1f1f1;
width:auto;
margin-top:10px;
margin-right:20%;
margin-bottom:10px;
margin-left:20%;
text-align: center;
padding-top:10px;
padding-bottom: 10px;
}
/*
errorMessage class is used to display error messages to user,
such as "No tables found"
*/
.errorMessage {
color:FF3366; /* light burgendy */
font-weight:bold;
font-family:verdana,arial,helvetica,sans-serif;
font-size:13px;
}
/******************************************************/
/* TABLE AREA */
/******************************************************/
.table {
border : solid 1px #000000;
}
/* Header columns (bold etc) */
.tableHeader {
font-family:verdana,arial,helvetica,sans-serif;
font-size:12px;
font-weight:normal;
color:FFFFFF;
background-color:CC3366;

}
/* actual table info */
.tableResults{
font-family:verdana,arial,helvetica,sans-serif;
font-size:10px;
font-weight:normal;
color:000000;
background-color:EEEEEE;
}
/******************************************************/
/* END TABLE AREA */
/******************************************************/
</style>

<body>

<?php
// Server defaults
$host = [host name];
$username = [user name];
$password = [password];

// open database connection
$chandle = @mysql_connect($host, $username, $password) or die("could not connect to server");

// Get list of databases; run everytime
$db_list_sql = "SHOW databases";
$db_list_results = @mysql_query($db_list_sql) or die("could not get DB list");
$db_list_rows = mysql_num_rows($db_list_results);

// if database name posted, set; otherwise default to first database
if (isset($_POST['my_db'])) {
$my_db = trim($_POST['my_db']);
}
else {
$my_db = mysql_result($db_list_results, 0, "database");
}

// get table list for given db
$table_list_sql = "SHOW tables FROM $my_db";
$table_list_results = @mysql_query($table_list_sql) or die("Table list failed");
$table_list_rows = mysql_num_rows($table_list_results);

?>

<div align="center"><br/><h3>HOST: <?php echo $host ?></h3></div>


<form name="dbForm" method="post" id="formDiv">
<b>Database: </b>
<select name="my_db" onChange="document.dbForm.submit();">
<?php
for ($i = 0; $i < $db_list_rows; $i++) {
$current_db = trim(mysql_result($db_list_results, $i, "database"));
if ($current_db == $my_db) {
echo "\t\t<option value=\"$current_db\" selected>$current_db</option>\n";
}
else {
echo "\t\t<option value=\"$current_db\">$current_db</option>\n";
}
}
?>
</select>
<input type="submit" value="Change DB">
</form>


<?php
// if no tables, echo out error message
if ($table_list_rows < 1) {
echo "<div align=\"center\" class=\"errorMessage\">This database has no tables</div>";
}
// this will only run if table rows exist
// nested loop: First of tables, for each table, second, display the table schema in tabular form
for ($t = 0; $t < $table_list_rows; $t++) {
$current_table = mysql_result($table_list_results, $t, "tables_in_".$my_db);
echo "<table cellpadding=\"3\" cellspacing=\"1\" border=\"0\" align=\"center\" width=\"80%\" class=\"table\">
<tr class=\"tableHeader\">
<td colspan=\"4\"><b>$current_table</b></td>
</tr>
<tr>
<td width=\"25%\"><b>Column</b></td>
<td width=\"25%\"><b>Type</b></td>
<td width=\"25%\"><b>Nullable</b></td>
<td width=\"25%\"><b>Default</b></td>
</tr>";
// get columns for given table
$column_list_sql = "SHOW columns from $my_db.$current_table";
$column_list_results = @mysql_query($column_list_sql) or die("column list failed");
$column_list_rows = mysql_num_rows($column_list_results);
// get column info
for ($i = 0; $i < $column_list_rows; $i++) {
$current_field = mysql_result($column_list_results,$i,"field");
$current_type = mysql_result($column_list_results,$i,"type");
$current_null = mysql_result($column_list_results,$i,"null");
if (strlen($current_null) == 0) { $current_null = " "; }
$current_default = mysql_result($column_list_results,$i,"default");
if ($current_default == NULL) { $current_default = "(null)";}
// echo results
echo "<tr class=\"tableResults\">
<td width=\"25%\">$current_field</td>
<td width=\"25%\">$current_type</td>
<td width=\"25%\">$current_null</td>
<td width=\"25%\">$current_default</td>
</tr>";
} // end columns
echo "</table>
<br>";
} // end tables
?>

<?php
// close connection
mysql_close($chandle);
?>
</body>
</html>

Obviously (?), such a tool should only be deployed to an admin area of a site - and there are layers of functionality, security and complexity that one can add to such an app.

But I've worked with a lot of coders who were just that: coders. NOT programmers.

A tool such as this is helpful; they don't have to pop open a database tool (or command line) to query a given database to find out if a given table has a column called "user_profile" vs. "userprofile" or if a given column was int vs. char vs. varchar(255) vs. [longer text].

Useful.

Even if not, a good exercise.

- Posted by Lee at 3:19 PM Permalink #

^Top | Top Ten Home | Blog This! Home | Blog This! Archives