Database List

Posted on by

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).












Database List



// 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);

?>



HOST:




Database: 







// if no tables, echo out error message

if ($table_list_rows < 1) {
echo "

This database has no tables

";

}

// 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 "










";

// 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 "






";

} // end columns

echo "

$current_table
Column Type Nullable Default
$current_field $current_type $current_null $current_default

";

} // end tables

?>

// close connection

mysql_close($chandle);

?>






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.