how to pass column filters as URL query strings

genealogy_guy

I have a genealogy website using DataTables - http://www.Irish-Place-Names.com. It is a search facility to a database with 64000 townlands (places) in Ireland. Everything is working fine, but I would like to improve it.

How can I use URL query strings to pass column filters? I would like to be able to pass two variables for two column filters: place_name and county. So an example URL would look like .../search/?place_name=dun&county=dublin

Has anybody have any advice on how to achieve this? An extra niggle would be updating the column filters at the bottom of the table with the current URL query strings.

Regards

fbas

in Javascript? or PHP?

javascript to get querystring values:
http://stackoverflow.com/questions/901115/get-query-string-values-in-javascript

in PHP check the $_GET array, i.e. [code]if (isset($_GET['place_name']) ) $place_name = $_GET['place_name']; [/code], use the value in some javascript you generate to implement that value in a javascript variable.

once you have the value, you can initialize your table with those values in the column filters, and put those values into your column filter textboxes

[code]
//putting the values into your filters using PHP


[/code]


[code]
//putting the values into your filters using javascript
$('#place_name_filter').val(place_name);
$('#county_filter').val(county);
[/code]

using the filter input (or use the variable directly) in server side call fnServerData, in your intialization
[code]
"fnServerData": function ( sSource, aoData, fnCallback ) {
// look for the correct sSearch column for place_name and county
if ($('#place_name_filter').val() != "") {
o = getODataByName(aoData, 'sSearch_0'); // this is defined below
if (o) o.value=$('#place_name_filter').val(); // replace sSearch for this column with contents of the filter
}
if ($('#county_filter').val() != "") {
o = getODataByName(aoData, 'sSearch_0'); // this is defined below
if (o) o.value= county; // replace sSearch for this column with querystring variable
}


$.ajax( {
"dataType": 'json',
"type": "POST",
"url": sSource,
"data": aoData,
"success": fnCallback
} );
},

// ...


function getODataByName(aoData, name) {
for (i in aoData) {
if (aoData[i].name == name) return aoData[i]
}

return;
}
[/code]

for client side processing, I'm not sure if you could set the filter before the initial draw (maybe you can set something in the oSettings object), but you can surely call fnFilter for these columns in the fnInitComplete callback
[code]
$(document).ready( function() {
var oTable = $('#example').dataTable( {
"fnInitComplete": function() {
// if "this" doesn't work, try setting this code in a timer to be sure oTable's init has completed and oTable variable is set
this.fnFilter(place_name, 2); // change the column numbers as necessary
this.fnFilter(place_name, 3);
}
} );
} )
[/code]

there are probably other ways to do this, possibly better ways, but should work (some of this code I use in one of my projects)

genealogy_guy

Thank You, fbas.

My main reason for asking how to convert the filter strings into URL query strings was so that I could monitor the usage of the database and to see what filters were being entered.

At the moment, StatCounter and GoogleAnalytics only tell me that users spent a long time on one page. Because the page URL does not change while the filters change, I don't know what my users are searching for.

Step 1 would be passing filter values to page URL with as URL query strings.
Step 2 would then be passing URL query strings as filter values (and thus refreshing table)

Any suggestions as how I could achieve Step 1?

Regards
Seoirse

fbas

If I'm understanding correctly, I don't know if you can achieve #1 because your user is most likely accessing the base page (i.e. index.php) and all the calls to database filters are using ajax to gather data, but still display them on that index.php page.

if your google analytics or other stat counting code is in index.php, and they don't move to another page, the stat counter will only show index.php.

I doubt google lets you modify their code at all (you probably include it remotely, right?), in which case you can't add querystring parameters to the analytics/stat counting code. You might look into their API, because they might have a solution for this.

If you want a discontinuous experience for your users, you COULD have filter changes force a page refresh rather than an AJAX call to change the data.

qdata

genealogy_guy:

You have done a wonderful job and your web site is beautiful! I am hoping to create a website similar to what you have created.

I have a property database and my programmer is currently creatiing a new “Publish Lists” dropdown that will allow me to search for a user by name and bring back a list of properties for that user. When he is finished incorporating the FasterCSV library, clicking Publish Lists will instantly generate a downloadable CSV file of those properties. He thinks that I will then use that to populate my datatables.

He can make it generate those lists in many different formats: JSON, CSV, XML, etc. The other thing he could do is write an API function where I would hit the site with a URL (something like http://admin.mysite.com/api/get_properties?user_name=John+Doe&email=jd@gmail.com&format=xml.

That would instantly give me back an XML feed of that property data. Change “xml” to “json” and I would get the JSON version.

Make sense? Is he on the right track here?

Any assistance or advice would be GREATLY appreciated!

Thanks in advance.

studioleland

@fbas

Thanks for the great start on this thread