EDITOR - help with Select list reloading table

EDITOR - help with Select list reloading table

melphmelph Posts: 37Questions: 1Answers: 0
edited March 2013 in Editor
I need to be able to limit data entering DataTable by using a select list - this list will contain year values, so a user chooses a year and only corresponding data enters. Jquery is not my strong point, so any help would be appreciated.

I thought I could do it using "if" Jquery statements to detect year selected then route to DTEditor.mysql.class.php (the file that has the db query code) with the query amended to select for a specific year e.g. user selects 2006, this routes to "2006DTEditor.mysql.class.php"

This would work but I would have to create the DTEditor file for each year, including upcoming years. I need to make sure what ever is selected can be passed to DTEditor which looks for a value and then constructs the mysql query. I have tried using sessions but this just crashed Editor \ DataTables.

Could someone help with a suggestion on where I should look.

Many thanks

Mark

Replies

  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin
    I'm not sure I fully understand I'm afraid. It sounds like you want a SQL WHERE condition, and doesn't relate to jQuery / Javascript at all? Please can you link to a test case so I can understand what is happening.

    Allan
  • melphmelph Posts: 37Questions: 1Answers: 0
    Hi Allan
    Apologies for what I have now reread as a confusing post - I will explain in more detail.

    Users of my site log their bird sightings (the info goes into a mysql Db). The site has been going since 2006 and as such, some members have amassed quite a few sightings. What I want is for a user to be able to select to load a specific year worth of sightings into DataTables (e.g. just show their sigthings for 2007). I know I can load all their sightings into datatables and just get them to sort using the filter function of the table, but I was hoping just to limit to what the user wants to see for a specific year.

    So far, I have a sightings page which has a DataTable (editor version) which loads in all sightings for the logged in user (I use sessions so that only the logged in user sightings are viewed). That's fine, works great.

    On this page I have put a select box which using php populates the select with the current year back down to 2006 (the start year of the site). What I want to do is for the user to then select a year, then hit submit and then all the sightings for that year appear in the table. Then, if the user wants to look at a different year, they once again choose a year from the select box and then hit submit and the new selected year's sightings appear in the table etc etc.

    HOWEVER, what I cannot work out is that once the select year has been made by the user, is where to direct the page once the SUBMIT button is hit? This is where I thought I would have to use sessions, so that the year that has been selected by the user and submit pressed, the year is then carried across to the next page (browsers.php?) and then finds its way to DTEditor page where I write a WHERE statement that looks for the year contained in the session cookie. The last bit I can work out (e.g. writing the where statement), it is just getting that year selection to the DTEditor page where the mysql statement needs to be written.

    My test page is quite simple and only has the HTML select box and the dataTable - the select box doesnt actually do anything at the moment.


    I hope this helps and apologies it is long winded, but I thought it worth detailing it compared to my first post!

    Best wishes

    Mark
  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin
    edited March 2013
    Thanks for explanation.

    > I know I can load all their sightings into datatables and just get them to sort using the filter function of the table, but I was hoping just to limit to what the user wants to see for a specific year.

    I think from this you have two basic options:

    1. This one you mention - use filtering in DataTables. You could have a column (possibly hidden?) with the year in it, and simply use a few buttons which will trigger different filter (i.e. fnFilter using a column filter).

    2. Assuming you are Ajax loading the data (most Editor tables do), you could use fnReloadAjax ( http://datatables.net/plug-ins/api#fnReloadAjax ) to make a request to the server to load from a different feed. Rather than having a different feed document for each year, you'd just append the year as a GET parameter, and apply that to the query using the `where` method: http://editor.datatables.net/docs/current/php/class-DataTables.Editor.html#_where

    Either way, I think it will probably end up looking the same to the end users - button, or select or whatever to pick the year. It is just a case of where you want to do the filtering. If the year is a visible column, personally I'd do it client-side (since the users can then see it is a reduced set), but if not, the server-side is a good option, particularly if the data set is really large.

    Allan
  • melphmelph Posts: 37Questions: 1Answers: 0
    Thanks Allan - option 2 would seem to be what I am looking for. I will have a read through the tutorial and try to impliment this. Out of interest, using option 1, if a user was to filter the results (i.e. select sightings for 2007) and then press one of the download save buttons (PDF etc), would the saved file just be the filtered selection or all the data loaded into the table (i.e. every years sightings?)

    Thanks

    Mark
  • melphmelph Posts: 37Questions: 1Answers: 0
    // new section for reloading ajax?
    Hi Allan

    using the example you directed me to, I have added the following to my page...


    $.fn.dataTableExt.oApi.fnReloadAjax = function ( oSettings, sNewSource, fnCallback, bStandingRedraw )
    {
    if ( typeof sNewSource != 'undefined' && sNewSource != null ) {
    oSettings.sAjaxSource = sNewSource;
    }

    // Server-side processing should just call fnDraw
    if ( oSettings.oFeatures.bServerSide ) {
    this.fnDraw();
    return;
    }

    this.oApi._fnProcessingDisplay( oSettings, true );
    var that = this;
    var iStart = oSettings._iDisplayStart;
    var aData = [];

    this.oApi._fnServerParams( oSettings, aData );

    oSettings.fnServerData.call( oSettings.oInstance, oSettings.sAjaxSource, aData, function(json) {
    /* Clear the old information from the table */
    that.oApi._fnClearTable( oSettings );

    /* Got the data - add it to the table */
    var aData = (oSettings.sAjaxDataProp !== "") ?
    that.oApi._fnGetObjectDataFn( oSettings.sAjaxDataProp )( json ) : json;

    for ( var i=0 ; i
  • allanallan Posts: 61,452Questions: 1Answers: 10,055 Site admin
    > 1, if a user was to filter the results (i.e. select sightings for 2007) and then press one of the download save buttons (PDF etc), would the saved file just be the filtered selection or all the data loaded into the table (i.e. every years sightings?)

    That is one of the options available. You can enable or disable that using TableTools - http://datatables.net/extras/tabletools/button_options#bSelectedOnly

    > The table does not reload with data from browsers3.php, rather just refreshes the page.

    You aren't cancelling the default action - it is doing the default action as well as fnReloadAjax. You need `e.parentDefault()` in your click function (and reference `e` as the parameter passed in).

    Allan
  • melphmelph Posts: 37Questions: 1Answers: 0
    Thanks for your help, really appreciated

    I have now used the following code to detect the key press and prevent default actio nand route to new script

    [code]

    $(document).ready(function() {
    $("press").click(function(event)
    event.preventDefault();
    oTable.fnReloadAjax('php/browsers3.php');
    });
    });


    [/code]

    My html button is as

    [code] [/code]

    I think I must be close to getting this to work, but it still reloads the original page not the new page - is the positioning of the code important i.e. at present it sits below all the code for the datatables \ editor?
  • melphmelph Posts: 37Questions: 1Answers: 0
    Close topic, I have found a possible solution. Many thanks for your help
This discussion has been closed.