ParamQuery Grid

https://paramquery.com/tutorial/
Admin
ParamQuery Grid is a jQuery plugin that provides a high-performance data grid with virtual...
Tokens:12,924
Snippets:57
Trust Score:8.2
Update:1 year ago
Show doc for...
Tokens:
Raw
### Install ParamQuery Grid via NPM (Bash)

Source: https://paramquery.com/tutorial/index

This command installs the free version of ParamQuery Grid using npm. It also lists the peer dependencies that should be installed alongside.

```bash
npm i pqgridf
```

--------------------------------

### Call pqGrid Method Example

Source: https://paramquery.com/tutorial/index

Provides an example of calling the 'refreshRow' method on a pqGrid instance using the jQuery object syntax.

```javascript
$grid.pqGrid( 'refreshRow', { rowIndx: 2} )
```

--------------------------------

### Get pqGrid Instance (grid)

Source: https://paramquery.com/tutorial/index

Illustrates how to get a live instance of the pqGrid object. This can be done during initialization, from the jQuery object, or directly from the event callback context.

```javascript
var grid = pq.grid("#grid_json", options); //constructor (>= v3.2.0)
var grid = $grid.pqGrid('instance'); //jQueryUI >= v1.11
var grid = $grid.pqGrid('getInstance').grid;
var grid = this;
```

--------------------------------

### ParamQuery Grid Client-Side Setup (JavaScript)

Source: https://paramquery.com/tutorial/php

Configures the ParamQuery grid with column definitions and a data model for remote data fetching. It specifies the grid's width, height, and title, and uses a GET request to retrieve JSON data from 'remote.php'.

```JavaScript
$(function () {
    var colM = [
        { title: "Order ID", width: 100, dataIndx: "OrderID" },            
        { title: "Customer Name", width: 130, dataIndx: "CustomerName" },
        { title: "Product Name", width: 190, dataIndx: "ProductName" },
        { title: "Unit Price", width: 100, dataIndx: "UnitPrice", align: "right" },
        { title: "Quantity", width: 100, dataIndx: "Quantity", align:"right" },            
        { title: "Order Date", width: 100, dataIndx: "OrderDate"},
        { title: "Required Date", width: 100, dataIndx: "RequiredDate" },
        { title: "Shipped Date", width: 100, dataIndx: "ShippedDate" },
        { title: "ShipCountry", width: 100, dataIndx: "ShipCountry" },
        { title: "Freight", width: 100, align: "right", dataIndx: "Freight" },
        { title: "Shipping Name", width: 120, dataIndx: "ShipName" },
        { title: "Shipping Address", width: 180, dataIndx: "ShipAddress" },
        { title: "Shipping City", width: 100, dataIndx: "ShipCity" },
        { title: "Shipping Region", width: 110, dataIndx: "ShipRegion" },
        { title: "Shipping Postal Code", width: 130, dataIndx: "ShipPostalCode" }
    ];
    var dataModel = {
        location: "remote",                        
        dataType: "JSON",
        method: "GET",
        getUrl : function () {                
            return { url: 'remote.php'};
        },
        getData: function ( response ) {                
            return { data: response };                
        }
    };
    $("div#grid_php").pqGrid({
        width: 900,
        height: 400,
        dataModel: dataModel,
        colModel: colM,  
        bottomVisible: false,
        title: "Shipping Orders"
    });
});
```

--------------------------------

### Import ParamQuery Module (JavaScript)

Source: https://paramquery.com/tutorial/index

Demonstrates how to import the ParamQuery module into your application after installing it via npm.

```javascript
import pq from 'pqgridf';
```

--------------------------------

### ParamQuery Grid Remote Data Model Configuration

Source: https://paramquery.com/tutorial/index

Configure the dataModel for remote data loading by specifying options like url, getUrl, getData, dataType, and method. The grid automatically handles AJAX calls and query string construction for GET requests, prefixing parameters with 'pq_'. For more control, implement dataModel.getUrl or use dataModel.postData and dataModel.postDataOnce.

```javascript
dataModel: {
  location: "remote",
  url: "/your-data-url",
  getUrl: function() { /* custom URL logic */ },
  getData: function(response) { /* data processing */ },
  dataType: "json",
  method: "GET",
  postData: { /* additional POST data */ },
  postDataOnce: { /* data sent once */ }
}
```

--------------------------------

### ParamQuery Data Model Configuration (JavaScript)

Source: https://paramquery.com/tutorial/php

Defines the data model for the ParamQuery grid, specifying remote data fetching, JSON data type, and GET method. The getUrl callback returns the server endpoint, and getData processes the server response.

```JavaScript
var dataModel = {
    location: "remote",                        
    dataType: "JSON",
    method: "GET",
    getUrl : function () {                
        return { url: 'remote.php'};
    },
    getData: function ( response ) {                
        return { data: response };                
    }
}
```

--------------------------------

### PHP Server-Side Paging Logic for jQuery Grid

Source: https://paramquery.com/tutorial/php

Implements server-side paging logic for a jQuery grid. It retrieves the current page number (`pq_curpage`) and results per page (`pq_rpp`) from POST data. It then queries the database to get the total number of records, calculates the offset using a `pageHelper` function (assumed to be defined elsewhere), and prepares a SQL query to fetch the data for the current page.

```PHP
<?php

require_once '../conf.php';

if(isset($_POST["pq_curpage"]) && isset($_POST["pq_rpp"]) )
{
    $pq_curPage = $_POST["pq_curpage"];    
    
    $pq_rPP=$_POST["pq_rpp"];
    
    $sql = "Select count(*) from invoices";
    
    $dbh = getDatabaseHandle();
    $stmt = $dbh->query($sql);    
    $total_Records = $stmt->fetchColumn();
    
    $skip = pageHelper($pq_curPage, $pq_rPP, $total_Records);
                    
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,

```

--------------------------------

### Set Custom Localization String

Source: https://paramquery.com/tutorial/index

Allows setting or changing specific localization strings for a ParamQuery grid, similar to how other options are managed. This example shows how to set the 'No Rows found.' message.

```javascript
$grid.pqGrid({ strNoRows : 'No Rows found.' });
```

```javascript
$grid.pqGrid( 'option', { strNoRows : 'No Rows found.' });
```

--------------------------------

### PHP: Serve Paged JSON Data for Grid

Source: https://paramquery.com/tutorial/editing

This PHP code snippet handles serving paged data for a grid, responding to `pq_curpage` (current page) and `pq_rpp` (records per page) GET parameters. It calculates the total number of records, determines the data offset, fetches the relevant subset of data, converts boolean values, and formats the response with total records, current page, and the data itself.

```PHP
if( isset($_GET
```

```text

```

```text

```

--------------------------------

### Get jQuery Grid Object ($grid)

Source: https://paramquery.com/tutorial/index

Demonstrates how to obtain the jQuery object representing the grid container. This can be done using a CSS selector, or by accessing the event target within callbacks.

```javascript
var $grid = $(".selector"); // e.g., $("#grid_json");
var $grid = $( event.target ).closest('.pq-grid');
var $grid = grid.widget();
```

--------------------------------

### PHP: Handle Grid Row Updates via GET Request

Source: https://paramquery.com/tutorial/editing

This PHP code snippet processes single row updates submitted from a grid interface via a GET request. It calls the updateSingle function to modify the corresponding record in the database and returns a success message.

```PHP
else if( isset($_GET["pq_update"]))
{
    updateSingle(getDBH(), $_GET);
    $response =  "{\"result\": \"success\"}";
}
```

--------------------------------

### PHP: Handle Grid Row Additions via GET Request

Source: https://paramquery.com/tutorial/editing

This PHP code snippet handles single row additions initiated from a grid interface (like ParamQuery) via a GET request. It calls the addSingle function to insert the new record into the database and returns the newly generated record ID in a JSON response.

```PHP
if( isset($_GET["pq_add"]))
{
    $response = "{\"recId\": \"" . addSingle(getDBH(), $_GET ). "\"}";
}
```

--------------------------------

### PHP: Handle Grid Row Deletions via GET Request

Source: https://paramquery.com/tutorial/editing

This PHP code snippet handles the deletion of single rows initiated from a grid interface via a GET request. It calls the deleteSingle function to remove the specified record from the database and returns a success message.

```PHP
else if( isset($_GET["pq_delete"]))
{
    deleteSingle(getDBH(), $_GET);
    $response =  "{\"result\": \"success\"}";
}
```

--------------------------------

### Call pqGrid Methods using Instance

Source: https://paramquery.com/tutorial/index

Demonstrates the concise syntax for calling pqGrid methods directly on the grid instance. This method is faster and preferred for most frameworks.

```javascript
grid.method_name( parameters );
```

--------------------------------

### Include Files for ParamQuery Grid (No npm)

Source: https://paramquery.com/tutorial/index

This snippet shows the necessary HTML and CSS includes for using ParamQuery Grid without the npm package manager. It requires jQuery and jQueryUI, along with ParamQuery's own CSS and JS files. Optional includes for custom themes, jsZip export, localization, and touch support are also shown.

```html
<!--jQuery dependency, any stable version of jQuery-->
<script src="https://unpkg.com/jquery@2.2.4/dist/jquery.js"></script> 
<!--jQueryUI version 1.11.4 -->
<link rel="stylesheet" href="https://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css" />
<script src="https://code.jquery.com/ui/1.11.4/jquery-ui.min.js"></script>
<!--ParamQuery Grid css files-->
<link rel="stylesheet" href="path to pqgrid.(min/dev).css" /> 
<!--add pqgrid.ui.css for jQueryUI theme support-->
<link rel="stylesheet" href="path to pqgrid.ui.(min/dev).css" />
<!--ParamQuery Grid custom theme e.g., office, bootstrap, rosy, chocolate, etc (optional)-->
<link rel="stylesheet" href="path to custom theme pqgrid.css" />
<!--Include jsZip file (v2.5.0) before pqgrid js file to support xlsx and zip export (optional)-->
<script type="text/javascript" src="path to jsZip.js"></script> 
<!--ParamQuery Grid js files-->
<script type="text/javascript" src="path to pqgrid.(min/dev).js"></script> 
<!--ParamQuery Grid localization file-->
<script src="localize/pq-localize-xx.js"></script>
<!--Include pqTouch file to provide support for touch devices (optional)-->
<script type="text/javascript" src="path to pqtouch.min.js"></script>  
```

--------------------------------

### Initialize ParamQuery Grid (JavaScript)

Source: https://paramquery.com/tutorial/index

This snippet illustrates the basic structure for initializing a ParamQuery Grid within a jQuery ready function. It highlights the use of the `.pqGrid()` method and the importance of the `colModel` and `dataModel` options.

```javascript
$( ".selector" ).pqGrid( obj );
```

--------------------------------

### Include jQueryUI 1.12 and ParamQuery Grid Dependencies (HTML)

Source: https://paramquery.com/tutorial/index

This snippet shows how to include the necessary JavaScript and CSS files for jQuery, jQueryUI, and ParamQuery Grid using CDN links. It also includes optional files for JSZip export and custom themes.

```html
<script src="https://unpkg.com/jquery@2.2.4/dist/jquery.js"></script>
<link href="https://unpkg.com/jquery-ui-pack@1.12.2/jquery-ui.css" rel="stylesheet" />
<link href="https://unpkg.com/jquery-ui-pack@1.12.2/jquery-ui.structure.css" rel="stylesheet" />
<link href="https://unpkg.com/jquery-ui-pack@1.12.2/jquery-ui.theme.css" rel="stylesheet" />
<script src="https://unpkg.com/jquery-ui-pack@1.12.2/jquery-ui.js"></script>
<link rel="stylesheet" href="path to pqgrid.(min/dev).css" />
<link rel="stylesheet" href="path to pqgrid.ui.(min/dev).css" />
<link rel="stylesheet" href="path to custom theme pqgrid.css" />
<script type="text/javascript" src="path to jsZip.js"></script>
<script type="text/javascript" src="path to pqgrid.(min/dev).js"></script>
<script src="localize/pq-localize-xx.js"></script>
<script type="text/javascript" src="path to pqtouch.min.js"></script>
```

--------------------------------

### Initialize ParamQuery Grid with beforeSort Callback

Source: https://paramquery.com/tutorial/index

This snippet demonstrates how to initialize a ParamQuery grid and attach a callback function to the 'beforeSort' event. The callback function is executed before sorting occurs.

```javascript
pq.grid( selector, {
    beforeSort: function( evt, ui ){
        //code here 
    },
    //other options.
});
```

--------------------------------

### Initialize ParamQuery Grid with Local JSON Data

Source: https://paramquery.com/tutorial/index

This snippet shows how to initialize a ParamQuery grid using locally defined JSON data. It includes defining the data array, the column model with data types and alignment, and the grid options object. The grid is then rendered into a specified div element.

```javascript
function(){
  //JSON data (array of objects) can be defined locally 
  //or might be a response from an AJAX call from web server/service.
  var data = [
    { rank: 1, company: 'Exxon Mobil', revenues: 339938.0, profits: 36130.0 },
    { rank: 2, company: 'Wal-Mart Stores', revenues: 315654.0, profits: 11231.0 },
    { rank: 3, company: 'Royal Dutch Shell', revenues: 306731.0, profits: 25311.0 },
    { rank: 4, company: 'BP', revenues: 267600.0, profits: 22341.0 },
    { rank: 5, company: 'General Motors', revenues: 192604.0, profits: -10567.0 },
    { rank: 6, company: 'Chevron', revenues: 189481.0, profits: 14099.0 },
    { rank: 7, company: 'DaimlerChrysler', revenues: 186106.3, profits: 3536.3 },
    { rank: 8, company: 'Toyota Motor', revenues: 185805.0, profits: 12119.6 },
    { rank: 9, company: 'Ford Motor', revenues: 177210.0, profits: 2024.0 },
    { rank: 10, company: 'ConocoPhillips', revenues: 166683.0, profits: 13529.0 },
    { rank: 11, company: 'General Electric', revenues: 157153.0, profits: 16353.0 },
    { rank: 12, company: 'Total', revenues: 152360.7, profits: 15250.0 },
    { rank: 13, company: 'ING Group', revenues: 138235.3, profits: 8958.9 },
    { rank: 14, company: 'Citigroup', revenues: 131045.0, profits: 24589.0 },
    { rank: 15, company: 'AXA', revenues: 129839.2, profits: 5186.5 },
    { rank: 16, company: 'Allianz', revenues: 121406.0, profits: 5442.4 },
    { rank: 17, company: 'Volkswagen', revenues: 118376.6, profits: 1391.7 },
    { rank: 18, company: 'Fortis', revenues: 112351.4, profits: 4896.3 },
    { rank: 19, company: 'Crédit Agricole', revenues: 110764.6, profits: 7434.3 },
    { rank: 20, company: 'American Intl. Group', revenues: 108905.0, profits: 10477.0 }
  ];
  //array of columns.
  var colModel = [
    {
      title: "Rank", //title of column. 
      width: 100, //initial width of column
      dataType: "integer", //data type of column
      dataIndx: "rank" //should match one of the keys in row data.
    },
    {
      title: "Company", 
      width: 200, 
      dataType: "string", 
      dataIndx: "company"
    },
    {
      title: "Revenues ($ millions)", 
      width: 150, 
      dataType: "float", 
      align: "right", 
      dataIndx: "revenues"
    },
    {
      title: "Profits ($ millions)", 
      width: 150, 
      dataType: "float", 
      align: "right", 
      dataIndx: "profits"
    }
  ];
  //main object to be passed to pqGrid constructor. 
  var obj = {
    width: 700, //width of grid
    height: 400, //height of grid
    colModel: colModel,
    dataModel: {data: data}
  };
  
  $("#grid_json").pqGrid( obj );
});
```

