JqxGrid Server Side Editing / Update using PHP and MySQL (columntype:dropdownlist + using createeditor)

From NSB App Studio
Jump to navigation Jump to search

My target app is, that the customer must choose and define your "ZeitZone" yourself and save automatically the new values in mysql-tables.

My MySQL Sturucture

CREATE TABLE `waren_gruppen` (
	`Number` INT(3) NOT NULL,
	`Text` VARCHAR(50) NULL DEFAULT NULL,
	`ZeitZone` INT(2) NULL DEFAULT '0',
	PRIMARY KEY (`Firma`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
ROW_FORMAT=COMPACT;





PHP File Settings (mysql4.php)

File saved into : http://localhost/Kasse/mysql4.php



<?php
header('Access-Control-Allow-Origin: *');
 
$host = "localhost";
$databasename = "kasse_sql";
$username ="root";
$password = "MY_MYSQL_PASSWORT";

// If You want, you can send PASS, QUERY as POST and parser later here in this php
// $x = $_GET['t'];
// $tmp = explode(";",$x);
// $password = $tmp[0];
// $query = $tmp[1] ;
// $filename = $tmp[2];	



$con = mysql_connect($host,$username,$password) or die(mysql_error());
mysql_query('SET CHARACTER SET utf8');
mysql_select_db($databasename) or die(mysql_error()); 
 
$query = "Select * from waren_gruppen;";

if (isset($_GET['update']))
{
	// UPDATE COMMAND in my MySQL
	// UPDATE `waren_gruppen` SET Text ="Deneme" where SpkNummer= 11

	$update_query = "UPDATE kasse_sql.waren_gruppen SET waren_gruppen.SpkNumber='".$_GET['Number']."',
	waren_gruppen.Text='".$_GET['Text']."',  waren_gruppen.ZeitZone='".$_GET['ZeitZone']."'     WHERE waren_gruppen.SpkNumber='".$_GET['Number']."'";
	 $result = mysql_query($update_query) or die("SQL Error 1: " . mysql_error());
         echo $result;
}
else
{
    // SELECT COMMAND
	$result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
	while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
		$employees[] = array(
			'SpkNumber' => $row['SpkNumber'],
			'Text' => $row['Text'],
                        'ZeitZone' => $row['ZeitZone']
		  );
	}
	 
	echo json_encode($employees);
}


Settings in NSB/APPStudio

  • Start NSB/AppStudio
  • Create a new Project (like IPAD)
  • Add a JqxGrid in new project
  • Create a new JavaScript Area


Function gridready()
  console.log("Grid load complete")
End Function

JavaScript

 $(document).ready(function () {
            // prepare the data
            var data = {};
            var theme = 'classic';
            var source =
            {
                datatype: "json",
                datafields: [
					 { name: 'Firma' },
					 { name: 'Text' },
                                         { name: 'ZeitZone'}
                ],
                id: 'Firma',
                url: 'http://localhost/Kasse/mysql4.php',
                updaterow: function (rowid, rowdata, commit) {
                    // synchronize with the server - send update command
                    
                   var data = "update=true&Firma=" + rowdata.Firma + "&Text=" + rowdata.Text + "&ZeitZone=" + rowdata.ZeitZone ;


                    $.ajax({
                        dataType: 'json',
                        url: 'http://localhost/Kasse/mysql4.php',
                        data: data,
                        success: function (data, status, xhr) {
                            // update command is executed.
                            commit(true);
                            //alert ("Succes");
                            Label1.textContent = "ROW ID: " + rowid + " STATUS: Saved -> TRUE";
                        },
                        error: function () {
                            // cancel changes.
                            commit(false);
                            Label1.textContent = "ROW ID: " + rowid + " STATUS: Saved -> FALSE";
                        }
                    });
                }
            };
            

            var dataAdapter = new $.jqx.dataAdapter(source);
            var editrow = -1;
         
            
            
            
            // initialize jqxGrid
            $("#Grid1").jqxGrid(
            {
                width: 700,
                height: 350,
                selectionmode: 'singlecell',
                source: source,
                theme: theme,
                editable: true,
                columns:
                [
                    { text: 'Firma', editable: false, datafield: 'Firma', width: 100 },
                    { text: 'Text', datafield: 'Text', width: 140 },
                    { text: 'ZeitZone', datafield: 'ZeitZone', width: 150, columntype: 'dropdownlist',

 createeditor: function (row, column, editor) {
                            // assign a new data source to the dropdownlist.
                            var list = ['0', '1', '2'];
                            editor.jqxDropDownList({ autoDropDownHeight: true, source: list });
                        },
                        // update the editor's value before saving it.
                        cellvaluechanging: function (row, column, columntype, oldvalue, newvalue) {
                            // return the old value, if the new value is empty.
                            if (newvalue == "") return oldvalue;
                        }



                    }
                ]
            });
        });  
         
$('#Grid1 .jqx-widget-content').css('font-size','20px');
         
End JavaScript