jQWidgets Forums

Tagged: jqxgrid, server filtering with editing

This topic contains 4 replies, has 3 voices, and was last updated by lammiu 10 years ago.

Viewing 5 posts - 1 through 5 (of 5 total)

Author

Server Filtering + Editing – jqxGrid Posts

January 10, 2014 at 6:07 pm Server Filtering + Editing – jqxGrid #47714

Participant

Hi,
I am using JQwidgets version 3.1.0
Is it possible to do Server Filtering along with Editing data in jqxGrid ??
I am not able to use both (Server Filtering + Editing) simultaneously.
Please advice.

Best Regards,
Alex

January 10, 2014 at 8:51 pm Server Filtering + Editing – jqxGrid #47719

Peter Stoev

Keymaster

Hi Alex,

Yes, it is possible to use these features. Make sure that you implement correctly the custom Ajax call in your “updaterow” function of the source object.

Example:

            updaterow: function (rowid, rowdata, commit) {
                    // synchronize with the server - send update command
                    var data = "update=true&FirstName=" + rowdata.FirstName + "&LastName=" + rowdata.LastName + "&Title=" + rowdata.Title;
                    data = data + "&Address=" + rowdata.Address + "&City=" + rowdata.City + "&Country=" + rowdata.Country + "&Notes=''";
                    data = data + "&EmployeeID=" + rowdata.EmployeeID;
                    $.ajax({
                        dataType: 'json',
                        url: 'data.php',
                        data: data,
                        success: function (data, status, xhr) {
                            // update command is executed.
                            commit(true);
                        },
                        error: function () {
                            // cancel changes.
                            commit(false);
                        }
                    });
                }

The above code shows how to send an update to data.php through Ajax. The updated row has FirstName, LastName, etc. data fields. On your server side you will have to implement the UPDATE command using the received data from the Client and make your MySQL DB UPDATE call.

Please, do not make duplicate posts: http://www.jqwidgets.com/community/topic/filter-and-edit-data-not-working-together. I have already asked you politely about that, yesterday.

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com/

January 12, 2014 at 4:17 pm Server Filtering + Editing – jqxGrid #47751

AlexenderVanKan

Participant

Hi Peter,
Thank you for your reply.

When I use this updaterow: function, along with Server Filtering code, then it does not work.
Can you inform to me, how to execute both update and filtering command in data.php ??

Sincerely,
Alex

January 13, 2014 at 7:28 am Server Filtering + Editing – jqxGrid #47778

Peter Stoev

Keymaster

Hi Alex,

I have prepared an example with Server Filtering and Editing.

index.php

<!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="../../scripts/jquery-1.10.2.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/jqxgrid.edit.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', type: 'string'},
					 { name: 'ShipAddress', type: 'string'},
					 { name: 'ShipCity', type: 'string'},
					 { name: 'ShipCountry', type: 'string'}
                ],
				type: 'POST',
				id: 'OrderID',
                url: 'data.php',
			    updaterow: function (rowid, rowdata, commit) {
			      // synchronize with the server - send update command
					  var data = {
						update: true,
						ShipName: rowdata.ShipName,
						ShipAddress: rowdata.ShipAddress,
						ShipCity: rowdata.ShipCity,
						ShipCountry: rowdata.ShipCountry,
						OrderID: rowid
					  };
				  
             	
					var request = $.ajax({
						dataType: 'json',
						url: 'data.php',
						type: 'POST',
						data: data				
					});		
					request.done(function( msg ) {
						commit(true);
					});
					 
					request.fail(function( jqXHR, textStatus ) {
						commit(false);
					});
                },
				filter: function()
				{
					// update the grid and send a request to the server.
					$("#jqxgrid").jqxGrid('updatebounddata', 'filter');
				}
            };		
			
			var dataadapter = new $.jqx.dataAdapter(source, {
					loadError: function(xhr, status, error)
					{
						alert(error);
					}
				}
			);
			
            // initialize jqxGrid
            $("#jqxgrid").jqxGrid(
            {		
                source: dataadapter,
                theme: theme,
				editable: true,
				filterable: true,
			    selectionmode: 'singlerow',
                editmode: 'selectedrow',
             	width: 650,
			    columns: [
                      { text: 'Ship Name', datafield: 'ShipName', width: 200 },
                      { text: 'Address', datafield: 'ShipAddress', width: 180 },
                      { text: 'City', datafield: 'ShipCity', width: 100 },
                      { text: 'Country', datafield: 'ShipCountry' }
                  ]
            });
        });
    </script>
</head>
<body class='default'>
    <div id='jqxWidget'">
        <div id="jqxgrid"></div>
    </div>
</body>
</html>

data.php

<?php
	#Include the connect.php file
	include('connect.php');
	#Connect to the database
	//connection String
	$connect = mysql_connect($hostname, $username, $password)
	or die('Could not connect: ' . mysql_error());
	//Select The database
	$bool = mysql_select_db($database, $connect);
	if ($bool === False){
	   print "can't find $database";
	}
	
	$query = "SELECT * FROM Orders";
	if (isset($_POST['update']))
	{	
		// UPDATE COMMAND 
		$update_query = "UPDATE <code>Orders</code> SET <code>ShipName</code>='".mysql_real_escape_string($_POST['ShipName'])."',
		<code>ShipCountry</code>='".mysql_real_escape_string($_POST['ShipCountry'])."',
		<code>ShipAddress</code>='".mysql_real_escape_string($_POST['ShipAddress'])."',
		<code>ShipCity</code>='".mysql_real_escape_string($_POST['ShipCity'])."' WHERE <code>OrderID</code>='".mysql_real_escape_string($_POST['OrderID'])."'";
		 $result = mysql_query($update_query) or die("SQL Error 1: " . mysql_error());
		 echo $result;
		 return;
	}
	// filter data.
	if (isset($_POST['filterscount']))
	{
		$filterscount = $_POST['filterscount'];
		
		if ($filterscount > 0)
		{
			$where = " WHERE (";
			$tmpdatafield = "";
			$tmpfilteroperator = "";
			for ($i=0; $i < $filterscount; $i++)
		    {
				// POST the filter's value.
				$filtervalue = $_POST["filtervalue" . $i];
				// POST the filter's condition.
				$filtercondition = $_POST["filtercondition" . $i];
				// POST the filter's column.
				$filterdatafield = $_POST["filterdatafield" . $i];
				// POST the filter's operator.
				$filteroperator = $_POST["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());
	$orders = array();
	// POST data and store in a json array
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$orders[] = array(
			'OrderID' => $row['OrderID'],
			'OrderDate' => $row['OrderDate'],
			'ShippedDate' => $row['ShippedDate'],
			'ShipName' => $row['ShipName'],
			'ShipAddress' => $row['ShipAddress'],
			'ShipCity' => $row['ShipCity'],
			'ShipCountry' => $row['ShipCountry']
		  );
	}
  
	echo json_encode($orders);
?>

connect.php

<?php
# FileName="connect.php"
$hostname = "localhost";
$database = "northwind";
$username = "root";
$password = "";
?>

Best Regards,
Peter Stoev

jQWidgets Team
http://www.jqwidgets.com

June 24, 2015 at 4:59 am Server Filtering + Editing – jqxGrid #72916

lammiu

Participant

Hi Peter,

I tried the exact example on northwind db, everything goes well except data won’t save to mysql db. on refresh, everything no change. Please update example

Author

Posts

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic.