jQWidgets Forums

Tagged: virtualmode json editable virtual mode jqxgrid grid

This topic contains 6 replies, has 2 voices, and was last updated by susiesadowsky 10 years, 11 months ago.

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

Author

virtualmode and editable Posts
April 18, 2014 at 4:21 am virtualmode and editable #53257

susiesadowsky
Participant

Hi.

I am trying to create a grid that uses json, virtual mode and has an editable checkbox column.

The sample code below (which uses the Northwind database) works except that changes to the checkbox column are not saved. Can anyone help me fix this?

Thanks in advance for your help.
Kindest regards,
Susie

<!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.11.0.min.js”></script>
<script type=”text/javascript” src=”../../jqwidgets/jqx-all.js”></script>
<script type=”text/javascript”>
$(document).ready(function () {
var theme = ‘classic’;

var source =
{
datatype: “json”,
datafields: [
{ name: ‘ProductName’, type: ‘string’},
{ name: ‘Discontinued’, type: ‘bool’ }
],
url: ‘data.php’,
cache: false,
filter: function()
{
// update the grid and send a request to the server.
$(“#jqxgrid”).jqxGrid(‘updatebounddata’, ‘filter’);
},
sort: function()
{
// update the grid and send a request to the server.
$(“#jqxgrid”).jqxGrid(‘updatebounddata’, ‘sort’);
},
root: ‘Rows’,
beforeprocessing: function(data)
{
source.totalrecords = data[0].TotalRows;
}
};
var dataadapter = new $.jqx.dataAdapter(source, {
loadError: function(xhr, status, error)
{
alert(error);
}
}
);

// initialize jqxGrid
$(“#jqxgrid”).jqxGrid(
{
source: dataadapter,
theme: theme,
filterable: true,
sortable: true,
editable: true,
virtualmode: true,
showfilterrow: true,
rendergridrows: function(obj)
{
return obj.data;
},
columns: [
{ text: ‘Product Name’, datafield: ‘ProductName’,editable:false, width: 300 },
{ text: ‘Discontinued’, datafield: ‘Discontinued’, columntype:’checkbox’, width: 100 }
]
});
});
</script>
</head>
<body class=’default’>
<div id=’jqxWidget'”>
<div id=”jqxgrid”></div>
</div>
</body>
</html>

This is my data.php file:

<?php
$hostname = “localhost”;
$database = “northwind”;
$username = “username”;
$password = “password”;
$connect = mysql_connect($hostname, $username, $password) or die(‘Could not connect: ‘ . mysql_error());
mysql_select_db($database, $connect);
$bool = mysql_select_db($database, $connect);
if ($bool === False){print “can’t find $database”;}
$firstvisiblerow = $_GET[‘recordstartindex’];
// get the last visible row.
$lastvisiblerow = $_GET[‘recordendindex’];
$rowscount = $lastvisiblerow – $firstvisiblerow;
$pagesize = $rowscount;
$start = $firstvisiblerow;
$query = “SELECT SQL_CALC_FOUND_ROWS * FROM Products LIMIT $start, $pagesize”;
$result = mysql_query($query) or die(“SQL Error 1: ” . mysql_error());
$sql = “SELECT FOUND_ROWS() AS found_rows;”;
$rows = mysql_query($sql);
$rows = mysql_fetch_assoc($rows);
$total_rows = $rows[‘found_rows’];
$filterquery = “”;

// filter data.
if (isset($_GET[‘filterscount’]))
{
$filterscount = $_GET[‘filterscount’];

if ($filterscount > 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 “NOT_EMPTY”:
case “NOT_NULL”:
$where .= ” ” . $filterdatafield . ” NOT LIKE ‘” . “” .”‘”;
break;
case “EMPTY”:
case “NULL”:
$where .= ” ” . $filterdatafield . ” LIKE ‘” . “” .”‘”;
break;
case “CONTAINS_CASE_SENSITIVE”:
$where .= ” BINARY ” . $filterdatafield . ” LIKE ‘%” . $filtervalue .”%'”;
break;
case “CONTAINS”:
$where .= ” ” . $filterdatafield . ” LIKE ‘%” . $filtervalue .”%'”;
break;
case “DOES_NOT_CONTAIN_CASE_SENSITIVE”:
$where .= ” BINARY ” . $filterdatafield . ” NOT LIKE ‘%” . $filtervalue .”%'”;
break;
case “DOES_NOT_CONTAIN”:
$where .= ” ” . $filterdatafield . ” NOT LIKE ‘%” . $filtervalue .”%'”;
break;
case “EQUAL_CASE_SENSITIVE”:
$where .= ” BINARY ” . $filterdatafield . ” = ‘” . $filtervalue .”‘”;
break;
case “EQUAL”:
$where .= ” ” . $filterdatafield . ” = ‘” . $filtervalue .”‘”;
break;
case “NOT_EQUAL_CASE_SENSITIVE”:
$where .= ” BINARY ” . $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_CASE_SENSITIVE”:
$where .= ” BINARY ” . $filterdatafield . ” LIKE ‘” . $filtervalue .”%'”;
break;
case “STARTS_WITH”:
$where .= ” ” . $filterdatafield . ” LIKE ‘” . $filtervalue .”%'”;
break;
case “ENDS_WITH_CASE_SENSITIVE”:
$where .= ” BINARY ” . $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 Products “.$where;
$filterquery = $query;
$result = mysql_query($query) or die(“SQL Error 1: ” . mysql_error());
$sql = “SELECT FOUND_ROWS() AS found_rows;”;
$rows = mysql_query($sql);
$rows = mysql_fetch_assoc($rows);
$new_total_rows = $rows[‘found_rows’];
$query = “SELECT * FROM Products “.$where.” LIMIT $start, $new_total_rows”;
$total_rows = $new_total_rows;
}
}

if (isset($_GET[‘sortdatafield’]))
{
$sortfield = $_GET[‘sortdatafield’];
$sortorder = $_GET[‘sortorder’];

if ($sortorder != ”)
{
if ($_GET[‘filterscount’] == 0)
{
if ($sortorder == “desc”)
{
$query = “SELECT * FROM Products ORDER BY” . ” ” . $sortfield . ” DESC LIMIT $start, $total_rows”;
}
else if ($sortorder == “asc”)
{
$query = “SELECT * FROM Products ORDER BY” . ” ” . $sortfield . ” ASC LIMIT $start, $total_rows”;
}
}
else
{
if ($sortorder == “desc”)
{
$filterquery .= ” ORDER BY” . ” ” . $sortfield . ” DESC LIMIT $start, $total_rows”;
}
else if ($sortorder == “asc”)
{
$filterquery .= ” ORDER BY” . ” ” . $sortfield . ” ASC LIMIT $start, $total_rows”;
}
$query = $filterquery;
}
}
}

$result = mysql_query($query) or die(“SQL Error 1: ” . mysql_error());
$sql = “SELECT FOUND_ROWS() AS found_rows;”;
$rows = mysql_query($sql);
$rows = mysql_fetch_assoc($rows);
$total_rows = $rows[‘found_rows’];
$Products = null;
// get data and store in a json array
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$Products[] = array(
‘ProductName’ => $row[‘ProductName’],
‘Discontinued’ => $row[‘Discontinued’]
);
}
$data[] = array(
‘TotalRows’ => $total_rows,
‘Rows’ => $Products
);
echo json_encode($data);
?>

April 18, 2014 at 7:50 am virtualmode and editable #53263

Peter Stoev
Keymaster

Hi susiesadowsky,

The code which saves changes is missing. There’s no communication between your client code and server code when a cell value is changed. You should implement that through custom Ajax calls within a function called “updaterow” which you should add to the source object. I suggest you to look at the Help documentation topics about editing.

Best Regards,
Peter Stoev

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

April 18, 2014 at 8:16 pm virtualmode and editable #53296

susiesadowsky
Participant

Hi Peter.

Thanks for your quick response. I looked at the Help documentation topics about editing and looked into implementing a custom Ajax call within the function called “updaterow”. I think this method actually updates the database, which is not whatI want to do. I just want the grid on the users screen to update, but as soon as they refresh or close the window the changes are gone. Is this possible and if so can you point me in the right direction?

All my best,
Susie

April 19, 2014 at 8:24 am virtualmode and editable #53298

Peter Stoev
Keymaster

Hi Susie,

The changes will gone due to the fact that the Grid as a Client Side widget on Page refresh will be recreated and if you do not synchronize the changes with your Database, the data will still be unchanged.

Best Regards,
Peter Stoev

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

April 19, 2014 at 7:47 pm virtualmode and editable #53305

susiesadowsky
Participant

Hi Peter,

Thanks so much for your response.

My website (CustomCollegeRankings.com) contains a feature that allows users to select colleges using the checkbox in the 3rd column and then sort to bring these colleges to the top of the grid so they can be compared easily.

My situation is: I would like to implement virtualmode and I don’t know how to do this and maintain the above mentioned feature.

Do you have any suggestions?

I appreciate your help.

All my best,
Susie

April 20, 2014 at 2:54 pm virtualmode and editable #53315

Peter Stoev
Keymaster

Hi Susie,

We have multiple help topics about Editing in Virtual Mode. A good one is: http://www.jqwidgets.com/jquery-widgets-documentation/documentation/phpintegration/php-server-side-grid-paging-and-sorting.htm

Best Regards,
Peter Stoev

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

April 22, 2014 at 3:57 am virtualmode and editable #53366

susiesadowsky
Participant

Thank you.
Author

Posts

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

You must be logged in to reply to this topic.