Source Viewer

ShowTableNow.php : main PHP server side code; has dev portions not used here.

<?php
//error_reporting(E_ALL);
//ini_set('display_errors', 'On');

include_once("../inc/mysql2i.class.php");	// deprecated php5-mysql api to php7-mysqli, work-around

//var_dump(session_id()); echo"<br/>";
session_start();

if(!isset($_SESSION['views'])) {
    $_SESSION['views'] = 1;
    $_SESSION['ses_id'] = session_id();
    $_SESSION['ses_num'] = 42;	// should be a server wide var ++d
    $_SESSION['ses_time'] = time();
    $_SESSION['user_ip'] = $_SERVER['REMOTE_ADDR'];
} else {
    $_SESSION['views'] += 1;
}

//diag: foreach ($_SESSION as $item=>$val) echo " $item = $val<br/>";

// 		Authenticates the user & doing Login as needed
//===================================================================
//if (!isset($_SESSION['user_id'])) {
//  $_html = file_get_contents("Login.php");
//  // fill in his specifics
//  $_html = str_replace("{user_ip}",$_SESSION['user_ip'],$_html);
//  $_html = str_replace("{ses_time}",$_SESSION['ses_time'],$_html);
//  echo $_html;	// output this portion of this page
//  exit;	// done for now, till he gets logged in
//}

//-------------------- User session is logged in -----------------------
// $user_id = $_SESSION['user_id'];
// $user_name = $_SESSION['user_name'];
 $permissions = explode(",",$_SESSION['access']);
 //var_dump($access);

// ----------- Fetch request parameters
  $table_name = !empty($_REQUEST['table_name'])? $_REQUEST['table_name']:null;
  $items = !empty($_REQUEST['items'])? $_REQUEST['items']:'*';
  $where = !empty($_REQUEST['where'])? $_REQUEST['where']:null;
  $order = !empty($_REQUEST['order'])? $_REQUEST['order']:null;

$table_sql = "SELECT $items FROM `$table_name` \n";
  if ($where) $table_sql .= " WHERE " . $where . "\n";
  if ($order) $table_sql .= " ORDER BY " . $order;

// Disalow code injection into the SQL query
  // -----------------------------------------
  $uinput = $items . $where . $order;
  if ((strstr($uinput,';') . strstr($uinput,'--') . strstr($uinput,'/*') . strstr($uinput,'#') .
      strstr($uinput,'()') . stristr($uinput,'select') . stristr($uinput,'script')) ||
      $uinput != strip_tags($uinput) ) {
      $table_sql = "select 'The generated query violates security!';";
      $permissions =  null;
      $table_name = strip_tags($table_name);
//      $items = '*';
//      $where = '';
//      $order = '';
  }

$items = strip_tags($items);
  $where = strip_tags($where);
  $order = strip_tags($order);
  $table_sql = strip_tags($table_sql);

// ----------- if requested log em' out
  if( !empty($_REQUEST['logout']) ){
	unset($_SESSION['user_id']);
	unset($_SESSION['access']);
	unset($_SESSION['ses_id']);
	echo ("<h3><center>You have been logged out.</center></h3>");
	exit;
  }

$title = "Table SQL Viewer";
  // ---------- connect to the MySQL DB table used on this page
  $dbname = 'microapps';
  include('../../www-conf/DBconfig.lib.php');

// ============================  Do a Download if requested
  if( !empty($_REQUEST['download']) ){
	if (!in_array('admin',$permissions) && !in_array('download',$permissions)) {
		echo "<script>alert('Security Violation! You do not have the proper approval to perform the requested operation !')</script>";
	} else {
//echo ("<pre>");print_r($_REQUEST);echo ("</pre>");
        header('Content-Description: File Transfer');
        header("Content-Type: application/force-download");
        header("Content-Disposition: attachment; filename=table_download.txt");
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
        header('Pragma: public');
        ob_clean();
        flush();

//$stmt = "select * FROM {$_REQUEST['table_name']}";
		$stmt = $table_sql;
  		$query = mysql_query($stmt);
		$results = array();
		while($result = mysql_fetch_array($query)) {
			$results[] = $result;
		}

$content = $results;
		$txt = '';
		// on the first line give the field names
		foreach ($content[0] as $field=>$val){
        	if (is_string($field)) {
        	  $txt .= (empty($txt))? $field : "\t".$field;
        	}
		}
		echo ("$txt\r\n");

foreach($content as $row){
        	$txt = '';
			foreach ($row as $field=>$val){
        	  if (is_string($field)) {
				$txt .= (empty($txt))? $val : "\t".$val;
			  }
			}
        	echo ("$txt\r\n");
        }

//flush();
		exit;
	}
}

// ============================  Otherwise build the web page
  include('std_page.inc.php');
  head($title);	// generate the site wide std top section of the page
  echo "<small><font color='black'>This form allows you to experiment with basic SQL ... see both results & the query used. &nbsp; &nbsp; (fill in just those text entries you care to before exersizing a new SQL query.)</font></small>";

// -------------- provide a Selector with the table names in it that can be choosen
  $sql = "SELECT table_name FROM INFORMATION_SCHEMA.TABLES
	WHERE table_schema = '$dbname'";
  $query = mysql_query($sql);
  $rows = mysql_num_rows($query);

echo "<div id='outer_div'>";
  echo "<h5>Tables</h5>";

$tables = array();
  while($result = mysql_fetch_array($query)){
  	//echo $result[0]; echo "<br/>";
//	if (in_array('admin',$permissions) || !preg_match('/^admin_.*/', strtolower($result[0]))) {
	if (in_array('admin',$permissions) || !preg_match('/^admin_sessions.*/', strtolower($result[0]))) {  // use while testing
	    $tables[] = $result[0];
	}
  }
 ?>

<select style="width: 320px;" name="table_name" id="table_name" value="???" onChange="javascript:document.location.href='ShowTableNow.php?table_name=' + $('#table_name').val();">
            <option value="" >- Select a Table -</option>
 <?php
      foreach ($tables as $table) {
         echo ("<option value='$table'" . (($table==$table_name)? " selected='selected'>":'>') ."$table</option>");
      }
 ?>
		</select>
<?php

if (!in_array($table_name,$tables) && !empty($table_name)) {
		echo "<script>alert('The requested Table is not among those accessable !\\nPlease select one from the pulldown.');</script>";
		$table_name = '';
	}

// ---------------------------- If he has selected a table then build all the sections regarding it
if ( !empty($table_name) ) {

// --------------- create the Table Structure section
  sleep(1);	// this is for effect to let the user is the "Loading..." spinner
  $sql = "DESCRIBE $table_name";
  $query = mysql_query($sql);
  $rows = mysql_num_rows($query);
  echo ("<div onClick=\"$('#tbl_struct').slideToggle(1000);\" style='cursor:pointer; color:Gray'>table structure &gt;&gt; </div>");
  echo ("<pre id='tbl_struct' style='display: none;'>");

$tbl_fields = '';
  while($result = mysql_fetch_array($query)) {
	  $i = 1;
      foreach ($result as $col_name=>$data) {
        if (is_string($col_name)) {
			if ($i==1) {
				echo ("\t");
				$tbl_fields .= "<li><a onClick=\"javascript: add2select('$data');\">$data</a></li>";
			}
        	if (!empty($data)) {
        		if ($data=='YES') {
        			echo ($col_name ."\t");
        		} elseif ($data=='NO') {
        			echo ('NOT ' . $col_name ."\t");
        		} else {
					if ($i<3) echo (str_pad($data,20));
					else echo ($data ."\t");
        		}
        	}
			$i++;
        }
      }
	  echo ("\n");
  }
  echo ("</pre>");

// --------------- customize the the template for the main section of the page
  $main_html = file_get_contents("Content_ShowTable.tpl.html");
  // fill in page sections as needed
  $main_html = str_replace("{user_name}",$user_name,$main_html);
  $main_html = str_replace("{main-content}","<p/>",$main_html);
  $main_html = str_replace("{menu}","",$main_html);
  $main_html = str_replace("{sidebar}", "<div style='width:30px;'> </div>",$main_html);
  $main_html = str_replace("{extras}","",$main_html);
  $main_html = str_replace("{table_name}",$table_name,$main_html);
  $main_html = str_replace("{tbl_fields}",$tbl_fields,$main_html);
  $main_html = str_replace("{items_val}",$items,$main_html);
  $main_html = str_replace("{where_val}",$where,$main_html);
  $main_html = str_replace("{order_val}",$order,$main_html);
  echo $main_html;	// output the main portion of this page

// --------------- Fetch the table DATA
  $sql = $table_sql;
  //echo ("<pre>" . $sql . "</pre>");

$query = mysql_query($sql);
  $rows = mysql_num_rows($query);
  $title = $table_name;
  if($rows == 0){
    $title = 'No table data found';
  }

// ---- Prevent code injection going to the browser
  $sql  = mysql_real_escape_string(trim($sql));

// --------------- form the HTML for the Table section
?>
  <p>
  <h1 class="sectionLabel"  style="display:inline-block;"><?php echo $title; ?></h1>
  
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Results of: &nbsp; &nbsp;<?php echo $sql; ?>
  </p>
  <table id="dataTable" width="800" cellpadding="0" cellspacing="0" border="1" align="center">
  <?php
  if($rows > 0){
		$first_row=true;
  ?>
	<tr>
	   <?php	// ----------  Output the Table contents
	   $col_names = array();
	   //$colOpts = "<option value='' ></option>";
	   $order_fields = '';
       while($result = mysql_fetch_array($query)){
        if ($first_row) {	// ----  build the table header with Column Nmaes
        	$first_row = FALSE;
        	echo "<tr>";
        	foreach ($result as $col_name=>$data) {
        	  if (is_string($col_name)) {
        		echo "<th>$col_name</th>";
        		$col_names[] = "'" . $col_name . "'";
        		//$colOpts .=	"<option value='$col_name' >$col_name</option>"; // setup col options picker
				$order_fields .= "<li><a onClick=\\\"javascript: add2order('$col_name');\\\">$col_name</a></li>";
        	  }
        	}
        	echo "</tr>";
        }
		echo "<tr>";
		// ----  fill in the columns with data
		foreach ($result as $col_name=>$data) {
		  if (is_string($col_name)) {
			if ($col_name===NULL) echo "<td>NULL</td>";
			else echo "<td>$data</td>";
		  } else {
		    if ($col_name===NULL) echo "<td>NULL</td>";
		  }
		}
		echo "</tr>";
  }
      ?>

</td>
	</tr>
  </table>
  <br/><br/>

<script type="text/javascript">
	$(document).ready(function(){
		suggestions = new Array( <?php echo join(",",$col_names); ?> );
		//$('#col_choices').html("<?php echo $colOpts; ?>");
		$('#order_fields_list').html("<?php echo $order_fields; ?>");
		<?php if (!in_array('admin',$permissions) && !in_array('download',$permissions)) {
			echo (" $('#downloadData').css('display','none');\n");
		} ?>
	});

<?php
  }
}
	//$sql = str_replace("'"," ",$sql);
