Server side filtering AJAX-style
I needed to filter a large number of rows (stored in SQL) and wrote the following code which uses an AJAX-style http.request to get the necessary rows from the Server and then filter the grid based on this list.
I am using static data in this example (to keep it simple) but it can easily be adapted to get 'live' data off a backend database.
Firstly, you need to create a grid whose RowIDs correspond to the data IDs in your database.
In this example, I presume the data IDs are 111,222,333,444,555 but you will obviously substitute this whole data construction section with your own code to represent your 'live' data.
Next, create the grid and a button to test the filtering
Now for the interesting bit. Using a server side scripting language such as ASP or PHP you can dynamically get a list of data IDs from your backend (using http.request) and filter the grid accordingly. In this example, I presume the user selected some drop down which resulted in rows 222 and 444 being displayed, i.e. 111,333 and 555 are filtered out as a result of this user intereaction. I am simulating this by using a button just to keep the example simple.
The getRecords.php file referred to above can contain a SQL SELECT statement to return the Data Ids based upon some selection by the user. For simplicity (and to keep the example generic), however, I have simply returned a static list of DataIDs in this example:
If you click on the "Filter" button, you should only see two rows.
I found it to be very fast, especially on large data sets. I hope you find it useful.
I am using static data in this example (to keep it simple) but it can easily be adapted to get 'live' data off a backend database.
Firstly, you need to create a grid whose RowIDs correspond to the data IDs in your database.
In this example, I presume the data IDs are 111,222,333,444,555 but you will obviously substitute this whole data construction section with your own code to represent your 'live' data.
<html>
<head>
<script src="./runtime/lib/aw.js"></script>
<link href="./runtime/styles/classic/aw.css" rel="stylesheet"></link>
</head>
<body>
<script>
var myData = [];
myData["111"] = ["MSFT","Microsoft Corporation", "314,571.156", "32,187.000"];
myData["222"] = ["ORCL", "Oracle Corporation", "62,615.266", "9,519.000"];
myData["333"] = ["SAP", "SAP AG (ADR)", "40,986.328", "8,296.420"];
myData["444"] = ["CA", "Computer Associates Inter", "15,606.335", "3,164.000"];
myData["555"] = ["ERTS", "Electronic Arts Inc.", "14,490.895", "2,503.727"];
var myColumns = ["Ticker", "Company Name", "Market Cap.", "$ Sales"];Next, create the grid and a button to test the filtering
var obj = new AW.UI.Grid;
obj.setCellText(myData);
obj.setHeaderText(myColumns);
obj.setRowIndices([111, 222, 333, 444, 555]);
obj.setRowCount(5);
obj.setColumnCount(4);
obj.setSelectorVisible(false);
document.write(obj);
document.write('<br>');
var Testbutton = new AW.UI.Button;
Testbutton.setControlText("Filter Rows");
document.write(Testbutton);Now for the interesting bit. Using a server side scripting language such as ASP or PHP you can dynamically get a list of data IDs from your backend (using http.request) and filter the grid accordingly. In this example, I presume the user selected some drop down which resulted in rows 222 and 444 being displayed, i.e. 111,333 and 555 are filtered out as a result of this user intereaction. I am simulating this by using a button just to keep the example simple.
Testbutton.onClick = function(){
sendRequest(''); // Put your SELECT statement in here
};
function createRequestObject() {
var ro;
var browser = navigator.appName;
if(browser == "Microsoft Internet Explorer"){
ro = new ActiveXObject("Microsoft.XMLHTTP");
}else{
ro = new XMLHttpRequest();
}
return ro;
};
var http = createRequestObject();
function sendRequest(queryString) {
http.open('get', 'getRecords.php?query='+queryString); // Call a Server side script to return the rows you want
http.onreadystatechange = handleResponse;
http.send(null);
};
function handleResponse() {
if (http.readyState == 4){ // Finished loading the response
var response = http.responseText; // Get the response from the Server
var indexList = response.split(','); // Create an array from the comma-separated list
obj.setRowIndices(indexList); // Only display these rows
obj.setRowCount(indexList.length); // Set the number of rows
obj.setSelectedRows([indexList[0]]); // Select the top row
}
};
</script>
</body>
</html>The getRecords.php file referred to above can contain a SQL SELECT statement to return the Data Ids based upon some selection by the user. For simplicity (and to keep the example generic), however, I have simply returned a static list of DataIDs in this example:
<?php
/***********************
getRecords.php
This can (and should) be modified to return a comma separated list of data IDs based on some SELECT statement which can be passed to this page via the "query" parameter in the URL
************************/
echo "222,444";
?>If you click on the "Filter" button, you should only see two rows.
I found it to be very fast, especially on large data sets. I hope you find it useful.
Rick Jordan
October 14,