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.
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.
function createXMLHttpRequest()
{
if (typeof XMLHttpRequest != "undefined")
{
return new XMLHttpRequest();
}
else if (typeof ActiveXObject != "undefined")
{
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.
function loadFile(FileToRun, fileParams)
{
var requestFile = createXMLHttpRequest();
requestFile.open("GET", FileToRun, true);
requestFile.onreadystatechange = function()
{
if (requestFile.readyState == 4)
{
tempText = requestFile.responseText;
tempText2 = tempText.split("\r\n");
if (tempText2.length > 1)
{
var tempVSarray;
for (var i=0; i<tempText2.length; i++)
{
tempVSarray = tempText2[i];
tempVSarray = tempVSarray.split(",");
for (var j=0; j< tempVSarray.length; j++)
{
if ((i>0) && (j != 1) && (j != 2) && (j != 5))
{
tempVSarray[j] = tempVSarray[j]*1;
}
}
vitalSignsArray[i] = tempVSarray;
}
}
setTable("dataTable" ,vitalSignsArray);
}
}
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.
function setTable(tblId,tempArray)
{
var clTable = document.getElementById("contentLines");
clTable.display = "block";
var vsTable = document.createElement('TABLE');
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++)
{
SEE LOOP ON NEXT PAGE
}
fxheaderInit(tblId,250,1,0);
fxheader();
}
for (v=0;v<tempArray.length;v++)
{
if (v == 0) {Color = "#dddddd";}
else
{
fontW = "normal";
setColor(lineNum);
}
var row = document.createElement('TR');
tbody.appendChild(row);
lineArray = tempArray[v];
for (w=0;w<lineArray.length;w++)
{
var cellW = "cell"+w;
if (v == 0) cellW = document.createElement("TH");
else cellW = document.createElement("TD");
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>";
if (tblId == "dataTable");
{
if (w == 0) {cellW.setAttribute("class","firstTD");}
else cellW.setAttribute("class","otherTD");
if (v == 0)
{
cellW.setAttribute("class","header");
cellW.setAttribute("id","col"+w);
cellW.setAttribute("onmouseover","
style.cursor='hand';style.cursor='pointer';");
}
}
if (browser == "Internet Explorer")
{
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.
function arraySort(sortCol)
{
var header = vitalSignsArray.shift();
if ((sortCol == 5) || (sortCol == 2))
{
setCol(sortCol,0);
}
else
{
if (typeof vitalSignsArray[0][sortCol] == 'string')
{z = vitalSignsArray.naturalSort(sortCol);}
else z = vitalSignsArray.deepsort(sortCol);
}
results = "";
if (direct == "Asc") direct = "Desc";
else direct = "Asc";
document.getElementById("contentLines").innerHTML = "";
if ((sortCol == 5) || (sortCol == 2)) setCol(sortCol,1);
vitalSignsArray.unshift(header);
setTable("dataTable");
}
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.
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.
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.
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:
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.
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.
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.
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.
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.
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 =
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)
{
$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'";
$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:
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:
Figure 5.11 - MS SQL Server query selection screenshot
With the result:
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");
$dbName = "world";
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.
Figure 5.13 - MySQL Query selection screenshot
With the result shown below:
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:
Figure 5.15 - ODBC text file query selection screenshot
With the result:
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 ‘|’.
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||