--------------------------------

### Call pqGrid Methods using jQuery Object

Source: https://paramquery.com/tutorial/index

Shows the syntax for calling pqGrid methods using the jQuery object. This method is safer but more verbose and slower.

```javascript
$grid.pqGrid('method_name', parameters);
```

--------------------------------

### Bind pq Events using Callback Options

Source: https://paramquery.com/tutorial/index

Explains how to bind ParamQuery events by providing callback functions directly in the initialization options. This method is compatible with Angular, jQuery, plain JS, React, and Vue.

```javascript
// Example of using callback options during initialization
// var grid = pq.grid("#grid_json", {
//   // ... other options
//   events: {
//     'cellClick': function(event, ui) {
//       console.log('Cell clicked:', ui);
//     }
//   }
// });
```

--------------------------------

### Applying jQueryUI Themes to ParamQuery Grid

Source: https://paramquery.com/tutorial/index

Integrate jQueryUI themes with ParamQuery Grid by referencing the desired theme's CSS file. Replace 'smoothness' with the name of any available jQueryUI theme (e.g., 'redmond') in the stylesheet link. Custom themes can be created using the jQueryUI Themeroller.

```html
<!-- Using Redmond theme -->
<link rel="stylesheet" href="https://code.jquery.com/ui/1.11.4/themes/redmond/jquery-ui.css" />

<!-- Default smoothness theme -->
<link rel="stylesheet" href="https://code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css" />
```

--------------------------------

### Include ParamQuery Grid Styles in Angular (CSS)

Source: https://paramquery.com/tutorial/index

Shows how to import ParamQuery Grid and jQueryUI CSS files within an Angular application's styles.css file using the @import directive.

```css
/* Add application styles & imports to this file! */
@import '~jquery-ui-pack/jquery-ui.css';
@import '~jquery-ui-pack/jquery-ui.structure.css';
@import '~jquery-ui-pack/jquery-ui.theme.css';
@import '~pqgridf/pqgrid.min.css';
@import '~pqgridf/pqgrid.ui.min.css';
@import '~pqgridf/themes/steelblue/pqgrid.css';
```

--------------------------------

### HTML Placeholder for ParamQuery Grid

Source: https://paramquery.com/tutorial/index

This HTML snippet defines a div element with a specific ID, which serves as the container or placeholder for the ParamQuery grid to be rendered.

```html
<div id="grid_json"></div>
```

--------------------------------

### Update beforeSort Callback Option on Grid Instance

Source: https://paramquery.com/tutorial/index

This snippet demonstrates how to update the 'beforeSort' event callback option on an existing ParamQuery grid instance. This allows for dynamic modification of event handlers.

```javascript
grid.option( "beforeSort", function( evt, ui ){
    //code here 
});
```

--------------------------------

### JavaScript ParamQuery Data Model for Remote Paging

Source: https://paramquery.com/tutorial/php

Configures the ParamQuery data model for remote server-side paging. It specifies the data location as 'remote', enables remote paging, sets the initial current page to 1, defines the data type as JSON, and uses the POST method. The `getUrl` function constructs the request URL and includes paging parameters (`pq_rpp`, `pq_curpage`), while `getData` formats the server response.

