Click here to Skip to main content
15,867,835 members
Articles / Web Development / HTML
Article

Generic DataBase Browser

Rate me:
Please Sign up or sign in to vote.
5.00/5 (3 votes)
14 Jan 2014CPOL17 min read 23.4K   459   6  
This article presents a database browser that can be used for multiple different databases

Introduction

A generic solution is presented to query, display, and browse database contents, in which it's only necessary to change the drop down selection box options in the client-side HTML file, and the connection strings in the server-side PHP file, to access any databases of the provided types.

Background

This article is taken from my book, "A Practical Guide to Developing Web 2.0 Rich Internet Applications", in which I discuss ways to build RIA / Single Page Application web sites. The complete working example can be found at the site that accompanies the book at www.web2ria.com by clicking the "Server-Side" tab and then the "Database Browser" menu item.

In the RIA site example, access is provided to 4 different data sources all at different URLs, covering 3 different types of data sources, MySQL, Microsoft SQL Server, and ODBC plain text Character Separated Value ( CSV ) files. The approach taken is to allow users to build SQL queries in the web page, as needed to select, view, and manipulate the underlying data in the databases, in order to see the information they want in the manner desired.

Meta data from the databases, such as table names and field names in tables, is queried and used to populate the drop down select boxes that are then used to build SQL queries. The results from queries are displayed in scrollable HTML tables that have fixed headers, so that readability is improved. And database information can be paged through by selecting starting points and the number of records to be returned, as well as being able to scroll through the records returned from any given query.

Using the Code

On the site that accompanies the book, there's a .zip file that contains the widgets discussed in the book, including the database example covered in this article. While the database source code in the widgets.zip "TableFuncs" folder can be used on your "localhost", and the "genome" database can be accessed from localhost, the other data sources aren't available from localhost.

So the suggested way to use the the dbBrowse.html and queryDB.php pages is to upload them to the web site where the database of interest is located and access the database from there. The source code should first be be edited to reflect the database name in the dbBrowse.html drop down box of database options, and the connection string(s) in queryDB.php to reflect the URL, username, and password needed to access the particlular database(s).

Once a database has been selected from the drop down, the default query ( SELECT * FROM TableName ) is run on the first table in the database, and users can then build SQL queries to refine their searches by selecting a table name from the "Select Table" drop down, selecting fields from the "Select Field" drop down, and specifying "Where" and "Sort By" clauses if needed. If a WHERE clause is specified, an option from the "=" drop down is selected ( i.e. =, LIKE, <, >, <=, >= ), and a value is keyed into or copied into the text box between the "=" and the "AND/OR" drop downs. When the query has been built, it's run by clicking the "Run Query" button.

In the case of the MS SQL Server "Northwind" database, the default table of results also permits a mapping application to be run by double clicking on the "Address" fields. A map showing the address clicked on is displayed, and you should zoom in by clicking on the circled "+" icon at the top of the map, to see street details. The fields to used for mapping ( e.g. Country, City, Postal Code in the "Northwind" database, "Customers" table ) can also be specified by selecting options in the "Select Map Field" drop down before running a query, and the first field selected is the one that will activate mapping when that field is clicked on in a row.

The database selections available in dbBrowse are all at different Internet IP addresses, as shown in the ‘Select Database’ drop-down, in Figure 5.5.

Image 1

Figure 5.5 - Select Databases

The databases don’t contain any proprietary information, and all are freely available in the public domain. There are two MySQL databases, ‘world’ and ‘genome’, one Microsoft SQL Server database, ‘Northwind’, and the ‘SNOMED_CORE_SUBSET_201002.txt’ ASCII text file, accessed with an Open Database Connectivity ( ODBC ) text file SQL driver.

Other databases of these types can be substituted by changing the ‘Select database’ drop-down in dbBrowse and the queryDB connection strings.

The dbBrowse.html page uses the following JavaScript code to initialize AJAX. Feature detection is employed to initialize in a cross-browser manner, and AJAX is then used to call the default .csv ASCII text file in the block of code on the page after next.

C#
function createXMLHttpRequest() 
{
    if (typeof XMLHttpRequest != "undefined")	        //  Feature detect for
    {						        //  non-Microsoft browsers
         return new XMLHttpRequest();
    } 
    else if (typeof ActiveXObject != "undefined")         //  Feature detect for
    {						        //  Microsoft browsers
         return new ActiveXObject("Microsoft.XMLHTTP");
    }
    else 
    {
         throw new Error("XMLHttpRequest not supported");
    }
}