//	if (empty($table_name)) echo ("<span title=\"$sql\" style='color:#B0B0B0;'><b> mouse over to view SQL </b></span>");
	echo ("</div>");	// end of the outter div
//    foot();	// generate the site wide std bottom section of the page
//    $bottom = file_get_contents("../inc/bottom.ssi");
//    $bottom = str_replace('</span>', '</span> | <a href="viewSrc.php?src=ShowTableNow"> view server code </a> ', $bottom);
    $bottom = file_get_contents("../inc/bottom_src.ssi");
    $bottom = str_replace('viewSrc.php', 'viewSrc.php?src=ShowTableNow', $bottom);
    echo $bottom;

std_page.inc.php : some common page pieces/parts

<?php
function head($title="Default") {

$site_meta = file_get_contents('../inc/site_meta.ssi');
$html = <<<HTML
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html lang="en">
<head>
  <title>$title</title>
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
  <meta name="author" content="Ron Miller" />
  $site_meta
  <style type="text/css" media="screen"> @import "css/styles.css"; </style>

</head>
 <body>
 
 <div xheigth="80px" style="background:#E02020;">

<div style="display:inline-block;">	<center><h2 class="header" style="margin-left:60px;">PHP-SQL sample <font size=2> - illustrates SQL statements and PHP coding</font></h2></center></div>
</div>
 <div style="float:right; color:#444444;" ><small>Hello, {user_name} |
	<a onclick="$('#outer_div').load('showtable.php?logout=true')"/> (logout)</a></small>
 </div>

HTML;
	$html = str_replace("{user_name}",$_SESSION['user_name'],$html);
	echo $html;
}

function foot() {

echo <<<HTML
<br/>
<hr/>
  <font size="-1">
<p class="copyright footer" style="text-align:center;">&copy; 2011 DR.Miller. All Rights Reserved. Click here for trademark and copyright information. </p>
</font>
  <div id="debug" class="debug">
  </div>
 </body>
</html>
HTML;

}

Content_ShowTable.tpl.html : template; used to form the final page, in realtime.

<div id=menu>{menu}</div>
<table>
  <tr>
  	<td> <div id="sidebar" class="sidebar">{sidebar}</div></td>
  	<td> <div id="main-content">
  	{main-content}

Select the fields of interest ...
<div  class="dropbox">
<span><a class="hide"><u>Fields</u></a>
	<ul>
	{tbl_fields}
	</ul>
</span>
</div>

<br/>
  	<b>SELECT </b>
  	<input name="items" id="items" type="text" style="text-align:center; width:500px;" value="{items_val}" />

<br/><br/>
  	Filter out the table data ... &nbsp; <font color='black' size=1> e.g. USER_ID>200 or  UPD_USER='rmiller'</font><br/>
  	<b>WHERE </b>
<span>
  	<input name="where" type="text" class="autotext" index="0" style="width:500px;" value="{where_val}"  autocomplete="off"/> <br/><br/>
</span>
	<div class="shadow"><div class="output"></div></div>

Sort that resulting data set ...<br/>
  	<b>ORDER BY </b>
  	<input name="order" id="order" type="text" style="width:400px;" value="{order_val}" />
	<div  class="dropbox"> <span><a class="hide"><u>Fields</u></a>
		<ul id="order_fields_list"> </ul>
	</span></div>

</form>
  	</div></td>
  </tr>
  <tr><td><div>{extras}</div></td>
  </tr>
</table>
<div id='diag'></div>
<div id='diag0'></div>
<div id='diag1'></div>

Viewing source code files from the server

which support rendering for: ShowTableNow

ShowTableNow.php : main PHP server side code; has dev portions not used here.

std_page.inc.php : some common page pieces/parts

Content_ShowTable.tpl.html : template; used to form the final page, in realtime.