```JavaScript
var dataModel = {
    location: "remote",
    paging: "remote",
    curPage: 1,
    dataType: "JSON",
    method: "POST",
    getUrl: function (ui) {
        return {
            url: "remote.php",
            data: {
                pq_rpp: this.rPP,
                pq_curpage: this.curPage
            }
        }
    },
    getData: function (dataJSON) {
        return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: dataJSON.data };
    }
}
```

--------------------------------

### PHP Database Configuration and Utility Functions

Source: https://paramquery.com/tutorial/php

Defines database connection parameters (hostname, username, password, database name) and provides utility functions for establishing a PDO database connection and validating column names. The `getDataBaseHandle` function sets up the PDO connection with UTF-8 support, while `isValidColumn` uses regex to ensure column names are alphanumeric.

```PHP
//config.php

// mysql example

define('DB_HOSTNAME','localhost'); // database host name
define('DB_USERNAME', 'username');     // database user name
define('DB_PASSWORD', 'password'); // database password
define('DB_NAME', 'northwind'); // database name

function getDataBaseHandle(){
    $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);
}
//check every column name
function isValidColumn($dataIndx){
    if (preg_match('/^[a-z,A-Z]*$/', $dataIndx))
    {
        return true;
    }
    else
    {
        return false;
    }
}
```

--------------------------------

### Set beforeSort Event Listener on Grid Instance

Source: https://paramquery.com/tutorial/index

This snippet shows how to set an event listener for the 'beforeSort' event directly on a ParamQuery grid instance after initialization. This method allows for binding event listeners to the grid instance.

```javascript
grid.on( "beforeSort", function( evt, ui ){
    //code here.
});
```

--------------------------------

### PHP: Serve JSON Data from Database to Grid

Source: https://paramquery.com/tutorial/editing

This PHP code snippet demonstrates how to fetch data from a database and serve it as JSON to a grid. It includes iterating through rows to convert integer boolean values (0/1) to actual booleans (false/true) for grid compatibility. It assumes the existence of a `getDBH()` function for database connection and an `intToBool()` function for type conversion.

```PHP
$sql = "Select ProductID, ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Discontinued from products order by ProductID";

    $dbh = getDBH();

    $stmt = $dbh->prepare($sql);
    $stmt->execute();
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    foreach ($products as $i=> &$row)
    {
        $row['Discontinued']= intToBool($row['Discontinued']);
    }

    $response =  "{\"data\":".json_encode($products).")";
```

--------------------------------

### SQL: Create Products Table Schema

Source: https://paramquery.com/tutorial/editing

Defines the SQL schema for the 'products' table, including columns like ProductID, ProductName, UnitPrice, and Discontinued. ProductID is set as an auto-increment primary key, and a tinyint is used for boolean fields.

```sql
CREATE TABLE `products` (
  `ProductID` int(11) NOT NULL AUTO_INCREMENT,
  `ProductName` varchar(40) DEFAULT NULL,
  `SupplierID` int(11) DEFAULT NULL,
  `CategoryID` int(11) DEFAULT NULL,
  `QuantityPerUnit` varchar(20) DEFAULT NULL,
  `UnitPrice` decimal(10,4) DEFAULT '0.0000',
  `UnitsInStock` smallint(6) DEFAULT '0',
  `UnitsOnOrder` smallint(6) DEFAULT '0',
  `ReorderLevel` smallint(6) DEFAULT '0',
  `Discontinued` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`ProductID`)
) ENGINE=MyISAM AUTO_INCREMENT=96 DEFAULT CHARSET=utf8$$
```

--------------------------------

### PHP Server-Side Data Fetching for jQuery Grid

Source: https://paramquery.com/tutorial/php

Fetches data from the 'invoices' table using PDO and returns it as a JSON string. It connects to the database using the `getDataBaseHandle` function, executes a SQL query to select specific columns, fetches all results as associative arrays using `PDO::FETCH_ASSOC`, and then encodes the result using `json_encode`.

```PHP
<?php

    require_once '../conf.php';

    $dbh = getDatabaseHandle();
                
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,
            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices order by orderID limit 0,100";
    
    $stmt = $dbh->query($sql);    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($products);
?>
```

--------------------------------

### PHP Script for JSON Data (PHP)

Source: https://paramquery.com/tutorial/php

A PHP script that prepares an array of product data and serializes it into a JSON string using json_encode. This JSON output is intended to be sent as an HTTP response to the ParamQuery grid.

```PHP
<?php

$products = array();

$products[0] = array("OrderID"=>"10248","CustomerName"=>"Vins et alcools Chevalier",
```

--------------------------------

### Change Global Grid Locale

Source: https://paramquery.com/tutorial/index

Updates the locale for all ParamQuery grid instances on the page. This should be done after loading ParamQuery and its localization files, but before grid initialization. It extends the default options for both `pqGrid` and `pqPager` with the specified regional settings.

```javascript
$.extend($.paramquery.pqGrid.prototype.options, $.paramquery.pqGrid.regional[locale]);
$.extend($.paramquery.pqPager.prototype.options, $.paramquery.pqPager.regional[locale]);
```

