Skip to content

Instantly share code, notes, and snippets.

@cacycleworks
Last active May 29, 2018 16:17
Show Gist options
  • Save cacycleworks/546137adc92dc00c0f5390032bf07883 to your computer and use it in GitHub Desktop.
Save cacycleworks/546137adc92dc00c0f5390032bf07883 to your computer and use it in GitHub Desktop.
MySQLi helper functions I use to describe and understand tables. Great for Magento.
<?php
// (c) Chris Kelley of "Tech Nerd Chris", technerdchris at gmail
// This is my dev code to speed understanding of databases. Put up a search form
// and use this to render HTML results of table structures
//
// $db = mysqli handle
// optional $tbl_order lets you send in table names as an array to set order or limit results
function describe( $db, $tbl_order='' ){
$_colors =array( 'red' => "red", 'orn' => "orange", 'blue' => "#06f", 'gray' => "#666", 'grn' => "#0a3" );
foreach( $_colors as $var => $_c )
if( !isset($$var) )
$$var = " style=\"color:$_c\" ";
echo "<fieldset style=\"display: inline-block;border:none\">";
$sql="SELECT database() AS 'db_name'";
$db_name=oneRow($db, $sql);
$db_name=$db_name['db_name'];
$sql ="SELECT TABLE_NAME AS 'table', TABLE_ROWS AS 'rows' FROM information_schema.TABLES ";
$sql.="WHERE TABLES.TABLE_SCHEMA = '$db_name' AND TABLES.TABLE_TYPE = 'BASE TABLE'";
$show_tables=multiRow($db,$sql);
$err=mysqli_error($db);
if(strlen($err))
echo($sql."\nError description: $err\n" );
if( count( $show_tables) > 0 ) {
foreach($show_tables as $arr){
$_tbl=$arr['table'];
$_rws=$arr['rows'];
$tables[$_tbl]['table']=$_tbl;
$tables[$_tbl]['rows']=$_rws;
}
}
$tbl_order = array();
if( strlen($tbl_order) > 5 )
$tbl_order = explode( ',', $tbl_order);
else
$tbl_order=array_keys($tables);
$keys = array('Field','Type','Null','Key','Default','Extra');
$fieldname_width=0;
echo "<h4>database `$db_name`</h4>";
foreach( $tbl_order AS $table ){
$sql="SHOW FULL COLUMNS FROM $table";
$_desc=multiRow($db, $sql);
// Process the output
foreach( $_desc as $idx => $col ) {
// determine max fieldname width
if( strlen( $col['Field'] ) > $fieldname_width )
$fieldname_width=strlen( $col['Field'] );
}
$fieldname_width+=2; // add padding for the ` `
$_f = sprintf( "%%%ds", $fieldname_width );
printf("<div style=\"float:left;border:solid thin #666;padding:3px\">");
echo "<pre>";
echo "<h4>$table </h4>";
num_records( $db, $table );
$table_format_string = "$_f <small $gray>%-14.14s</small> %4s %3s <small><span $blue>%s</span> <span>%s</span> <span $orn>%s</span></small>\n";
printf( $table_format_string, 'Field','Type','Null','Key','Default ','Extra', 'Comment' );
foreach( $_desc as $col ) {
printf( $table_format_string
, '`'.$col['Field'].'`'
, $col['Type']
, $col['Null'] =='YES' ? 'yes' : " <span $red>NO</span>"
, $col['Key']
, strlen($col['Default']) > 0 ? $col['Default']." " : "<small $blue>┈ </small> "
, strlen($col['Extra']) > 0 ? $col['Extra']." " : "<small>┈ </small> "
, strlen($col['Comment']) > 0 ? $col['Comment']." " : "<small $orn>┈ </small> "
);
}
printf("</pre></div>" );
}
printf("</fieldset>");
}
// $db=mysqli handle, $findfield is what you're searching for
// Optional $hideZeroes: if set to true, hides tables with no rows.
// This code is lifted from development and altered for public sharing
// ... this fundtion calls describe() above
function table_search( $db, $findfield, $hideZeroes=0 )
{
$sql="SELECT database() AS 'db_name'";
$db_name=oneRow($db, $sql);
$db_searched=$db_name['db_name'];
if( $hideZeroes==true; ) {
printf( '<P><small style="color:#666">--> hiding empty tables (which do not have any rows of data)</small></P>' );
}
$and_where = "";
if (true===$hideZeroes)
$and_where = " AND TABLE_ROWS > 0 ";
$tables=Array();
$sql ="SELECT TABLE_NAME AS 'table', TABLE_ROWS AS 'rows' FROM information_schema.TABLES ";
$sql.="WHERE TABLES.TABLE_SCHEMA = '$db_searched' AND TABLE_NAME LIKE '%$findfield%' $and_where AND TABLES.TABLE_TYPE = 'BASE TABLE'";
$show_tables=multiRow($db,$sql);
$err=mysqli_error($db);
if(strlen($err))
echo($sql."\nError description: $err\n" );
if( count( $show_tables) > 0 ) {
foreach($show_tables as $arr){
$_tbl=$arr['table'];
$_rws=$arr['rows'];
$tables[$_tbl]=$_rws;
}
}
$cols=Array();
$sql ="SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS ";
$sql.="WHERE TABLE_SCHEMA='$db_searched' AND COLUMN_NAME LIKE '%$findfield%'";
$show_tables=multiRow($db,$sql);
$err=mysqli_error($db);
if(strlen($err))
echo($sql."\nError description: $err\n" );
if( count( $show_tables) > 0 ) {
foreach($show_tables as $arr){
$_col=sprintf( "<span $gray>%s.</span>%s", $arr['TABLE_NAME'], $arr['COLUMN_NAME'] );
$cols[]=$_col;
}
} // else echo "No results from query:\n$sql:\n";
printf("<div style=\"float:left;border:solid thin red;padding:3px\"><pre>");
if (count($tables)) {
printf("%d tables with '%s' in the name:\n", count($tables), $findfield);
foreach($tables as $name=>$count )
printf("\t%s <small style=\"color:#666\">[%d]</small>\n", $name, $count);
} else
echo "No tables have '$findfield' in their name.\n";
printf("</pre></div>" );
printf("<div style=\"float:left;border:solid thin green;padding:3px;margin-left:5px\"><pre>");
if (count($cols) > 0) {
printf("%d fieldnames with '%s' in their name:\n\t%s\n", count($cols), $findfield, implode("\n\t",$cols));
} else
echo "No columns have '$findfield' in their name.\n";
printf("</pre></div>" );
printf("<div style=\"clear:both\">");
if( isset($tables) && count($tables) > 0 ){
printf( "<P>&nbsp;</P><h3>DESCRIBE `table` for each of %d tables above...</h3>", count($tables) );
printf('<div>'); // style="width:1610">');
describe( $db, implode( ',', array_keys($tables) ));
('</div>');
printf('<div style="clear:both"></div>');
}
}
// $db is mysqli handle
function oneRow($db, $sql) {
if( is_string($db) || is_null($sql) ) {
printf( "<pre>Incorrect usage: oneRow( db handle \$db, string \$sql ) both required.</pre>\n");
return false;
}
try {
$result=$db->query($sql);
} catch (Exception $e) {
printf( "<pre>An error occurred: %s\n\t%s:\n\t%s</pre>\n", $e, $sql, $db->error);
}
$data = false;
if( !is_bool($result) )
$data = $result->fetch_assoc();
return $data;
}
// $db is mysqli handle
function multiRow($db, $sql) {
global $red;
if( is_string($db) || is_null($sql) ) {
printf( "<pre>Incorrect usage: multiRow( db handle \$db, string \$sql ) both required.</pre>\n");
return false;
}
$result=$db->query($sql);
if ($result===false) {
printf("<span>ERROR:</span>\n%s\n<span>Server response:</span> %s\n", $sql, $db->error);
return( false );
}
$data = Array();
if( !is_bool($result) )
while( $row = $result->fetch_assoc() )
$data[]=$row;
return $data;
}
// $db is mysqli handle
function query( $db, $sql ) {
$result=$db->query($sql);
if ($result===false) {
printf("<span>ERROR:</span>\n%s\n<span>Server response:</span> %s\n", $sql, $db->error);
return( false );
}
$_rows = $db->affected_rows;
if( is_numeric( $_rows ) && $_rows > 0 )
return $_rows;
return true;
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment