Return ALL Distinct Column Values - Server Side

Trevor

My project is located at:
http://www.blackmagicms.com/regex/Vendor/sandbox/datatables/index4.html

I'm looking to return all Distinct values from the Vendor column in the database (not just those currently displayed) given the filters currently set by the user. I then want to display that information to the user in the header of the table. A little guidance or even pointing me in the right direction would be greatly appreciated. I saw the fnGetColumnData function, but from what I could tell, it doesn't work with Server Side processing.

My initial attempt was to add a parameter to the JSON output via something like this... however I wasn't sure how to get at that JSON value via existing Datatables methods:

[code]
$sQuery = "
SELECT SQL_CALC_FOUND_ROWS `".str_replace(" , ", " ", implode("`, `", $aColumns))."`
FROM $sTable
$sWhere
$sOrder
";

$vResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
foreach($vResult as $value){
$vList .= $value;
}
........
$output = array(
"sEcho" => intval($_GET['sEcho']),
"iTotalRecords" => $iTotal,
"iTotalDisplayRecords" => $iFilteredTotal,
"iVendorList" => $vList,
"aaData" => array()
);
........
echo json_encode( $output );

[/code]

Thank you in advance!

Trevor

Really looking for some help on this issue. I'm completely stumped.

allan

Looks okay to me - if you have a look in Firebug you should see your vendor list coming back. You then have a number of options:

1. Use fnServerData to make your own Ajax call to the server, and then in the success function, do what you will with the vendor list.

2. Listen for the xhr event: http://datatables.net/docs/DataTables/1.9.3/#xhr

3. Use fnDrawCallback to access the JSON data (its in oSettings.jqXHR).

Allan

Trevor

Thank you, Allan! I'll give this a go sometime in the next day or so. I also seem to be having issues with this project working in IE 8 and lower. Not sure what's going on there. I have another thread about that issue here: http://www.datatables.net/forums/discussion/11484/data-not-showing-in-ie

Trevor

So, I've finally come back to this. I've been researching the 3 methods you have provided above and I'm not sure I understand how to use them accordingly. It seems that since I already have my table setup and pulling in data, it would be easiest to use fnDrawCallback.

My current JSON return looks like this:

[code]
{
"sEcho": 1,
"iTotalRecords": 214682,
"iTotalDisplayRecords": 54,
"iVendors": "ATT, BLUEBIRD NETWORK, CENTURY LINK, CHARTER COMMUNICATIONS, CINCINNATI BELL, CONSOLIDATED COMMUNICATIONS, COX, EARTHLINK, GEORGIA PUBLIC WEB, GLOBAL CROSSING, ION, IOWA NETWORK SERVICES, KDL, NEBRASKA CENTRAL TELEPHONE COMPANY, SHOME, SPRINT, Texas Lone Star Network, USCARRIER, VERIZON, AFS, AGL, CENTURYTEL (LIGHTCORE), CITYNET, COSTREET, EASY-TEL COMM, FIBERLIGHT, FP&L, IRIS, MISSOURI NETWORK ALLIANCE, MCLEOD, QWEST, SOUTHERN LIGHT, SUDDENLINK, TELEPAK, WINDSTREAM, WISCONSIN INDEPENENT NETWORK, XO, LEVEL 3, COVAD, ZAYO, TIME WARNER TELECOM, SOUTH DAKOTA NETWORK, LLC, ELECTRIC LIGHTWAVE (ELI), PAETEC, NUVOX, DUKENET, ENVENTIS, ABOVENET, BRIGHT HOUSE, SWITCHES, US METRO, ALPHEUS, PALMETTONET",
"aaData": [
... data...
[/code]

I want to access the iVendors list via fnDrawCallBack and pass that variable to a div to display to the user upon every refresh of the datatable.

Trevor

Still need a little assistance on this. I haven't found a good example that "explains" how fnDrawCallback works and which parameters are required to get at a custom return value in the json return (iVendors in this case).

Trevor

I'm not sure if this could be more eloquent, but here is my solution for those who have had trouble finding a good working example of how to do this:

[code]
"fnDrawCallback": function(oSettings) {
var jsonResp = $.parseJSON(oSettings.jqXHR.responseText);
$("#vendor_results").html(jsonResp.iVendors);
},[/code]

For the code above, you would just want to replace the iVendors variable with the variable you want to access/manipulate. In this case, I'm updating a div (#vendor_results) to include the value for iVendors returned in my JSON response.

Allan, thank you for making me figure it out, I learned more that way.

Regards,


Trevor

allan

Hi Trevor,

Good to hear you got it sorted out. The method you use is currently the best way I think. jQuery doesn't hold on to its parsed JSON object, so you need to reparse it, as you have done.

Allan

Osiris

Sorry I'm mingling in this after the facts ...
If you want all distinct values from all columns, this is the way to do it (for oracle or mssql) :

mssql (supposing that your $columns is a php array of the column names) :
[code]$s = "with s as ( $query ) select * from ( select ";
for ($i=0;$i<$nc;$i++) { // nc=number of columns
$s.= " case when row_number() over (partition by ".$columns[$i]." order by ".$columns[$i].") = 1 then ".$columns[$i]." else null end as ".$columns[$i].",";
}
$s.= " from s ) t where ";
for ($i=0;$i<$nc;$i++) {
$s.= " ".$columns[$i]." is not null or";
}
if (substr($s, -2)=='or') {
$s=substr($s, 0, -2);
}[/code]

For oracle, lose the with-clause and just use [code]from ($query)[/code]

This should be ultra fast even for very large datasets (I'm doing it on a table of 100.000.000 rows on a mssql server and it takes max 3 seconds).

Now you have a table of all distinct values per column and all other values are nulls.
Cycle through that in php to build your filter (or whatever).
After some checks (on empty returns) I do this in php to strip out the nulls from the md-array :
[code]$cfw_rno=count($cfw_ray);
$cfw_cno=count($cfw_ray[0]);
$cfw=array();
for ($i=0;$i<$cfw_rno;$i++) {
for ($j=0;$j<$cfw_cno;$j++) {
if ($cfw_ray[$i][$j]!=null) {
$cfw[$j][]=utf8_encode($cfw_ray[$i][$j]);
}
}
}[/code]

maybe this helps someone.

allan

I'd suggest using `DISTINCT` rather than removing duplicates in PHP. It will be a huge amount faster: http://msdn.microsoft.com/en-us/library/ms187831(v=sql.105).aspx

Edit- I misunderstood slightly - you are building an SQL query with `partition` - that looks nice :-). Ignore my ignorant reply!

Allan

Trevor

Actually, here is the select code I used to generate the distinct values:

[code]
$sQuery = "
SELECT DISTINCT VENDOR
FROM $sTable
$sWhere
";
$rVendors = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
//$aVendors = mysql_fetch_array($rVendors);
while($aVendors = mysql_fetch_array($rVendors)){
$vList[] = $aVendors['0'];
}

for ( $i=0 ; $i