--------------------------------

### Implement Simultaneous Paging and Sorting for jQuery Grid with PHP

Source: https://paramquery.com/tutorial/php

This snippet demonstrates how to implement simultaneous paging and sorting on the server-side for a jQuery grid using PHP and MySQL. The jQuery grid sends pq_rpp, pq_curpage, sortIndx, and sortDir parameters to the server via POST. The PHP code retrieves these parameters, validates the sort column, calculates the offset for paging, and constructs a SQL query to fetch the sorted and paginated data from the database.

```JavaScript
var dataModel = {
    location: "remote",            
    paging:"remote",
    curPage:1,
    sorting:"remote",
    dataType: "JSON",
    method: "POST",
    sortIndx: "OrderID",
    getUrl: function(){
        return {
            url: "remote.php",
            data:{
                pq_curpage:this.curPage,
                pq_rpp:this.rPP,
                sortIndx:this.sortIndx,
                sortDir:this.sortDir
            }
        }
    },
    getData: function ( dataJSON ) {                
        return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: dataJSON.data };                
    }
}
```

```PHP
<?php

    require_once '../conf.php';

if(isset($_POST["sortDir"]) && isset($_POST["sortIndx"]))
{
    $sortIndx = $_POST["sortIndx"];
    if(isValidColumn($sortIndx)==false){
        throw("invalid sort column");
    }        
    $sortDir = $_POST["sortDir"];
    $sortDir=($sortDir=="up")?"asc":"desc";
    
    $pq_curPage = $_POST["pq_curpage"];        
    $pq_rPP=$_POST["pq_rpp"];
    
    $sql = "Select count(*) from invoices";
    
    $dbh = getDatabaseHandle();
    $stmt = $dbh->query($sql);    
    $total_Records = $stmt->fetchColumn();
    
    $skip = pageHelper($pq_curPage, $pq_rPP, $total_Records);
    
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,\n            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,\n            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices order by ".$sortIndx."  ".$sortDir.\n            " limit ".$skip." , ".$pq_rPP;

    //echo $sql;
    $stmt = $dbh->query($sql);    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $sb = "{\"totalRecords\":" . $total_Records . ",\"curPage\":" . $pq_curPage . ",\"data\":".json_encode($products)."}";
    echo $sb;
}    
?>

```

--------------------------------

### Implement Filtering for jQuery Grid with PHP

Source: https://paramquery.com/tutorial/php

This snippet outlines the initial steps for implementing filtering in a jQuery grid with PHP. It describes adding a textbox and select list to the toolbar in the render callback. It also mentions adding custom fields (filterIndx & filterValue) in the dataModel to hold the dataIndex and filtered input by the user, which are then posted to the server in the dataModel.getUrl callback.

```JavaScript
//add textbox and select list in the toolbar
    obj.render = function (evt, obj) {
```

--------------------------------

### Implement server-side filtering and paging in PHP with MySQL

Source: https://paramquery.com/tutorial/php

This PHP code snippet demonstrates server-side filtering and paging for a jQuery grid using MySQL. It retrieves filter and paging parameters from the POST request, constructs a SQL query with a WHERE clause for filtering and a LIMIT clause for paging, and returns the filtered and paged data as a JSON response.

```php
<?php
    require_once '../conf.php';

    $where = "";
    $filterValue ="";
    if(isset($_POST["filterIndx"]) && isset($_POST["filterValue"]) )
    {
        $filterIndx = $_POST["filterIndx"];
        if(isValidColumn($filterIndx)==false){
            throw("invalid filter column");
        }
        $filterValue = $_POST["filterValue"];        
        $where  = " where ".$filterIndx." like CONCAT('%', ?, '%')";
    }            
    $dbh = getDatabaseHandle();
    
    //count filtered records.
    $sql = "Select count(*) from invoices ".
            $where;            

    $pq_curPage = $_POST["pq_curpage"];    
    
    $pq_rPP=$_POST["pq_rpp"];
            
    $stmt = $dbh->prepare($sql);    
    $stmt->execute(array($filterValue));
    
    $total_Records = $stmt->fetchColumn();
    
    $skip = pageHelper($pq_curPage, $pq_rPP, $total_Records);

    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,
            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices ".
            $where.
            " order by OrderID limit ".$skip." , ".$pq_rPP;
        
    //$stmt = $dbh->query($sql);    
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($filterValue));
            
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $sb = "{\"totalRecords\":" . $total_Records . ",\"curPage\":" . $pq_curPage . ",\"data\":".json_encode($products)."}";
    echo $sb;
?>
```

--------------------------------

### Bind beforeSort Event Listener to DOM Element

Source: https://paramquery.com/tutorial/index

This snippet illustrates how to bind an event listener to a DOM element for the 'pqGrid:beforeSort' event using jQuery. This method is useful for handling events that bubble up through the DOM.

