Show Posts

Help for ParamQuery Grid (free version) / Place data from mysql into grid to sort

« on: April 07, 2016, 09:38:44 pm »

I tried doing the remote sorting method but the fetched data does not appear in the grid

Javascript part(fetchData.php):

Code: [Select]

<?php
require_once 'require/include.php';
?>

<html>
<head>
</head>
<body>
<script>
$(function () {

        var colM = [
        { title: "Excel ID", width: 100, dataIndx:"ExcelID" },
        { title: "Excel Name", width: 130, dataIndx:"ExcelName" },
        { title: "Excel Email", width: 100, dataIndx:"ExcelEmail",  dataType:"string" }		
		];
             var dataModel = {
            location: "remote",
            sorting: "remote",
            dataType: "JSON",
            method: "GET",
            sortIndx: ["ExcelID"],
            sortDir: ["down"],
            //url: "/pro/orders/get", //for ASP.NET
            url: "orders.php", //for PHP
            getData: function (dataJSON) {
                return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: dataJSON.data };
            }
        }

        var gObj = {
            width: 700, height: 400,
            dataModel: dataModel,
            wrap: false,
            hwrap: false,
            showBottom:false,
            collapsible:false,
            colModel: colM,            
            title: "Excel Table",
            resizable: true,
            columnBorders: true,
            freezeCols: 0
        };
        var $grid = $("div#grid_sorting_remote").pqGrid(gObj);

        //bind the select list.
        $("#grid_remote_sorting_select").change(function (evt) {
            var val = $(this).val();
            var DM = $grid.pqGrid("option", "dataModel");
            if (val == "multiple") {
                DM.sortIndx = [DM.sortIndx];
                DM.sortDir = [DM.sortDir];
            }
            else {
                DM.sortIndx = DM.sortIndx[0];
                DM.sortDir = DM.sortDir[0];
            }
            $grid.pqGrid("option", "dataModel", DM);
            $grid.pqGrid("refreshDataAndView");
        });
    });
</script>
    <div style="margin: auto; width: 250px; margin-bottom: 20px;">
        Sorting type:
        <select id="grid_remote_sorting_select">
            <option value="single">Single Column</option>
            <option value="multiple" selected="">Multiple Column</option>
        </select>
    </div>
    <div id="grid_sorting_remote" style="margin: 5px auto;">
    </div>
</body>
</html>

PHP part(orders.php):

Code: [Select]

<?php
require_once 'require/conf.php';
class ColumnHelper
{
    public static function isValidColumn($dataIndx)
    {            
        if (preg_match('/^[a-z,A-Z]*$/', $dataIndx))
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}
class SortHelper
{
    public static function deSerializeSort($pq_sort)
    {
        $sorters = json_decode($pq_sort);
        $columns = array();
        $sortby = "";
        foreach ($sorters as $sorter){
            $dataIndx = $sorter->dataIndx;
            $dir = $sorter->dir;
            if ($dir == "up")
            {
                $dir = "asc";
            }
            else
            {
                $dir = "desc";
            }
            if (ColumnHelper::isValidColumn($dataIndx))
            {
                $columns[] = $dataIndx . " " . $dir;
            }
            else{
                throw new Exception("invalid column ".$dataIndx);
            }
        }
        if (sizeof($columns) > 0)
        {
            $sortby = " order by " . join(", ", $columns);
        }
        return $sortby;
    }
}

//orders.php
$sortQuery = "";
if(isset($_GET["pq_sort"]))
{
    $pq_sort = $_GET["pq_sort"];
    $sortQuery = SortHelper::deSerializeSort($pq_sort);
}

$sql = "SELECT * FROM tbl_excel ".$sortQuery;

$dsn = 'mysql:host='.DB_HOSTNAME.';dbname='.DB_NAME;
$options = array(
    PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
); 
$dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);

$stmt = $dbh->prepare($sql);

$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo "{\"data\":". json_encode( $rows ) ." }" ;

?>

Messages - smolsarah

Help for ParamQuery Grid (free version) / Re: Place data from mysql into grid to sort

Help for ParamQuery Grid (free version) / Place data from mysql into grid to sort