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

From NSB App Studio
Jump to navigation Jump to search
No edit summary
 
(5 intermediate revisions by the same user not shown)
Line 1: Line 1:
== PHP File Settings ==
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 ==
<pre>
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;
 
 
 
</pre>
 
 
 
 
== PHP File Settings (mysql4.php)==
 
File saved into :
http://localhost/Kasse/mysql4.php
 
 
<pre>
<pre>


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


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


$update_query = "UPDATE kasse_sql.waren_gruppen SET waren_gruppen.Firma='".$_GET['Firma']."',
$update_query = "UPDATE kasse_sql.waren_gruppen SET waren_gruppen.SpkNumber='".$_GET['Number']."',
waren_gruppen.Text='".$_GET['Text']."' WHERE waren_gruppen.Firma='".$_GET['Firma']."'";
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());
$result = mysql_query($update_query) or die("SQL Error 1: " . mysql_error());
         echo $result;
         echo $result;
Line 40: Line 68:
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$employees[] = array(
$employees[] = array(
'Firma' => $row['Firma'],
'SpkNumber' => $row['SpkNumber'],
'Text' => $row['Text']
'Text' => $row['Text'],
                        'ZeitZone' => $row['ZeitZone']
  );
  );
}
}
Line 52: Line 81:


== Settings in NSB/APPStudio ==
== Settings in NSB/APPStudio ==
* Start NSB/AppStudio
* Create a new Project (like IPAD)
* Add a JqxGrid in new project
* Create a new JavaScript Area
<pRE>
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
</pre>
==Result==
[[File:Jqxgrid direkt mysql edit.jpg]]

Latest revision as of 23:47, 26 December 2014

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


Result