```javascript
$( ".selector" ).on( "pqGrid:beforeSort", function( evt, ui ){
    //code here.
});
```

--------------------------------

### PHP: Database Connection and Data Mapping Functions

Source: https://paramquery.com/tutorial/editing

Provides essential PHP functions for interacting with a MySQL database using PDO and for mapping boolean values to tinyint types. The `getDBH` function establishes a database connection, while `boolToInt` and `intToBool` handle conversions for boolean fields.

```php
function getDBH(){
    $dsn = 'mysql:host='.DB_HOSTNAME.';dbname='.DB_NAME;
    $options = array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
        PDO::ATTR_EMULATE_PREPARES => false //to get native data types from prepared statements.(> PHP 5.3 && mysqlnd)
    );
    $dbh = new PDO($dsn, DB_USERNAME, DB_PASSWORD, $options);
    return $dbh;
}
//for mapping of boolean values to TINYINT column in db.
function boolToInt($val){
    //return $val;
    if($val=='true'){
        return 1;
    }
    else if($val =='false'){
        return 0;
    }
}
//for mapping of number to booleans.
function intToBool($val){
    if($val==1){
        return true;
    }
    else if($val ==0){
        return false;
    }
}
```

--------------------------------

### ParamQuery Grid Remote Request Variables

Source: https://paramquery.com/tutorial/index

When using remote paging, filtering, or sorting, ParamQuery Grid sends specific variables to the server. These include paging information (pq_curpage, pq_rpp), sorting details (pq_sort), and filtering criteria (pq_filter). By default, these are stringified using JSON.stringify, but this can be disabled with the 'stringify: false' option.

```javascript
// Paging
{
  pq_datatype: "json",
  pq_curpage: 1,
  pq_rpp: 20
}

// Sorting
{
  pq_datatype: "json",
  pq_sort: [
    {"dataIndx":"ShipCountry", "dir":"up"},
    {"dataIndx":"ContactName", "dir":"down"}
  ]
}

// Filtering
{
  pq_datatype: "json",
  pq_filter: {
    "mode": "AND",
    "data": [
      {"dataIndx":"ShipCountry", "value":"fr", "condition":"begin"},
      {"dataIndx":"ContactName", "value":"pa", "condition":"begin"}
    ]
  }
}
```

--------------------------------

### Using Custom Icons with ParamQuery Grid

Source: https://paramquery.com/tutorial/index

ParamQuery Grid utilizes jQueryUI icons for various UI elements. You can leverage existing jQueryUI icon classes or define your own custom icons (16x16px) by creating CSS classes and assigning them to relevant grid API parameters. This allows for personalized icon sets.

```javascript
// Example: Using a custom icon class in a toolbar button
{
  icon: "my-custom-icon-class",
  // ... other button options
}
```

--------------------------------

### ParamQuery Grid DataModel.data Structure

Source: https://paramquery.com/tutorial/index

The dataModel.data property holds the grid's row data. For local paging, it contains all data across all pages. When using remote paging, dataModel.data is updated with data only for the current page and is refreshed upon page changes or manual refreshes.

```javascript
// Local Paging: dataModel.data contains all rows
// Remote Paging: dataModel.data contains only current page rows
```

--------------------------------

### PHP Database Connection and CRUD Operations

Source: https://paramquery.com/tutorial/editing

Establishes a PDO database connection and provides functions for adding, updating, deleting single and multiple records, and handling boolean conversions. It also includes logic for processing requests related to data manipulation and retrieval, including pagination.

```PHP
'SET NAMES utf8'
```

--------------------------------

### Implement Remote Sorting for jQuery Grid with PHP

Source: https://paramquery.com/tutorial/php

This snippet demonstrates how to implement remote server-side sorting for a jQuery grid using PHP and MySQL. The jQuery grid sends the sortIndx and sortDir parameters to the server via POST. The PHP code retrieves these parameters, validates the sort column, and constructs a SQL query to fetch the sorted data from the database.

```JavaScript
var dataModel = {
        location: "remote",            
        sorting:"remote",
        dataType: "JSON",
        method: "POST",
        sortIndx: "OrderID",            
        getUrl: function(ui){
            return {
                url: "remote.php",
                data:{
                    sortIndx:this.sortIndx,
                    sortDir:this.sortDir
                }
            }
        },
        getData: function ( data ) {                
            return { data: data };                
        }
    }
```

```PHP
<?php

require_once '../conf.php';

if(isset($_POST["sortDir"]) && isset($_POST["sortIndx"]))
{
    $sortIndx = $_POST["sortIndx"];
    if(isValidColumn($sortIndx)==false){
        throw("invalid column");
    }
    $sortDir = $_POST["sortDir"];
    $sortDir=($sortDir=="up")?"asc":"desc";
        
    $dbh = getDatabaseHandle();
    
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,\n            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,\n            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices order by ".$sortIndx."  ".$sortDir;

    //echo $sql;
    $stmt = $dbh->query($sql);    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

    echo json_encode($products);    
}    
?>

```

