Author Topic: Problems with remote header filtering - No data  (Read 6289 times)

Andre

  • Newbie
  • *
  • Posts: 2
    • View Profile
Problems with remote header filtering - No data
« on: June 17, 2014, 08:32:55 pm »
Hello,

today I downloaded a trial version and I'm testing the grid functionalities.
It Looks great and offers a lot of excellent Features. Smaller grids with "local data" worked really fine.

But I got stuck when playing around with the remote header filtering example.
I adapted the code snippets from the demo and tried to load the data from an mysql database.

What I receive is a perfect grid, but wothout any content! It says "No rows to Display".

What I have done:
I created a table called "ordercustomers" in the db "paramquery".
It includes the columns: ShipCountry, ContactName, OrderID, OrderDate, ShipRegion, paid, ShipVia, RequiredDate, ShippedDate, Freight, ShipName, ShipAddress, ShipCity, ShipPostalCode.

I created a php file to fetch the data "getdata.php" I added the DB_HOSTNAME, DB_NAME, DB_USERNAME and DB_PASSWORD.

I changed the URL code in the example.


Hopefully someone can give me a hint and help me out.

Greetings,
Andre


code grid2.html:

Code: [Select]
<html>
  <head>
    <title>ParamQuery Grid Pro Demos</title>

<!--jQuery dependencies-->
 <link rel="stylesheet"
     href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/themes/base/jquery-ui.css" />
 <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>   
 <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/jquery-ui.min.js"></script>
 
 <!--ParamQuery Grid files-->
 <link rel="stylesheet" href="pqgrid.min.css" />
 <script type="text/javascript" src="pqgrid.min.js" ></script>   
 
 <!--Include Touch Punch file to provide support for touch devices-->
 <script type="text/javascript" src="touch-punch/touch-punch.js" ></script>

<!--Insert demo script-->
<script type="text/javascript">
$(function () {
//calender dialog
function pqDatePicker(ui) {
        var $this = $(this);
        $this
            .css({ zIndex: 3, position: "relative" })
            .datepicker({
                yearRange: "-20:+0", //20 years prior to present.
                changeYear: true,
                changeMonth: true,
                showButtonPanel: true,
                onClose: function (evt, ui) {
                    $(this).focus();
                }
            });
        //default From date
        $this.filter(".pq-from").datepicker("option", "defaultDate", new Date("01/01/1996"));
        //default To date
        $this.filter(".pq-to").datepicker("option", "defaultDate", new Date("12/31/1998"));
    }
    //define colModel
    var colM = [
    { title: "ShipCountry", width: 100, dataIndx: "ShipCountry",
        filter: { type: 'textbox', condition: 'begin', listeners: ['change'] }
    },
    { title: "Customer Name", width: 120, dataIndx: "ContactName",
        filter: { type: 'textbox', condition: 'begin', listeners: ['change'] }
    },
    { title: "Order ID", minWidth: 130, dataIndx: "OrderID", dataType: "integer",
        filter: { type: 'textbox', condition: "between", listeners: ['change'] }
    },
    { title: "Order Date", minWidth: "190", dataIndx: "OrderDate", dataType: "date",
        filter: { type: 'textbox', condition: "between", init: pqDatePicker, listeners: ['change'] }
    },
    { title: "Shipping Region", width: 130, dataIndx: "ShipRegion",
        filter: { type: 'select',
            //attr: "multiple", //for multiple
            //style:"height:120px;",//for multiple
            condition: 'equal',
            //condition: 'range', //for multiple
            valueIndx: "ShipRegion",
            labelIndx: "ShipRegion",
            groupIndx: "ShipCountry",
            prepend: { '': '--Select--' },
            listeners: ['change']
        }
    },
    { title: "Paid", width: 100, dataIndx: "paid", dataType: "bool", align: "center",
        filter: { type: "checkbox", subtype: 'triple', condition: "equal", listeners: ['click'] }
    },
    { title: "Shipping Via", width: 140, dataIndx: "ShipVia",
        filter: { type: "select",
            condition: 'equal',
            prepend: { '': '--Select--' },
            valueIndx: "ShipVia",
            labelIndx: "ShipVia",
            listeners: ['change']
        }
    },
    { title: "Required Date", width: 100, dataIndx: "RequiredDate", dataType: "date" },
    { title: "Shipped Date", width: 100, dataIndx: "ShippedDate", dataType: "date" },
    { title: "Freight", width: 100, align: "right", dataIndx: "Freight", dataType: "float" },
    { title: "Shipping Name", width: 150, dataIndx: "ShipName" },
    { title: "Shipping Address", width: 270, dataIndx: "ShipAddress" },
    { title: "Shipping City", width: 100, dataIndx: "ShipCity" },
    { title: "Shipping Postal Code", width: 180, dataIndx: "ShipPostalCode" }
 
    ];
    //define dataModel
    var dataModel = {
        location: "remote",
        sorting: "local",
        dataType: "JSON",
        method: "GET",
        sortIndx: "OrderID",
        sortDir: "up",
        //url: "/pro/orders/get",
        url: "getdata.php",//for PHP
        getData: function (dataJSON) {
            return { data: dataJSON.data };
        }
    }
    var obj = { width: 1200, height: 800,
        dataModel: dataModel,
        colModel: colM,
        pageModel: { type: 'local', rPP: 20 },
        editable: false,
        selectionModel: { type: 'cell' },
        filterModel: { on: true, mode: "AND", header: true },
        title: "Shipping Orders",
        resizable: true,
        hwrap:false,
        freezeCols: 2
    };
    var $grid = $("#grid_filter").pqGrid(obj);
 
    //load shipregion and shipvia dropdowns in first load event.               
    $grid.one("pqgridload", function (evt, ui) {
        var column = $grid.pqGrid("getColumn", { dataIndx: "ShipRegion" });
        var filter = column.filter;
        filter.cache = null;
        filter.options = $grid.pqGrid("getData", { dataIndx: ["ShipCountry", "ShipRegion"] });
 
        var column = $grid.pqGrid("getColumn", { dataIndx: "ShipVia" });
        var filter = column.filter;
        filter.cache = null;
        filter.options = $grid.pqGrid("getData", { dataIndx: ["ShipVia"] });
 
        $grid.pqGrid("refreshHeader");
    });
});
</script>

