Click here to Skip to main content
12,403,811 members (68,586 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

8.7K views
1 bookmarked
Posted

Server Side Filtering with jqxGrid using PHP and MySQL

, 23 Mar 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
This post shows how to implement server-side filtering with the jqxGrid widget. The Grid will request data from the server when the user applies a new filter or clears the filtering. The server-side script is going to deliver the data … Continue reading →

This post shows how to implement server-side filtering with the jqxGrid widget. The Grid will request data from the server when the user applies a new filter or clears the filtering. The server-side script is going to deliver the data records in JSON format depending on the filter expression. We will obtain the data from Northwind Database and especially from the Orders table. You can download the Northwind database .sql script here and run it into MySQL to create the database. You can download the Northwind database .sql script here and run it into MySQL to create the database.

The first thing we need to do is create the file we’ll connect with. We’ll call this file connect.php


Now we need to create the file that will run the query and bring the data so our Grid can be populated. We will call the file data.php.

 0)
		{
			$where = " WHERE (";
			$tmpdatafield = "";
			$tmpfilteroperator = "";
			for ($i=0; $i < $filterscount; $i++)
		    {
				// get the filter's value.
				$filtervalue = $_GET["filtervalue" . $i];
				// get the filter's condition.
				$filtercondition = $_GET["filtercondition" . $i];
				// get the filter's column.
				$filterdatafield = $_GET["filterdatafield" . $i];
				// get the filter's operator.
				$filteroperator = $_GET["filteroperator" . $i];

				if ($tmpdatafield == "")
				{
					$tmpdatafield = $filterdatafield;
				}
				else if ($tmpdatafield <> $filterdatafield)
				{
					$where .= ")AND(";
				}
				else if ($tmpdatafield == $filterdatafield)
				{
					if ($tmpfilteroperator == 0)
					{
						$where .= " AND ";
					}
					else $where .= " OR ";
				}

				// build the "WHERE" clause depending on the filter's condition, value and datafield.
				switch($filtercondition)
				{
					case "CONTAINS":
						$where .= " " . $filterdatafield . " LIKE '%" . $filtervalue ."%'";
						break;
					case "DOES_NOT_CONTAIN":
						$where .= " " . $filterdatafield . " NOT LIKE '%" . $filtervalue ."%'";
						break;
					case "EQUAL":
						$where .= " " . $filterdatafield . " = '" . $filtervalue ."'";
						break;
					case "NOT_EQUAL":
						$where .= " " . $filterdatafield . " <> '" . $filtervalue ."'";
						break;
					case "GREATER_THAN":
						$where .= " " . $filterdatafield . " > '" . $filtervalue ."'";
						break;
					case "LESS_THAN":
						$where .= " " . $filterdatafield . " < '" . $filtervalue ."'";
						break;
					case "GREATER_THAN_OR_EQUAL":
						$where .= " " . $filterdatafield . " >= '" . $filtervalue ."'";
						break;
					case "LESS_THAN_OR_EQUAL":
						$where .= " " . $filterdatafield . " <= '" . $filtervalue ."'";
						break;
					case "STARTS_WITH":
						$where .= " " . $filterdatafield . " LIKE '" . $filtervalue ."%'";
						break;
					case "ENDS_WITH":
						$where .= " " . $filterdatafield . " LIKE '%" . $filtervalue ."'";
						break;
				}

				if ($i == $filterscount - 1)
				{
					$where .= ")";
				}

				$tmpfilteroperator = $filteroperator;
				$tmpdatafield = $filterdatafield;
			}
			// build the query.
			$query = "SELECT * FROM orders" . $where;
		}
	}

	$result = mysql_query($query) or die("SQL Error 1: " . mysql_error());

	// get data and store in a json array
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$orders[] = array(
			'OrderDate' => $row['OrderDate'],
			'ShippedDate' => $row['ShippedDate'],
			'ShipName' => $row['ShipName'],
			'ShipAddress' => $row['ShipAddress'],
			'ShipCity' => $row['ShipCity'],
			'ShipCountry' => $row['ShipCountry']
		  );
	}

	echo json_encode($orders);
?>

In the above code, we create a query depending on the filter expression. The Grid sends the following data to the server:

  • sortdatafield – the sort column’s datafield.
  • sortorder – the sort order – ‘asc’, ‘desc’ or ”
  • pagenum – the current page’s number when the paging feature is enabled.
  • pagesize – the page’s size which represents the number of rows displayed in the view.
  • groupscount – the number of groups in the Grid
  • group – the group’s name. The group’s name for the first group is ‘group0', for the second group is ‘group1' and so on.
  • filterscount – the number of filters applied to the Grid
  • filtervalue – the filter’s value. The filtervalue name for the first filter is “filtervalue0?, for the second filter is “filtervalue1? and so on.
  • filtercondition – the filter’s condition. The condition can be any of these: “CONTAINS”, “DOES_NOT_CONTAIN”, “EQUAL”, “EQUAL_CASE_SENSITIVE”,
    NOT_EQUAL”,”GREATER_THAN”, “GREATER_THAN_OR_EQUAL”, “LESS_THAN”, “LESS_THAN_OR_EQUAL”, “STARTS_WITH”,
    “STARTS_WITH_CASE_SENSITIVE”, “ENDS_WITH”, “ENDS_WITH_CASE_SENSITIVE”, “NULL”, “NOT_NULL”, “EMPTY”, “NOT_EMPTY”
  • filterdatafield – the filter column’s datafield
  • filteroperator – the filter’s operator – 0 for “AND” and 1 for “OR”