--------------------------------

### PHP: Add Single Record to Database

Source: https://paramquery.com/tutorial/editing

This PHP function, addSingle, inserts a single record into the 'products' table. It prepares and executes an SQL INSERT statement using PDO, handling boolean to integer conversion for the 'Discontinued' field. It returns the ID of the newly inserted product.

```PHP
//add single record in db.
function addSingle($pdo, $r){
    $discontinued = boolToInt($r['Discontinued']);

    $sql = "insert into products (ProductName, QuantityPerUnit, UnitPrice, UnitsInStock, Discontinued) values (?, ?, ?, ?, ?)";

    $stmt = $pdo->prepare($sql);
    $result = $stmt->execute(array($r['ProductName'], $r['QuantityPerUnit'], $r['UnitPrice'], $r['UnitsInStock'], $discontinued));

    if($result == false) {
        throw new Exception(print_r($stmt->errorInfo(),1).PHP_EOL.$sql);
    }
    return $pdo->lastInsertId();
}
```

--------------------------------

### PHP: Echo Response Back to Grid

Source: https://paramquery.com/tutorial/editing

This PHP code snippet simply echoes the prepared response variable. This is the final step in the data processing flow, sending the result (e.g., success message, new IDs) back to the client-side grid for updates.

```PHP
echo $response;
```

--------------------------------

### Styling Alternate Rows in ParamQuery Grid

Source: https://paramquery.com/tutorial/index

Add custom styles to alternate rows in ParamQuery Grid by targeting the '.pq-grid-oddRow > td' CSS class. This allows for visual differentiation of rows when default jQueryUI themes lack specific styling for alternating rows.

```css
.pq-grid-oddRow > td {
    /* Add your custom styles here */
    background-color: #f0f0f0;
}
```

--------------------------------

### Define dataModel for remote filtering and paging in jQuery

Source: https://paramquery.com/tutorial/php

Defines the dataModel for a jQuery grid to enable remote filtering and paging. It sets the location to 'remote', specifies the data type as JSON, and configures the `getUrl` function to include filtering and paging parameters in the request. The `getData` function parses the JSON response.

```javascript
//define dataModel
var dataModel = {
    location: "remote",
    paging: "remote",
    dataType: "JSON",
    method: "POST",
    curPage: 1,
    filterIndx: "",
    filterValue: "",
    getUrl: function () {
        var data = {
            pq_curpage: this.curPage,
            pq_rpp: this.rPP};
        if (this.filterIndx && this.filterValue ) {
            data['filterIndx']=this.filterIndx;
            data['filterValue']=this.filterValue;
        }
        var obj = { url: "remote.php", data: data };
    //debugger;
        return obj;
    },
    getData: function ( dataJSON ) {                
        return { curPage: dataJSON.curPage, totalRecords: dataJSON.totalRecords, data: dataJSON.data };
    }
};
```

--------------------------------

### Implement server-side filtering in PHP with MySQL

Source: https://paramquery.com/tutorial/php

This PHP code snippet demonstrates server-side filtering for a jQuery grid using MySQL. It retrieves filter parameters from the POST request, validates the filter column, constructs a SQL query with a WHERE clause for filtering, and returns the filtered data as a JSON response.

```php
<?php
    require_once '../conf.php';

    $where = "";
    $filterValue ="";
    if(isset($_POST["filterIndx"]) && isset($_POST["filterValue"]) )
    {
        $filterIndx = $_POST["filterIndx"];
        if(isValidColumn($filterIndx)==false){
            throw("invalid filter column");
        }
        $filterValue = $_POST["filterValue"];
        $where  = " where ".$filterIndx." like CONCAT('%', ?, '%')";
    }            
        
    $dbh = getDatabaseHandle();
    
    $sql = "Select OrderID,CustomerName,ProductName,UnitPrice,Quantity,
            OrderDate,RequiredDate,ShippedDate,ShipCountry,Freight,ShipName,
            ShipAddress,ShipCity,ShipRegion,ShipPostalCode from invoices ".
            $where.
            " order by OrderID";

    //echo $sql;
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array($filterValue));    
    $products = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo json_encode($products);    
?>
```

--------------------------------

### PHP: Add Multiple Records to Database

Source: https://paramquery.com/tutorial/editing

This PHP function, addList, is designed to add multiple records to a database. It iterates through an array of records, calling addSingle for each one, and returns the modified list which includes the ProductID of the newly inserted rows. This is typically used in conjunction with a grid component like ParamQuery.

```PHP
//add multiple records in db.
function addList($addList)
{
    $pdo = getDBH();
    foreach ($addList as &$r)
    {
        $r['ProductID'] = addSingle($pdo, $r);
    }
    return $addList;
}
```
Tokens:
Raw