The code on the next page performs the actual AJAX call, loading the data into an array from the file ‘vitalSigns.csv’. The file is split first on carriage returns / linefeeds ( \r\n ) to get the lines / rows, and then each line / row is split on commas ( , ) to get the fields / columns.

All fields in this example are strings, except for the 2nd, 3rd, and 6th columns, which are converted to numeric types. But no conversion is performed on the first line of the file, as this is column headers / titles.

All fields could be left as strings if they are simply to be displayed, and numeric data only needs to be converted if it’s to be used in calculations. In this particular case the file structure is known, but if fields were to be converted without knowledge of the file structure, the code snippet to test each field would be something like:

if ((varName*1) == varName) Run code that treats varName as numeric;
else Run code that treats varName as text;

Note that the loadFile() function on the next page is very similar to the ‘loadVitals()’ function in Chapter 4. This AJAX function is used to load the .CSV file that’s displayed initially on ‘dbBrowse.html’, although it can be used to load any .CSV file.

C#
function loadFile(FileToRun, fileParams)
 {
     var requestFile = createXMLHttpRequest();
     requestFile.open("GET", FileToRun, true);
     requestFile.onreadystatechange = function()	    // Request state has changed
     {
         if (requestFile.readyState == 4) 		    // Request is complete 
         {
	 tempText = requestFile.responseText;
              tempText2 = tempText.split("\r\n");	           // Line separator = ‘\r\n’
	 if (tempText2.length > 1)
	 {
	     var tempVSarray;
	     for (var i=0; i<tempText2.length; i++)        // Loop through each line
	     {
	         tempVSarray = tempText2[i];
	         tempVSarray = tempVSarray.split(",");     // Field separator = ‘,’
	         for (var j=0; j< tempVSarray.length; j++)  // Loop through each
	         {					           // item in the line
		     if ((i>0) && (j != 1) && (j != 2) && (j != 5))
                               {
                                   tempVSarray[j] = tempVSarray[j]*1; // Convert some
                               }				               // columns to numeric
	         }		
	          vitalSignsArray[i] = tempVSarray; 	// Load line array
	     }						// into table array
 	 }
	 setTable("dataTable" ,vitalSignsArray);        // Populate table with
          }						       // id ‘dataTable’ from
     }						       // ‘vitalSignsArray’ array
     if ((fileParams == null) || (fileParams == ""))
     {requestFile.send();}
     else 
     {
	requestFile.setRequestHeader('Content-Type', 
                                                   'application/x-www-form-urlencoded');
	requestFile.setRequestHeader('Content-length',fileParams.length);
	requestFile.send(fileParams);
     }
 }

The ‘requestFile.setRequestHeader’ lines near the end of the function are configured to return information from any kind of file format, rather than just the XML format specified by the ‘X’ in the ‘AJAX’ name.

Cross-Browser Features

Browser detection is implemented in a cross-browser manner with the JavaScript code shown at the end of Chapter 3.

The data table is built on the fly with the setTable() function shown on the next page, using the data in the ‘vitalSignsArray’ array generated when the AJAX call is made. And near the end of the function the code branches for the browser that’s been detected.

The existing HTML <div> element with the id ‘contentLines’ is used to hold the new HTML <table> element created with ‘createElement’ and having the id ‘dataTable’.

In the code on the next two pages, because the underlying data in the array has headers as the first row, when ‘v’ has a value of zero headers are created with table <th> elements rather than data rows’ <td> elements. And a distinguishing appearance is used for the headers, set by the class ‘header’.

Table <th> cell elements are created with <a> anchors to provide built-in ‘onclick’ functionality. And the header elements provide table sorting with the arraySort() function, so that clicking headers sorts tables.

The ‘text-decoration’ is set to ‘none’ so that table header titles aren’t underlined, the ‘color’ parameter is set so that the table header title colors stay the same when the table header / anchor elements are clicked, and the cursor appearance is set to ‘hand’ to indicate an active, clickable element to users.

The ‘setColor(lineNum);’ function, near the top of the page two pages ahead, alternates data row background colors so that users can more easily follow data rows across the table.

Two loops, the outer one using the variable ‘v’ to control it, and the inner one using the variable ‘w’ for control, build the data rows of the <table>, after the structure has been created. Each ‘v’ value refers to a line / row of data, and each ‘w’ value refers to a field within a row.

At the end of the inner loop, the code branches for Internet Explorer and non-IE browsers, in order to avoid using the ‘setAttribute’ JavaScript command that’s problematic with IE in the context used here for setting fields’ style / background and border properties.

And at the end of the function, the ‘fxheaderInit()’ and ‘fxheader()’ functions implement fixed headers with scrolling data rows as users scroll through the table.

C#
function setTable(tblId,tempArray)
{
    var clTable = document.getElementById("contentLines");
    clTable.display = "block";
    var vsTable = document.createElement('TABLE');	//  Set up table structure
    vsTable.setAttribute("id",tblId);
    vsTable.setAttribute("border","1");
    vsTable.setAttribute("cellpadding","2");
    vsTable.setAttribute("cellspacing","0");
    vsTable.setAttribute("class","mt");
    vsTable.setAttribute("style","width:750px;background:#F5F5DC;
                            border:1px solid lightgray;color:#000080;
                            font- weight:bold;");                                                                                                                                                                   
    clTable.appendChild(vsTable);
    var vsTBody = document.createElement('TBODY');
    vsTable.appendChild(vsTBody);
    var tbody = document.getElementById(tblId).
                            getElementsByTagName('tbody')[0]; 
    lineNum = 0;
    for (v=0;v<tempArray.length;v++)			//  Loop through rows
    {

                     SEE   LOOP   ON   NEXT   PAGE

    }
    fxheaderInit(tblId,250,1,0);				//  Set up and implement
    fxheader();						//  fixed header scrolling
}

for (v=0;v<tempArray.length;v++)		//  Loop through rows
{
     if (v == 0) {Color = "#dddddd";} 
     else 						//  Switch alternate row
     {						//  background colors
	fontW = "normal";
	setColor(lineNum);
     }
     var row = document.createElement('TR');
     tbody.appendChild(row);
     lineArray = tempArray[v];
     for (w=0;w<lineArray.length;w++)		//  Loop through fields
     {
          var cellW = "cell"+w;
          if (v == 0) cellW = document.createElement("TH");   // Create headers
          else cellW = document.createElement("TD"); 	    //  Create data rows
          space = " ";
          if ((lineArray[w]*1) > 99) {space = "";}
          else if ((lineArray[w]*1) < 10) {space = "  ";}
          if (v > 0) cellW.innerHTML = space+lineArray[w];
          else cellW.innerHTML = "<a href='javascript:
                arraySort("+w+");' 					// Sort by
                style='color:#000080;cursor:hand;cursor: pointer;	// column
                text-decoration:none;'>"+space+lineArray[w]+"</a>";  // contents
          if (tblId == "dataTable");
         {
	 if (w == 0) {cellW.setAttribute("class","firstTD");}
	 else cellW.setAttribute("class","otherTD");
	 if (v == 0)					    //  Set up headers
	 {
	      cellW.setAttribute("class","header");
	      cellW.setAttribute("id","col"+w);
	      cellW.setAttribute("onmouseover","
                                               style.cursor='hand';style.cursor='pointer';");
	 }
           }
           if (browser == "Internet Explorer") 		    // Branch for IE
          {
                  cellW.style.cssText="background:"+Color+
                                        ";border:solid #d3d3d3 1px;font-size:13px";
          }
          else cellW.setAttribute("style","background:"+Color+";
                                                       border:solid #d3d3d3 1px;font-size:13px;");
          row.appendChild(cellW);
       }
    }

The table sorting code is shown below, and it performs in-memory array sorts of columns containing textual data in both alphabetical and numeric form. The table headers are clicked to sort on the contents of particular columns, alternately in ascending order and descending order each time headers are clicked - see www.web2ria.com/dbBrowse.html.

C#
function arraySort(sortCol)
{
   var header = vitalSignsArray.shift();		// Remove Headers
   if ((sortCol == 5) || (sortCol == 2)) 
   {
      setCol(sortCol,0); 				// setCol() handles text 
   }						// with "/" or "-"
   else
   {
      if (typeof vitalSignsArray[0][sortCol] == 'string')
     {z = vitalSignsArray.naturalSort(sortCol);}	// Sort alphabetical data
      else z = vitalSignsArray.deepsort(sortCol);	// Sort numeric data
   }
   results = "";
   if (direct == "Asc") direct = "Desc";		// Switch next sort direction
   else direct = "Asc";
   document.getElementById("contentLines").innerHTML = "";
   if ((sortCol == 5) || (sortCol == 2)) setCol(sortCol,1);
   vitalSignsArray.unshift(header);		// Replace headers
   setTable("dataTable");			// Build HTML <table>
}

Sorting in this example uses the array ‘vitalSignsArray’ as the data source, and the setTable() function also uses this array to populate the HTML table.

The ‘deepSort()’ function on the next page handles sorting numeric columns.

C#
Array.prototype.deepsort= function()
{
    var i, order=arguments, L=order.length, tem;
    return this.sort(function(a, b)
    {
        i= 0;
        while(i < L)
       {
            tem= order[i++];
            var ao= a[tem] || 0, bo= b[tem] || 0;
            if(ao== bo) continue;
            if (direct == "Asc") return ao> bo? 1: -1;
    else return bo> ao? 1: -1;
        }
        return 0;
    });
}

And the ‘naturalSort()’ function on the next page handles sorting string columns.

C#
Array.prototype.naturalSort= function()
{
    var a, b, a1, b1, rx=/(\d+)|(\D+)/g, rd=/\d+/;
    return this.sort(function(as, bs)
    {
        a= String(as).toLowerCase().match(rx);
        b= String(bs).toLowerCase().match(rx);
        while(a.length && b.length)
        {
            a1= a.shift();
            b1= b.shift();
            if(rd.test(a1) || rd.test(b1))
            {
                  if(!rd.test(a1)) return 1;
                  if(!rd.test(b1)) return -1;
                  if(a1!= b1)
                  {
                      if (direct == "Asc") return a1-b1;
                      else return b1-a1;
                  }
            }
            else if(a1!= b1)
            {
                return a1> b1? 1: -1;
            }
        }
        return a.length- b.length;
    });
}

A database is selected from one of the options in the ‘Select Database’ drop-down box, and when the selection changes, the function ‘dbSel()’ is called, in a similar way to the following pseudo-code:

<select id="dbSel" onChange="if (this.value != 0) dbSel(this.value);">
   <option value=0 selected>Select Database</option>
   <option value="One Database">One Database</option>
   <option value="Another Database">Another Database</option>
   <option value="Etc."> Etc.</option>
</select>

And the code for the function ‘dbSel’ is shown below.

C#
function dbSel(dbNum,startRec,recNum)
{
     var dbSel;
      if ((startRec == "") && (recNum == ""))
      {
  startRec = 0;
  recNum = 1000;
       }
       document.getElementById("dbSelFrame").style.visibility = "visible";
       dbSel = "queryDB.php?dbSel="+dbNum +
                                   "&startRec="+startRec+"&recNum="+recNum;
       document.getElementById("dbSelFrame").src = dbSel;
}

In this function, the selected database is passed to queryDB.php, which is called by changing the <iFrame> ‘src’ attribute to load queryDB.php into the <iFrame> in dbBrowse.html. Also passed are the URL querystring parameters for the SQL query, such as the selected database, the start record number, and how many records to return.

In dbBrowse.html are the functions ‘fieldSel()’ and tableSel(), both of which are called by JavaScript sections of code from within the <iFrame> source page queryDB.php, in the following manner:

<select
onChange=parent.tableSel('databaseName’,this.value);></select>

And the code for tableSel() in dbBrowse.html is as follows:

C#
function tableSel(dbSel,tableSel)
{
    if (tableSel != 'selectTable')
    {
         document.getElementById("dbSelFrame").src =
                   "queryDB.php?dbSel="+dbSel+"&tableSel="+tableSel;
    }
}

Here, the <iFrame> source file, queryDB.php, is reloaded with new parameters.

And the fieldSel() function in dbBrowse.html is called by clicking a button in queryDB.php, similar to that shown in the following pseudo code:

<input type='button' onclick=parent.fieldSel('databaseName'); value='Run Query'>

The ‘fieldSel()’ function in dbBrowse.html has the code on the next page.

C#
function fieldSel(dbSelLocal)
{
   if (document.getElementById("distUn").checked == true) 
   {
	var distinctSQL = 1;
   } 
   else var distinctSQL = 0;
   var myIFrame	        = document.getElementById('dbSelFrame');
   var mapStr 	        = myIFrame.contentWindow.mapStr;
   var contentStr 	        = myIFrame.contentWindow.columnSelect;
   var whereField 	        = myIFrame.contentWindow.whereStr;
   var orderStr 	        = myIFrame.contentWindow.orderStr;
   var startRec 	        = myIFrame.contentWindow.startRec;
   var recNum  	        = myIFrame.contentWindow.recNum;
   var table	        = "";
   table 		        = myIFrame.contentWindow.table;
   tableSelected	        =   
         myIFrame.contentWindow.document.getElementById('db'+dbSelLocal);
   table = tableSelected.options[tableSelected.selectedIndex].text;
   if ((contentStr != "undefined") && (contentStr != "")) 
   {
	if (contentStr.indexOf(".") > 0)
	{
	    var tempPos = contentStr.indexOf(".");
	    table = contentStr.substring(0,tempPos);
	}
   }
   var tempSrc = "queryDB.php?startRec="+startRec+
                                    "&recNum="+recNum+
                                    "&whereStr="+whereField+
                                    "&mapStr="+mapStr+
                                    "&orderStr="+orderStr+
                                    "&dbSel="+dbSelLocal+
                                    "&distinctSQL="+distinctSQL+
                                    "&sqlSelect="+contentStr+
                                    "&tableSel="+table;
   document.getElementById("dbSelFrame").src = tempSrc;
}

The ‘myIFrame’ variable is used to directly access the required variable values in the JavaScript section(s) of the <iFrame> queryDB.php file.

Of course queryDB.php could have been loaded directly from itself with the ‘location.href’ JavaScript command. But this methodology demonstrates parent-child communications in an <iFrame> environment.

queryDB.php

The <iFrame> source page, queryDB.php, is a single page that handles queries for all the databases selectable from the parent dbBrowse.html page. The code varies from one version of SQL to another as the versions used by MySQL, Microsoft SQL Server, and ODBC, use different syntax for the same purposes, and they format returned results differently.

The code is divided into sections, one for each different database. And for clarity of section operation, and due to the differences between the various database vendors’ versions of SQL, ‘common’ operations haven’t been separated into common functions. However, some truly common JavaScript functions that apply to all sections are used where appropriate, such as functions that create fixed headers with scrollable tables.

Each section connects with and logs on to the individual databases at different Internet URLs, the default query is then run, and results are returned that are displayed in a scrollable table with fixed headers. The results returned by the default query are displayed in a page with similar layout regardless of which database is being displayed.

The layout of the top part of the page ( shown below in Figure 5.6 ) allows for the selection of SQL queries for each database, by employing HTML <select> drop-down boxes. These are populated automatically with options that apply specifically to the database and table that has been selected, for example with field names for each specific database and table.

Image 2

Figure 5.6 - Layout of common controls in queryDB.php

The layout of the lower part of the page is database-dependent, with an HTML table with a column for each database table column, and headers titled the same as the database table column / field header names. The HTML headers are fixed and the rows of the table are scrollable.

Image 3

Figure 5.7 - A typical queryDB.php data table

Table column headers match the options in the drop-down <select> boxes that allow database table fields to be selected for SQL queries, with the addition in the ‘Select Field’ box of the ‘*’ option to select all fields with a single click. The ‘Select Table’ <select> box directly below the database selection drop-down on the previous page shows all the tables for a particular database.

When a new table is selected from the table drop-down, queryDB is automatically reloaded with the default query of the form ‘SELECT * FROM tableName’ run on the table, and information for the newly selected table’s fields are shown. But when individual fields are selected to form a query, the query only runs when the ‘Run Query’ button is clicked.

The <select> drop-down with the default option of ‘Select Field’ permits individual fields to be selected for the SQL query, one at a time, and the selected fields are displayed in the ‘Fields Selected’ box. All fields can be selected with a single click of the ‘*’ option. In the event that a selected field needs to be removed from the query, double clicking on the entry in the ‘Fields Selected’ box removes it.

Queries can be refined with the ‘Where Select Field’ and conditional drop-downs that show the ‘=’, ‘LIKE’,’<’, ‘>’, ‘<=’, and ‘>=’ options. And a value from a table field is entered into the adjacent text box, either by copying and pasting from the table, or by typing a value in.

After a ‘Where’ clause is specified, it’s automatically entered into the ‘WHERE Clauses Selected’ box whenever focus shifts to another page element. Examples of this are clicking the ‘Run Query’ button, or selecting a new option in the ‘Select Field’ drop-down.

Additional ‘Where’ clauses are selected with the ‘AND’ or ‘OR’ options from the ‘AND/OR’ drop-down and by entering a new ‘Where’ clause. As with the ‘Fields Selected’ box, unwanted ‘WHERE Clauses Selected’ items that have been selected, can be removed by double-clicking them.

The Sort By ‘Select Field’ drop-down allows a field to be selected that’s used to sort the query results, and the Asc or Desc order is selected by clicking the appropriate radio button.

Image 4

Figure 5.8 - A completed query, before execution

When the query has been satisfactorily built, it’s executed by clicking the ‘Run Query’ button, which reloads the queryDB.php page with the new query parameters to be run. The SQL query results for the selected columns are then displayed in the table. If at least one field in the ‘Select Field’ drop-down hasn’t been selected, an alert box warns of an error.

If the database selected is ‘MS SQL Server Northwind’, a Geocoding drop-down box is also displayed. This permits selection of fields for mapping when the ‘Customers’ table is selected.

By default, Geocoding in the ‘Customers’ table is run by double-clicking on ‘Address’ fields, and the ‘Address’, ‘City’, ‘Region’, ‘PostalCode’, and ‘Country’ field values for a clicked record are used to show a map in a separate window. See Figures 5.9 and 5.10 below.

Image 5

Image 6

Figure 5.9 & 5.10 - Geocoding in MS SQL Server Northwind database

The connection strings and SQL queries for the 3 different database types look like the PHP pseudo-code described in the following pages. 

MS SQL Server

$MSSQLconnect =  // Replace userName and password with actual ones
                  mssql_connect("205.209.97.138","userName","password");
$dbName             = "Northwind";
$objDB                = mssql_select_db($dbName);

The table names are obtained from the database with the SQL query:

$strSQL = "SELECT name FROM sys.tables";
$objQuery = mssql_query($strSQL);

And the ‘Table Names’ drop-down is populated with the loop:

while($objResult = mssql_fetch_array($objQuery))
{
  $tablename=$objResult[0];
  $tempStr = "<option value='".$tablename;
  if ($tablename == $table)            // $table is the selected table
  {
    $tempStr = $tempStr."' selected";
  }
  else $tempStr = $tempStr."'";
  print $tempStr.">$tablename</option>";
  $tableArray[$count] = $tablename;
  $count++;
}

The field headers drop-down is populated from the ‘$fieldArray’ array produced by the SQL query:

$query = "SELECT COLUMN_NAME FROM
        INFORMATION_SCHEMA.COLUMNS WHERE
            TABLE_NAME='tableName'";
// Replace tableName with real name
$result = mssql_query($query,$MSSQLconnect);
$rowCount = 0;
 while ($get_info = mssql_fetch_row($result))
{
    foreach ($get_info as $field)  $fieldArray[$rowCount] = "$field";
    $rowCount++;
 }

The actual database query is built with the following PHP pseudo code:

    if ($sqlSelect != "")
    {
        if ($whereStr == "undefined") $whereStr = "";
        $tempSel = "SELECT ";
        if ($whereStr != "")
        {
  $whereStr = str_replace("\%27","'",$whereStr);
        }
        $query = "$tempSel TOP $recNum $sqlSelect";
        $query = "$query FROM $table $whereStr";
        if (($orderStr != "undefined") && ($orderStr != "null") &&
($orderStr != "")) $query = $query.$orderStr;
    }
    else $query = "SELECT TOP $recNum * from $table";
    $query = str_replace('%27',"'",$query);
    $result = mssql_query($query,$MSSQLconnect);
    if ($result != "") $numrows = mssql_num_rows($result);
    else $numrows = 0;

The following variables are all passed to queryDB.php from dbBrowse.html:

  • $sqlSelect - the ‘SELECT’ part of the SQL query
  • $recNum - the number of records to be shown
  • $whereStr - the ‘WHERE’ part of the SQL query
  • $orderStr - the ‘ORDER BY’ part of the SQL query
  • $table - the table name used in the SQL query

And the final query might look like this:

SQL
SELECT TOP 5 CompanyName,City FROM Customers 
    WHERE Country = ‘USA’ 
        AND ContactTitle = ‘Owner’;

The selections on the web page for this query would be as shown below:

Image 7

Figure 5.11 - MS SQL Server query selection screenshot

With the result:

Image 8

Figure 5.12 - MS SQL Server results screenshot

Note that there are some restrictions in SQL Server on sorting columns with SQL, and you may want to implement the sorting procedures initiated by clicking column headers, as an alternative.

MySQL

    $MySQLconnect = mysql_connect("74.50.94.228","userName","password");

// Replace the URL, userName, and password with actual ones, and note that the 
// URL can be the long-form descriptive URL, e.g. ‘genome-mysql.cse.ucsc.edu’.

    $dbName = "world";

// Replace database name with actual one

    mysql_select_db($dbName,$MySQLconnect);

Table names are found from the PHP function 
$tablelist = mysql_list_tables($dbName);’

In the pseudo code below, the table name being queried is in the variable $table passed from dbBrowse.html to queryDB.php. The $result variable that contains the field names for a particular table is found with this query:

$query = "SELECT COLUMN_NAME FROM 
    INFORMATION_SCHEMA.COLUMNS WHERE 
        TABLE_SCHEMA='".$dbName."' AND    
            TABLE_NAME='".$table."'"; 
$result = mysql_query($query,$MySQLconnect);

A loop populates the ‘Select Field’ drop-down and builds $fieldArray as shown on the next page.

$rowCount = 0;
while ($get_info = mysql_fetch_row($result))
{
            foreach ($get_info as $field)
            print "<option value='$table.$field'>$field</option>\n";
   $fieldArray[$rowCount] = "$field";
   $rowCount++;
 }

The database query to fetch data from the selected table is built with the PHP code below:

    if ($whereStr == "undefined") $whereStr = "";
    $tempSel = "SELECT ";
    if ($whereStr != "")
    {
       $whereStr = str_replace("%27","'",$whereStr);
    }
    $query = "$tempSel $sqlSelect FROM $table $whereStr";
    if (($orderStr != "undefined") && ($orderStr != "")) $query =
                      $query.$orderStr;
$query = $query." LIMIT $startRec, $recNum";
    $result = mysql_query($query,$MySQLconnect);

And the following variables are all passed to queryDB.php from dbBrowse.html:

  • $startRec - the first record to be shown
  • $recNum - the number of records to be shown
  • $sqlSelect - the ‘SELECT’ part of the SQL query
  • $whereStr - the ‘WHERE’ part of the SQL query
  • $orderStr - the ‘ORDER BY’ part of the SQL query

The final query might look like this for the MySQL ‘world’ database:

SELECT Name, Continent, Population, LifeExpectancy FROM Country  
 	WHERE Continent = ‘Europe’ AND Population >= 1000000 
 	    ORDER BY Population DESC LIMIT 0, 10

And the selections to achieve this query would be as shown in the screen shot on the next page.

Image 9

Figure 5.13 - MySQL Query selection screenshot

With the result shown below:

Image 10

Figure 5.14 - MySQL query results screenshot

ODBC Text

$connect = odbc_connect("ODBC_DSN","userName","password");

Replace the ‘userName’ and ‘password’ above with the real ones, and the ODBC DSN

( Data Service Name ) Text driver is set for the server file to be accessed, ‘SNOMEDCT_CORE_SUBSET_201002.txt’ here. The ‘schema.ini’ initialization file is placed in the same location as the ‘SNOMED … ’ .txt data file. The .ini file looks as shown on the next page for the vertical bar ( | ) delimited file, ‘SNOMEDCT_CORE_SUBSET_201002.txt’.

[schema.ini]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=25
CharacterSet=OEM
[snomedct_core_subset_201002.txt]
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=25
CharacterSet=OEM
Col1=SNOMED_CID Integer
Col2=SNOMED_FSN Char Width 255
Col3=SNOMED_CONCEPT_STATUS Char Width 255
Col4=UMLS_CUI Char Width 255
Col5=OCCURRENCE Integer
Col6=USAGE Float
Col7=FIRST_IN_SUBSET Integer
Col8=IS_RETIRED_FROM_SUBSET Char Width 255
Col9=LAST_IN_SUBSET Char Width 255
Col10=REPLACED_BY_SNOMED_CID Char Width 255

The field headers drop-down is populated from the first record of the HTML table that results from the default query pseudo code as follows:

$Query = "SELECT TOP 1000 * FROM 
    SNOMEDCT_CORE_SUBSET_201002.txt";
$queryexe = odbc_exec($connect, $Query); 
odbc_result_all($queryexe,'id="dataTable" class="mt"');

The data from the ‘dataTable’ HTML <table> populates the array variable ‘tableTH’ like this:

var tempTable = document.getElementById('dataTable');
var tableTH     = tempTable.getElementsByTagName('th');

And the Select Field drop-down, that contains the table headers, and has the id ‘dbSNOMEDFields’, is populated with the array variable ‘tableTH’, in a loop as follows:

for (h=0; h < tableTH.length; h++)
{
	var optn = document.createElement('OPTION');
	optn.text = tableTH[h].innerHTML;
	optn.value = tableTH[h].innerHTML;
	document.getElementById('dbSNOMEDFields').options.add(optn);
 }

The actual database query is built from the selections made in the HTML drop-downs in queryDB.php, as follows:

$Query1 = "SELECT TOP $recNum $sqlSelect FROM
    SNOMEDCT_CORE_SUBSET_201002.txt";
if (($whereStr != "") && ($whereStr != undefined))
{
    $whereStr = str_replace('%27',"'",$whereStr);
	$Query1 = $Query1." ".$whereStr;
}
if (($orderStr != "") && ($orderStr != undefined))
{
    $Query1 = $Query1." ".$orderStr;
}

$recNum ( number of records ), $sqlSelect ( SQL SELECT statement ), $orderStr ( SQL ORDER BY string ), and $whereStr ( SQL WHERE clause ) are passed to queryDB.php from dbBrowse.html.

The parameters are passed up from queryDB.php to dbBrowse.html, before being passed back to queryDB.php in the URL as the <iFrame> reloads.

The final query might look like this:

SELECT TOP 5 SNOMED_CID,SNOMED_FSN FROM  
    SNOMEDCT_CORE_SUBSET_201002.txt 
         WHERE SNOMED_FSN  LIKE '%Asthma%' 
             ORDER BY SNOMED_CID DESC

And the selections for this query in the application would be as follows:

Image 11

Figure 5.15 - ODBC text file query selection screenshot

With the result:

Image 12

Figure 5.16 - ODBC text file results screen

The ‘SNOMEDCT_CORE_SUBSET_201002.txt’ source file isn’t actually a Comma Separated Value .csv file, as some fields contain commas. So the separator is the vertical bar character, ‘|’, ASCII HTML number ‘&#124;’.

The ODBC DSN must be configured for this separator. The first 3 records in the data file are shown below ( folded for readability ), and the first record is column headers ( the line break and blank line after this line, are also inserted here for readability ). The 2nd record has a comma in the 2nd field, between the words ‘disorder’ and ‘systemic’.

SNOMED_CID|SNOMED_FSN|SNOMED_CONCEPT_STATUS|UMLS_CUI|
OCCURRENCE|USAGE|FIRST_IN_SUBSET|IS_RETIRED_FROM_SUBSET|LAST_IN_SUBSET|REPLACED_BY_SNOMED_CID

38341003|Hypertensive
disorder, systemic arterial (disorder)|Current|C0020538|7|3.0865|200907|False|| 55822004|Hyperlipidemia (disorder)|Current|C0020473|7|1.9093|200907|False||

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer MediRec
United States United States
I've been developing web sites since 2000, especially Single Page Application sites ( SPA ), and I use AJAX extensively. In 2013 I wrote the book titled "A Practical Guide to Developing Web 2.0 Rich Internet Applications" that's published by Amazon.

And in the course of my career I've worked for organizations large and small, in the private and government sectors, such as the FBI, the Bureau of Land Management, Intermountain Health Care, Wells Fargo bank, and Microsoft Research. Before focusing on the web, I programmed PC-based voice systems, and before that I had a successful career in the oilfield services industry as a subsurface well surveyor working all over the world.

You can see additional information on my personal web site at www.philpearl.us.

Comments and Discussions

 
-- There are no messages in this forum --