In the data.php, we check the value of the filterscount property. If there’s a filter expression, the filterscount value will be greater than 0. Then we iterate through
all of the applied filters.

The following code gets the value of the filter with index i.

$filtervalue = $_GET["filtervalue" . $i];

The following code gets the condition(‘Contains’, ‘Starts With’, etc.) of the current filter.

$filtercondition = $_GET["filtercondition" . $i];

The current filter’s column is stored in the ‘filterdatafield’ property.

$filterdatafield = $_GET["filterdatafield" . $i];

The filter operator specifies the relation of the current filter with the filters from the same column. Its value can be 0 for “AND” and 1 for “OR”.

For example, if you have multiple filters applied to the same column the filter expression could mean the following ( filter all records in the ‘First Name’ column that start with ‘a’ and end with ‘b’). The ‘and’ keyword is the filter operator.

$filteroperator = $_GET["filteroperator" . $i];

Depending on the value of the $filtercondition property, we build the query’s WHERE clause.

The last step is to initialize the Grid. In order to enable the filtering, we need to reference the jqxgrid.filter.js and to set the Grid’s filterable property to true. In the source object’s initialization we override the built-in filtering by setting the filter member to a custom function. That function
is called when the user changes the filtering expression by adding or removing a filter. In the filter function’s implementation, we call the Grid’s ‘updatebounddata’ method which makes a request to the server and passes the filtering parameters.

<!DOCTYPE html>
<html lang="en">
<head>
    <link rel="stylesheet" href="../jqwidgets/styles/jqx.base.css" type="text/css" />
    <link rel="stylesheet" href="../jqwidgets/styles/jqx.classic.css" type="text/css" />
    <script type="text/javascript" src="../jquery-1.7.1.min.js"></script>
    <script type="text/javascript" src="../jqwidgets/jqxcore.js"></script>
    <script type="text/javascript" src="../jqwidgets/jqxbuttons.js"></script>
    <script type="text/javascript" src="../jqwidgets/jqxscrollbar.js"></script>
    <script type="text/javascript" src="../jqwidgets/jqxmenu.js"></script>
    <script type="text/javascript" src="../jqwidgets/jqxgrid.js"></script>
    <script type="text/javascript" src="../jqwidgets/jqxgrid.selection.js"></script>
	<script type="text/javascript" src="../jqwidgets/jqxgrid.filter.js"></script>
    <script type="text/javascript" src="../jqwidgets/jqxdata.js"></script>
	<script type="text/javascript" src="../jqwidgets/jqxlistbox.js"></script>
	<script type="text/javascript" src="../jqwidgets/jqxdropdownlist.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            // prepare the data
            var theme = 'classic';

            var source =
            {
                datatype: "json",
                datafields: [
				    { name: 'ShippedDate', type: 'date' },
				    { name: 'ShipName' },
				    { name: 'ShipAddress' },
				    { name: 'ShipCity' },
				    { name: 'ShipCountry' }
                ],
                url: 'data.php',
                filter: function () {
                    // update the grid and send a request to the server.
                    $("#jqxgrid").jqxGrid('updatebounddata');
                }
            };

            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {
                source: source,
                theme: theme,
                filterable: true,
                columns: [
                      { text: 'Shipped Date', datafield: 'ShippedDate', cellsformat: 'd', width: 200 },
                      { text: 'Ship Name', datafield: 'ShipName', width: 200 },
                      { text: 'Address', datafield: 'ShipAddress', width: 180 },
                      { text: 'City', datafield: 'ShipCity', width: 100 },
                      { text: 'Country', datafield: 'ShipCountry', width: 140 }
                  ]
            });
        });
    </script>
</head>
<body class='default'>
    <div id="jqxWidget">
        <div id="jqxgrid"></div>
    </div>
</body>
</html>

jquery-grid-server-side-filtering

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

jqwidgets
jQWidgets
United States United States
jQWidgets specializes in the development of platform independent and cross-browser compatible presentation layer components for building modern web-based applications for PC, Touch and Mobile. Our product provides developers with the essential set of User Interface widgets needed to streamline their development and reduce project costs.
Our goal is to help our customers deliver better web-based applications that can be accessed through any device and are pleasure to use.
Group type: Organisation

13 members


You may also be interested in...

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160721.1 | Last Updated 23 Mar 2012
Article Copyright 2012 by jqwidgets
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid