Php Programming Code Examples
Php > Databases Code Examples
This script is meant for "drilling" through your db
This script is meant for "drilling" through your db
drill.php
<html>
<head>
<style>
tr,td,th { font-family: arial; font-size: 11px; color: black; white-space: nowrap; }
.BlueHeader { background-color: Gray; color: DarkBlue; font-size: 12px; font-weight: bold; white-space: nowrap; line-height: 1.2em; }
.TableHeader { background-color: Black; color: White; font-size: 12px; font-weight: bold; white-space: nowrap; line-height: 1.2em; }
.WhiteHeader { background-color: 808080; color: White; font-size: 12px; font-weight: bold; white-space: nowrap; line-height: 1.2em; }
.MainHeading { background-color: c0c0c0; color: Black; font-size: 14px; font-weight: bold; white-space: nowrap; line-height: 2.0em; }
</style>
</head>
<body>
<?php
#
#Modify as needed or desired
#
$TESTING = false;
error_reporting( ($TESTING ? E_ALL : 0) );
#
# Initialize variables used in this script
#
$thisPAGE = $_SERVER["PHP_SELF"];
$thisHILITE = "LightGreen";
#
# MySQL login information
#
$UserName = "";
$UserPass = "";
$dbHost = "localhost";
#
#Connect to the database and get a database handle
#
$dbh = mysql_pconnect ("$dbHost", "$UserName", "$UserPass") or die ("Can't connect to server");
#
# Assign URL Parameters to Variables
#
$thisDATABASE = (isset($_REQUEST["db"]) ? $_REQUEST["db"] : "");
$thisTABLE = (isset($_REQUEST["tbl"]) ? $_REQUEST["tbl"] : "");
$thisACTION = (isset($_REQUEST["axn"]) ? $_REQUEST["axn"] : "");
#
# Start buffering output
#
ob_start();
print "<table border=0>";
# display the main heading
print "<tr><td colspan=10 class='MainHeading'> DB-DRILL : $rootName @ $dbHost : $thisDATABASE : $thisTABLE</td></tr>";
#
# Get a list of available databases on server
#
$dbh_rs = mysql_query("show databases");
print "<td valign=top bgcolor=f0f0f0>";
#
# If the query succeeded, display the results
#
IF ($dbh_rs AND mysql_num_rows($dbh_rs)) :
print "<table border=0 cellspacing=0 cellpading=0>";
print "<tr><td class='TableHeader'>Databases<td></tr>";
#
# Get each database a row at a time
#
WHILE ( list($thisDB) = mysql_fetch_row($dbh_rs) ) :
# highlight the "current" database
$bgColor = ($thisDB == $thisDATABASE ? "bgcolor='$thisHILITE'" : "");
print "<tr><td $bgColor><a href='$thisPAGE?axn=tbl&db=$thisDB'>$thisDB</a><br></td></tr>";
ENDWHILE;
print "</table>";
#
# Otherwise, display appropriate message
#
ELSE :
print "<tr><td>No databases found on system server.</td></tr>";
ENDIF;
print "</td>";
#
# User requested a list of tables
#
IF ($thisACTION == "tbl") :
print "<td valign=top>";
#
# Make sure that a database is selected
#
IF ($thisDATABASE == "") :
print "Database name not given: A list of tables cannot be displayed.";
EXIT;
ENDIF;
print "<table bgcolor=black cellpadding=0 cellspacing=1 border=0>";
print "<tr bgcolor=808080><th colspan=8 class='TableHeader'>Tables in DB: $thisDATABASE</th></tr>";
print "<tr bgcolor=a0a0a0 style='color:black;'>";
print "<th align=left> Table </th>";
print "<th align=left> Type </th>";
print "<th align=left> RowFmt </th>";
print "<th align=left> Recs </th>";
print "<th align=left> FileLen </th>";
print "<th align=left> Created </th>";
print "<th align=left> LUpdate </th>";
print "<th align=left> LCheck </th>";
print "</tr>";
#
# Get a list of tables and related information
#
$dbh_rs = mysql_query("show table status from $thisDATABASE");
#
# If the query succeeded, display the results
#
IF ($dbh_rs AND mysql_num_rows($dbh_rs)) :
#
# Get the tables information a row at a time
#
$loop = 0;
WHILE ($thisROW = mysql_fetch_row($dbh_rs)) :
# alternate background color for each row
$thisBG = ($loop++%2 ? "white" : "e0e0e0");
# display table information
print "<tr bgcolor=$thisBG>";
print "<td> <b><a href='$thisPAGE?axn=fld&db=$thisDATABASE&tbl=" . $thisROW[0] . "'>" . $thisROW[0] . "</a></td>";
print "<td> " . $thisROW[ 1] . " </td>";
print "<td> " . $thisROW[ 2] . " </td>";
print "<td align=right> " . $thisROW[ 3] . " </td>";
print "<td align=right> " . $thisROW[ 5] . " </td>";
print "<td> " . $thisROW[10] . " </td>";
print "<td> " . $thisROW[11] . " </td>";
print "<td> " . $thisROW[12] . " </td>";
print "</tr>";
ENDWHILE;
#
# Otherwise, display appropriate message
#
ELSE :
print "<tr><th colspan=8 bgcolor=white>No tables were found in this database.</th></tr>";
ENDIF;
print "</table>";
print "</td>";
#
# User requested field information from a table
#
ELSEIF ($thisACTION== "fld") :
#
# Make sure that a database is selected
#
IF ($thisDATABASE == "") :
print "Database name not given: A list of tables cannot be displayed.";
EXIT;
ENDIF;
#
# Make sure that a table is selected
#
IF ($thisTABLE == "") :
print "Table name not given: A list of fields cannot be displayed.";
EXIT;
ENDIF;
#
# Get a list of tables for navigation purposes
#
$dbh_rs = mysql_query("show tables from $thisDATABASE");
#
# If the query succeeded, display the results
#
IF ($dbh_rs AND mysql_num_rows($dbh_rs)) :
print "<td bgcolor=c0c0c0 valign=top>";
print "<table border=0 cellspacing=0 cellpading=0>";
print "<tr><td class='TableHeader'>Tables</td></tr>";
# Retrieve table information a row at a time
WHILE (list($tblName) = mysql_fetch_row($dbh_rs)) :
# Highlite the "current" table
$bgColor = ($tblName == $thisTABLE ? "bgcolor='$thisHILITE'" : "");
print "<tr><td $bgColor> <a href='$thisPAGE?axn=fld&db=$thisDATABASE&tbl=$tblName'>$tblName</a></td></tr>";
ENDWHILE;
print "</table>";
print "</td>";
#
# Otherwise, display appropriate message
#
ELSE :
print "<tr><th colspan=8 bgcolor=white>No tables were found in this database.</th></tr>";
ENDIF;
print "<td bgcolor=c0c0c0 valign=top>";
#
# Get fields list from selected table
#
$dbh_rs = mysql_query("show fields from $thisDATABASE.$thisTABLE");
print "<table border=0 cellspacing=1 cellpadding=0>";
#
# If the query succeeded, display the results
#
IF ($dbh_rs) :
print "<tr><td colspan=10 class='TableHeader'>Table: $thisTABLE</td></tr>";
print "<tr><td colspan=10 class='WhiteHeader' align=center>F I E L D S</td></tr>";
print "<tr>";
print "<td class='BlueHeader'> Name </td>";
print "<td class='BlueHeader'> Type </td>";
print "<td class='BlueHeader'> Null </td>";
print "<td class='BlueHeader'> Key </td>";
print "<td class='BlueHeader'> Default </td>";
print "<td class='BlueHeader' colspan=5> Attributes </td>";
print "</tr>";
$loop = 0;
#
# Get/Display field information a row at a time
#
WHILE ( list ($col_name, $col_type, $col_null, $col_key, $col_default, $col_extra) = mysql_fetch_row($dbh_rs) ) :
# alternate background color for each row
$thisBG = ($loop++%2 ? "f0f0f0" : "e0e0e0");
# display field information
print "<tr bgcolor=$thisBG style='font-size:11px;'>";
print "<td style='white-space:nowrap; font-weight:bold;'> $col_name </td>";
print "<td style='white-space:nowrap;'> $col_type </td>";
print "<td style='white-space:nowrap;'> $col_null </td>";
print "<td style='white-space:nowrap;'> $col_key </td>";
print "<td style='white-space:nowrap;'> $col_default </td>";
print "<td colspan=5 style='white-space:nowrap;'> $col_extra </td>";
print "</tr>";
ENDWHILE;
ENDIF;
#
# Get list of indices associated with the selected table
#
$dbh_rs = mysql_query("show index from $thisDATABASE.$thisTABLE");
#
# If the query succeeded, display the results
#
IF ($dbh_rs) :
print "<tr><td colspan=10 class='WhiteHeader' align=center>I N D I C E S</td></tr>";
print "<tr>";
print "<td class='BlueHeader'> Name </td>";
print "<td class='BlueHeader'> Type </td>";
print "<td class='BlueHeader'> Unique? </td>";
print "<td class='BlueHeader'> Column </td>";
print "<td class='BlueHeader'> Seq </td>";
print "<td class='BlueHeader'> Collate </td>";
print "<td class='BlueHeader'> Cardinality </td>";
print "<td class='BlueHeader'> Packed </td>";
print "<td class='BlueHeader'> Null? </td>";
print "<td class='BlueHeader'> Comment </td>";
print "</tr>";
#
# If no indices available, display appropriate message
#
IF (mysql_num_rows($dbh_rs) == 0) :
print "<tr><td colspan=10 align=center><b>No index has been defined for this table.</b></td></tr>";
#
# Otherwise, display index/key information
#
ELSE :
$old_idx_name = "";
WHILE ( list ($idx_table, $idx_unique, $idx_name, $idx_seq, $idx_col, $idx_collate, $idx_card, $idx_sub, $idx_packed, $idx_null, $idx_type, $idx_comment) = mysql_fetch_row($dbh_rs) ) :
# Prepare field information for "readability"
$idx_unique = ($idx_unique ? "False" : "True" );
$idx_comment = ($idx_comment == "" ? "None" : $idx_comment);
$idx_card = ($idx_card == "" ? "0" : $idx_card );
$idx_null = ($idx_null == "" ? "NO" : $idx_null );
# suppress repetition of index name in case of multiple field keys
$thisIDXname = ($idx_name == $old_idx_name ? " " : $idx_name);
$old_idx_name = $idx_name;
# alternate background color for each row
$thisBG = ($loop++%2 ? "f0f0f0" : "e0e0e0");
# display index information
print "<tr bgcolor=$thisBG>";
print "<td style='font-weight:bold;'> $thisIDXname </td>";
print "<td> $idx_type </td>";
print "<td> $idx_unique </td>";
print "<td> $idx_col </td>";
print "<td> $idx_seq </td>";
print "<td> $idx_collate </td>";
print "<td> $idx_card </td>";
print "<td> $idx_packed </td>";
print "<td> $idx_null </td>";
print "<td> $idx_comment </td>";
print "</tr>";
ENDWHILE;
ENDIF;
ENDIF;
#
# Display the table creation script for this table
#
$dbh_rs = mysql_query("show create table $thisDATABASE.$thisTABLE");
#
# If the query succeeded, display the results
#
IF ($dbh_rs) :
# display header
print "<tr bgcolor=gray><td colspan=10 class='WhiteHeader' align=center>CREATE SYNTAX</td></tr>";
# track index name
$old_idx_name = "";
# Fetch the code generated by server
list ($cre_table, $cre_code) = mysql_fetch_row($dbh_rs);
# "Format" the code and print it
$cre_code = str_replace(chr(10), "<br> ", $cre_code);
print "<tr><td colspan=10>$cre_code</td></tr>";
ENDIF;
print "</table>";
print "</td>";
ENDIF;
print "</tr></table>";
#
# Flush the output buffer to the user's browser
#
ob_end_flush();
?>
</body>
</html>