</head>
  <body>
<div id="grid_filter" style="margin: 5px auto;"></div>
  </body>
</html>


Code getdata.php
Code: [Select]
<?
class ColumnHelper
{
    public static function isValidColumn($dataIndx)
    {           
        if (preg_match('/^[a-z,A-Z]*$/', $dataIndx))
        {
            return true;
        }
        else
        {
            return false;
        }
    }
}
class FilterHelper
{
    public static function deSerializeFilter($pq_filter)       
    {
        $filterObj = json_decode($pq_filter);
         
        $mode = $filterObj->mode;               
        $filters = $filterObj->data;
         
        $fc = array();
        $param= array();
 
        foreach ($filters as $filter)
        {           
            $dataIndx = $filter->dataIndx;           
            if (ColumnHelper::isValidColumn($dataIndx) == false)
            {
                throw new Exception("Invalid column name");
            }
            $text = $filter->value;
            $condition = $filter->condition;
             
            if ($condition == "contain")
            {
                $fc[] = $dataIndx . " like CONCAT('%', ?, '%')";
                $param[] = $text;
            }
            else if ($condition == "notcontain")
            {
                $fc[] = $dataIndx . " not like CONCAT('%', ?, '%')";
                $param[] = $text;               
            }
            else if ($condition == "begin")
            {
                $fc[] = $dataIndx . " like CONCAT( ?, '%')";
                $param[] = $text;                               
            }
            else if ($condition == "end")
            {
                $fc[] = $dataIndx . " like CONCAT('%', ?)";
                $param[] = $text;                               
            }
            else if ($condition == "equal")
            {
                $fc[] = $dataIndx . " = ?";
                $param[] = $text;                               
            }
            else if ($condition == "notequal")
            {
                $fc[] = $dataIndx . " != ?";
                $param[] = $text;                               
            }
            else if ($condition == "empty")
            {             
                $fc[] = "ifnull(" . $dataIndx . ",'')=''";               
            }
            else if ($condition == "notempty")
            {
                $fc[] = "ifnull(" . $dataIndx . ",'')!=''";               
            }
            else if ($condition == "less")
            {
                $fc[] = $dataIndx . " < ?";
                $param[] = $text;                                               
            }
            else if ($condition == "great")
            {
                $fc[] = $dataIndx . " > ?";
                $param[] = $text;                                                               
            }
        }
        $query = "";
        if (sizeof($filters) > 0)
        {
            $query = " where " . join(" ".$mode." ", $fc);
        }
 
        $ds = new stdClass();
        $ds->query = $query;
        $ds->param = $param;
        return $ds;
    }
}//end of class
 
 
//orders.php
$filterQuery = "";
$filterParam = array();
if ( isset($_GET["pq_filter"]))
{
    $pq_filter = $_GET["pq_filter"];
    $dsf = FilterHelper::deSerializeFilter($pq_filter);
    $filterQuery = $dsf->query;
    $filterParam = $dsf->param;
}   
 
$sql = "Select * from ordercustomers ".$filterQuery;

$DB_HOSTNAME = 'myhostname';
$DB_NAME = 'paramquery';
$DB_USERNAME = 'myusername';
$DB_PASSWORD = 'mypassword';

$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($filterParam);
 
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
 
echo "{\"data\":". json_encode( $rows ) ." }" ;
?>



paramvir

  • Administrator
  • Hero Member
  • *****
  • Posts: 5785
    • View Profile
Re: Problems with remote header filtering - No data
« Reply #1 on: June 18, 2014, 12:14:02 am »
open your browser's console and look for errors.

implement dataModel.error callback
Code: [Select]
        error: function(jqXHR, textStatus, errorThrown){
            alert(textStatus);
        },

Andre

  • Newbie
  • *
  • Posts: 2
    • View Profile
Re: Problems with remote header filtering - No data
« Reply #2 on: June 18, 2014, 12:37:34 am »
Dear paramquery,

thanks for your reply. I tried it and I found the mistake - I have forgotten the "$" for the DB_HOSTNAME, DB_NAME, DB_USERNAME and DB_PASSWORD within getdata.php.
Stupid Thing - php Basics :-[

Grid looks really great.

Greetings,
Andre