How to save the sate of a grid using a database instead of a localstore jqgrid

Question

I have a grid that I created using the jqGrid PHP API and the Choose Columns add-on (to show/hide columns). I need to add the ability to save the grid state (after doing any filtering, altering the column order or hiding any columns).

I've seen several posts that use either cookies or a localStore to save the state of the grid. However, I want to be able to save it in a database so that I can restore it later, the next time the user logs into the system.

Here's the PHP code I have for the grid:

    require_once JQGRID_PATH."php/jqGrid.php";
    require_once JQGRID_PATH."php/jqGridPdo.php";

    // Connection to the server
    $conn = new PDO(DB_DSN,DB_USER,DB_PASS);

    // Create the jqGrid instance
    $grid = new jqGridRender($conn);

    // Write the SQL Query
    $grid->SelectCommand = 'SELECT timestamp, user_name, action_type FROM audit_log';

    // set the ouput format to json
    $grid->dataType = 'json';

    // Let the grid create the model
    $grid->setColModel();

    // Set the url from where we obtain the data
    $grid->setUrl('audit_log');

    // Set grid caption using the option caption
    $grid->setGridOptions(array(
        "caption"=>"Audit Log Report",
        "height"=>470,
        "width" => 1100,
        "rowNum"=>25,
        "shrinkToFit"=>false,
        "sortname"=>"timestamp",
        "sortorder"=>"desc",
        "rowList"=>array(25,50,100)
    )); 

    $grid->navigator = true;
    $grid->setNavOptions(
        'navigator', array(
            "excel"=>true,
            "add"=>false,
            "edit"=>false,
            "view"=>false,
            "del"=>false,
            "pdf"=>true
        )
    );

    $grid->renderGrid('#grid','#pager', true, null, null, true, true, true);

Also, the posts that I've seen that use localStore to save the grid state are all built using jQuery and not the jqGrid PHP Suite. Any ideas on how I can accomplish this with the jqGrid PHP API?

Any help would be greatly appreciated.

Thanks!

Mike Brant · Answer 1 · 2013-01-11T01:34:36.407

If your grid options are an array, why not just serialize that array and save it to the database in a record associated to that user?

If you wanted to get really fancy, you could extend the jqGridRender class with methods such as persist() and load() to persist the options to a DB and load them back in.

So assuming you have a user object of some sort, maybe you do someting like this:

$grid = new persistingJQGrid($conn); // instantiate your special extended class
$grid->setUser($user);
$grid->load();
...

or when persisting:

$grid->persist()

score 0 · Answer 2 · edited May 23 '17 at 12:04

In the answer I have shown how to save some information which represent the state of the grid in the localStorage and how to create the grid with the information. In the answer you can find saveObjectInLocalStorage method which uses window.localStorage.setItem to save the object which represent the state in the local storage. In you need to save the information in the database you need just replace window.localStorage.setItem to $.ajax which send the information to the server. In the same way you can replace getObjectFromLocalStorage used once during initializing of the grid to direct initializing of the data with the previously saved stated read from the database. So the approach can be easy modified to any place of holding of the state of the grid.

How to save the sate of a grid using a database instead of a localstore jqgrid

2 Answers2