jQuery UI Widgets › Forums › Grid › how to update Decimal column value in database
This topic contains 1 reply, has 1 voice, and was last updated by dinu1389 10 years, 5 months ago.
-
Author
-
i have “price” column in my table. datatype: decimal(10,2)
i tried cellsformat c2,f2 to update but it doesnot work.please help me out.
manageproductsdata.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”;
}
if (isset($_GET[‘update’]))
{
// UPDATE COMMAND
$update_query = “UPDATEdatatest
SETname
='”.$_GET[‘name’].”‘,
manufacturer
='”.$_GET[‘manufacturer’].”‘,
model
='”.$_GET[‘model’].”‘,
categoryid
='”.$_GET[‘categoryid’].”‘,
subcategoryid
='”.$_GET[‘subcategoryid’].”‘,
price
='”.$_GET[‘price’].”‘,
WHEREid
='”.$_GET[‘id’].”‘”;
$result = mysql_query($update_query) or die(“SQL Error 1: ” . mysql_error());
echo $result;
}
else
{if (isset($_GET[‘delete’]))
{
// UPDATE COMMAND DELETE FROMdatatest
WHERE 1
$update_query = “DELETE FROMdatatest
WHEREid
='”.$_GET[‘id’].”‘”;
$result = mysql_query($update_query) or die(“SQL Error 1: ” . mysql_error());
echo $result;
}
else{$pagenum = $_GET[‘pagenum’];
$pagesize = $_GET[‘pagesize’];
$start = $pagenum * $pagesize;
$query = “SELECT SQL_CALC_FOUND_ROWS * FROM datatest LIMIT $start, $pagesize”;
$result = mysql_query($query) or die(“SQL Error 1: ” . mysql_error());
$sql = “SELECT FOUND_ROWS() ASfound_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 “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 datatest “.$where;
$filterquery = $query;
$result = mysql_query($query) or die(“SQL Error 1: ” . mysql_error());
$sql = “SELECT FOUND_ROWS() ASfound_rows
;”;
$rows = mysql_query($sql);
$rows = mysql_fetch_assoc($rows);
$new_total_rows = $rows[‘found_rows’];
$query = “SELECT * FROM datatest “.$where.” LIMIT $start, $pagesize”;
$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 datatest ORDER BY” . ” ” . $sortfield . ” DESC LIMIT $start, $pagesize”;
}
else if ($sortorder == “asc”)
{
$query = “SELECT * FROM datatest ORDER BY” . ” ” . $sortfield . ” ASC LIMIT $start, $pagesize”;
}
}
else
{
if ($sortorder == “desc”)
{
$filterquery .= ” ORDER BY” . ” ” . $sortfield . ” DESC LIMIT $start, $pagesize”;
}
else if ($sortorder == “asc”)
{
$filterquery .= ” ORDER BY” . ” ” . $sortfield . ” ASC LIMIT $start, $pagesize”;
}
$query = $filterquery;
}
}
}$result = mysql_query($query) or die(“SQL Error 1: ” . mysql_error());
$orders = null;
// get data and store in a json array
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$orders[] = array(
‘id’=>$row[‘id’],
‘name’=>$row[‘name’],
‘manufacturer’=>$row[‘manufacturer’],
‘model’=>$row[‘model’],
‘categoryid’=>$row[‘categoryid’],
‘subcategoryid’=>$row[‘subcategoryid’],
‘price’=>$row[‘price’]);
}
$data[] = array(
‘TotalRows’ => $total_rows,
‘Rows’ => $orders
);echo json_encode($data);
}
}
?>
manageproducts.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.sort.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/jqxgrid.pager.js”></script>
<script type=”text/javascript” src=”./jqwidgets/jqxdropdownlist.js”></script>
<script type=”text/javascript” src=”./jqwidgets/jqxgrid.edit.js”></script><script type=”text/javascript”>
$(document).ready(function () {
// prepare the data
var theme = ‘classic’;var source =
{
datatype: “json”,
datafields: [
{ name: ‘id’, type: ‘int’},
{ name: ‘name’, type: ‘string’},
{ name: ‘manufacturer’, type: ‘string’},
{ name: ‘model’, type: ‘string’},
{ name: ‘categoryid’, type: ‘int’},
{ name: ‘subcategoryid’, type: ‘int’},
{ name: ‘price’/*, type:’number’*/},
{ name: ‘order_status_id’, type: ‘int’}
/* { name: ‘ShippedDate’, type: ‘date’},
{ name: ‘ShipName’, type: ‘string’},
{ name: ‘ShipAddress’, type: ‘string’},
{ name: ‘ShipCity’, type: ‘string’},
{ name: ‘ShipCountry’, type: ‘string’}*/
],
id: ‘id’,
url: ‘manageproductsdata.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)
{
if (data != null)
{
source.totalrecords = data[0].TotalRows;
}
},
updaterow: function (rowid, rowdata, commit) {
// synchronize with the server – send update command
var data = “update=true&name=” + rowdata.name;
data = data+”&manufacturer=” + rowdata.manufacturer;
data = data+”&model=” + rowdata.model;
data = data+”&categoryid=” + rowdata.categoryid;
data = data+”&subcategoryid=” + rowdata.subcategoryid;
data = data+”&price=” + rowdata.price;
data = data + “&id=” + rowdata.id;
$.ajax({
dataType: ‘json’,
url: ‘manageproductsdata.php’,
data: data,
success: function (data, status, xhr) {
// update command is executed.
commit(true);
},
error: function () {
// cancel changes.
commit(false);
}
});
},
deleterow: function (rowid , commit ) {
var data = “delete=true” ;
data = data + “&id=” + rowid;
$.ajax({
dataType: ‘json’,
url: ‘manageproductsdata.php’,
data: data,
success: function (data, status, xhr) {
// update command is executed.
commit(true);
},
error: function () {
// cancel changes.
commit(false);
}
});
}
};
var dataadapter = new $.jqx.dataAdapter(source, {
loadError: function(xhr, status, error)
{
alert(error);
}
}
);// initialize jqxGrid
$(“#jqxgrid”).jqxGrid(
{
source: dataadapter,
theme: theme,
width:”100%”,
editable: true,
filterable: true,
sortable: true,
autoheight: true,
pageable: true,
virtualmode: true,
showtoolbar: true,
rendertoolbar: function (toolbar) {
var me = this;
var container = $(“<div style=’margin: 5px;’></div>”);
toolbar.append(container);container.append(‘<input style=”margin-left: 5px;” id=”deleterowbutton” type=”button” value=”Delete Selected Row” />’);
$(“#deleterowbutton”).jqxButton();
// delete row.
$(“#deleterowbutton”).on(‘click’, function () {
var selectedrowindex = $(“#jqxgrid”).jqxGrid(‘getselectedrowindex’);
var rowscount = $(“#jqxgrid”).jqxGrid(‘getdatainformation’).rowscount;
if (selectedrowindex >= 0 && selectedrowindex < rowscount) {
var id = $(“#jqxgrid”).jqxGrid(‘getrowid’, selectedrowindex);
var commit = $(“#jqxgrid”).jqxGrid(‘deleterow’, id);
}
});
},
rendergridrows: function(obj)
{
return obj.data;
},
columns: [
{ text: ‘Product ID’, datafield: ‘id’, width: 70 },
{ text: ‘Name’, datafield: ‘name’, width: 100 },
{ text: ‘Manufacturer’, datafield: ‘manufacturer’, width: 100 },
{ text: ‘Model’, datafield: ‘model’, width: 100 },
{ text: ‘Catgeory’, datafield: ‘categoryid’, width: 50 },
{ text: ‘SubCatgeory’, datafield: ‘subcategoryid’, width: 50 },
{ text: ‘Price’, datafield: ‘price’, width: 100 }
/* { text: ‘Available’, datafield: ‘available’, columntype: ‘checkbox’, filtertype: ‘bool’, width: 67 }*/
/* { text: ‘Shipped Date’, datafield: ‘ShippedDate’, cellsformat: ‘yyyy-MM-dd’, 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>shit i removed comma after
price
='”.$_GET[‘price’].”‘ . It Works!! -
AuthorPosts
You must be logged in to reply to this topic.