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.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author

  • dinu1389
    Participant

    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 = “UPDATE datatest SET

    name='”.$_GET[‘name’].”‘,
    manufacturer='”.$_GET[‘manufacturer’].”‘,
    model='”.$_GET[‘model’].”‘,
    categoryid='”.$_GET[‘categoryid’].”‘,
    subcategoryid='”.$_GET[‘subcategoryid’].”‘,
    price='”.$_GET[‘price’].”‘,
    WHERE id='”.$_GET[‘id’].”‘”;
    $result = mysql_query($update_query) or die(“SQL Error 1: ” . mysql_error());
    echo $result;
    }
    else
    {

    if (isset($_GET[‘delete’]))
    {
    // UPDATE COMMAND DELETE FROM datatest WHERE 1
    $update_query = “DELETE FROM datatest WHERE id='”.$_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() 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 “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() AS found_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>


    dinu1389
    Participant

    shit i removed comma after price='”.$_GET[‘price’].”‘ . It Works!!

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

You must be logged in to reply to this topic.