MySQL/PHP Data Dictionary Script

June 5, 2010
Last Update: February 4, 2012

This free script is designed to help database administrators, system administrators, and web developers generate data dictionaries, and determine a leaner database hierarchy.

The current version, 3.0, adds several enhancements, please see the (changelog).

Several shortcomings have been corrected in this version, including security, aesthetics, and functionality.

Because it interfaces directly with MySQL, you can allow users to use this script and it will respect the permissions from the DBMS.

Like any software that interacts with your back end, you should make sure the software is secure and operate it only over SSL. You should only allow it to be used by trusted users and inside of a secured directory.

Logging into MySQL

Login Screen for MySQL PHP Data Dictionary Script

Selecting a Database

Database selection screen for MySQL PHP Data Dictionary Script

Selecting a Table

Table selection screen for MySQL PHP Data Dictionary Script

Viewing the Dictionary

Data Dictionary screen shot for MySQL and PHP Script

Changelog

v3.0 - Major Revision, Bug Fixes and Code Updates February 4, 2012

Code updates to improve ease of reading, reduce filesize, and remove pointless characters.
Aesthetic improvements including some shadows and gradients.
Better Cross-Browser support.
Removal of built-in print button, using external print stylesheet.
Migration of built-in stylesheet to external.
Fixed annoying bug with number_format where null, non-numeric fields would cause a PHP Warning.
Negation of use of arrays for the SmartType processor to reduce needless memory consumption and element resolution.
Escaping of database, table, and fields to allow use with poorly named fields in SmartType area.
Updated header to "noindex, nofollow, nocache, noarchive" so that search engines do not cache the page.
Changed error handling for queries to follow error handling specified by PHP.INI, instead of dumping to the screen.
Header and footer functions seperate instead of "hugging" the page.
Made Robert-Lerner link open in new tab.
Log Out now takes you directly to the login page.
Now requires Mcrypt extension to protect server/username/password combination. The less-secure version 2.1 can be downloaded here that doesn't require Mcrypt.
Updated login form security information, and enhanced use by defaulting to the password box.
Added "about" page, and moved changelog to there.
Added license fetching to the about page for systems that support it.
Added donations button to about page.

v2.1 - Minor Bug Fix February 19, 2011

Initialize all variables to prevent register_globals exploits, and to remove some notices.
Update version from invalid "1.1" to "2.1".
Fix slight bug in SmartType while determining MEDIUM INT field.

v2.0 - Major Revision February 19, 2011

Fixed Table Column Headers "Field, Type, Null, Key, Null, Extra" to "Field, Type, Null, Key, Default, Extra".
Tables are sorted into colums
Addition of:
- Min Field (Shows minimum numeric entry for a numeric field, minimum character length for a string field, nothing for date field).
- Max Field (Shows maximum numeric entry for a numeric field, maximum character length for a string field, nothing for a date field)
SmartType (Makes presumptions about the current dataset to give advice on new ways to structure columns. While pretty good, some issues exist, notibly:)
- For speed, decimal fields are interpreted as a numeric field with 4-fixed floating digits, and displayed that way in MIN and MAX, however SmartType does not do a full table scan, so it cannot determine if there is a need for a float/decimal field. To be implemented later.
- Does not account for future changes, it shows the minimum required field type for the data present. A good example of when you should ignore the suggestions is in the creation of a user table, or an empty table. If you use a TINYINT UNSIGNED for the user_id column of your database (either because you just made the database, or it hasn't been used enough) then you will be limited to 255 users. Always run this on a well-used database and make your own conclusions about the desired dataset.
- Suggests numeric lengths (tinyint(1), tinyint(2), etc). They're not really needed, just keeping it with what people are used to.
- Time/Date fields have no interpretation, and are simply returned the same as they were provided.
- All string fields under 255 characters that have a dynamic range (min and max are not the same), and also do not use an INDEX will return as a varchar(MAX).
- All string fields under 255 characters that have a static range (min and max are the same) will return as CHAR(MAX).
- All numeric fields are first checked if they can be used as UNSIGNED if their MIN is >= Zero.
- Totally doesn't care what TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB, ENUM, and SET do.
Print Mode (Drops some of the styling off so that it may be printed with background colors on and preserve ink.)

v1.0 - Initial Release June 5, 2010

Download Script Here (20kb)

Posted: Thursday, April 28, 2011 at 7:46:57 PM

By:

?php
Includes the required files and checks if the seession is active. If not, redirected to the login page.
includeonce 'connect.php'
include 'config.php'
sessionstart()

if (isset($SESSION'player'))

$player$SESSION'player'

else

echo "Not Logged in brbr A href'login.php'Logina"
exit

?

html
head
link relstylesheet href"style.css"
title?php echo $sitename ?title
head

body
div class"header"
centerimg src"imageslogo.jpg" center
div
div class"sidebar"
?php
include 'sidebar.php'
?
div
div class"content"

?php
$editid $GET'ID'
$staff$POST'staff'
$password$POST'password'
$pass2$POST'pass2'
$staffstriptags($staff)
$email$POST'email'
$emailstriptags($email)
if ($staff "")

echo "You didn't enter a username.br"
echo " A href'editstaff.php?ID$editid'Go backa"
exit

if ($email "")

echo "You didn't enter a email address!br"
echo " A href'editstaff.php?ID$editid'Go backa"
exit

if ($password $pass2)

if ($password "")

$query "SELECT * from staff where id '$editid'"
$result mysqlquery($query) or die("Could not query staff table")
$result2 mysqlfetcharray($result)

$password $result2'password'

else

$passwordmd5($password)

$SQL "UPDATE staff SET name'$staff', email'$email', password'$password' WHERE id'$editid'"
mysqlquery($SQL) or die("Could not update name ". mysqlerror())
echo "Editing user $editid success."

else

echo "Password verfication failed."

?

div
head
html