jQWidgets Forums

Tagged: jquery grid, php grid

This topic contains 1 reply, has 2 voices, and was last updated by admin 9 years, 7 months ago.

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

Author

grid editing for php mysql not save Posts
September 8, 2015 at 3:07 am grid editing for php mysql not save #75618

amylam
Participant

I have been trying very hard to follow exactly the demo on datagrid for php and mysql. So far view works fine, but editing cannot save data to db.
Please help. Already sent email to Peter
<?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”;
}

$query = “SELECT * FROM dutyroster”;
if (isset($_GET[‘update’]))
{

// UPDATE COMMAND
$update_query = “UPDATE dutyroster SET PEOPLE_ID='”.$_GET[‘PEOPLE_ID’].”‘,
CPID='”.$_GET[‘CPID’].”‘,
LAST_NAME='”.$_GET[‘LAST_NAME’].”‘,
FIRST_NAME='”.$_GET[‘FIRST_NAME’].”‘,
CP='”.$_GET[‘CP’].”‘,
Batch='”.$_GET[‘Batch’].”‘,
CSRP='”.$_GET[‘CSRP’].”‘,
CLUSTER='”.$_GET[‘CLUSTER’].”‘,
WARD='”.$_GET[‘WARD’].”‘,
SPECIALTY='”.$_GET[‘SPECIALTY’].”‘,
WARDSTART='”.$_GET[‘WARDSTART’].”‘,
WARDEND='”.$_GET[‘WARDEND’].”‘,
FitTest='”.$_GET[‘FitTest’].”‘,
FIRSTDAY='”.$_GET[‘FIRSTDAY’].”‘,
MON='”.$_GET[‘MON’].”‘,
TUE='”.$_GET[‘TUE’].”‘,
WED='”.$_GET[‘WED’].”‘,
THUR='”.$_GET[‘THUR’].”‘,
FRI='”.$_GET[‘FRI’].”‘,
SAT='”.$_GET[‘SAT’].”‘,
SUN='”.$_GET[‘SUN’].”‘,
STATUS='”.$_GET[‘STATUS’].”‘,
CONTROL='”.$_GET[‘CONTROL’].”‘,
HID='”.$_GET[‘HID’].”‘,
REMARKS='”.$_GET[‘REMARKS’].”‘,
TIMESTAMP='”.$_GET[‘TIMESTAMP’].”‘ WHERE ID='”.$_GET[‘ID’].”‘”;

$result = mysql_query($update_query) or die(“SQL Error 1: ” . mysql_error());

echo $result;

}
else
{

$result = mysql_query($query) or die(“SQL Error 1: ” . mysql_error());
// get data and store in a json array
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$dutyroster[] = array(
‘ID’ => $row[‘ID’],
‘PEOPLE_ID’ => $row[‘PEOPLE_ID’],
‘CPID’ => $row[‘CPID’],
‘LAST_NAME’ => $row[‘LAST_NAME’],
‘FIRST_NAME’ => $row[‘FIRST_NAME’],
‘CP’ => $row[‘CP’],
‘Batch’ => $row[‘Batch’],
‘CSRP’ => $row[‘CSRP’],
‘CLUSTER’ => $row[‘CLUSTER’],
‘WARD’ => $row[‘WARD’],
‘SPECIALTY’ => $row[‘SPECIALTY’],
‘WARDSTART’ => $row[‘WARDSTART’],
‘WARDEND’ => $row[‘WARDEND’],
‘FitTest’ => $row[‘FitTest’],
‘FIRSTDAY’ => $row[‘FIRSTDAY’],
‘MON’ => $row[‘MON’],
‘TUE’ => $row[‘TUE’],
‘WED’ => $row[‘WED’],
‘THUR’ => $row[‘THUR’],
‘FRI’ => $row[‘FRI’],
‘SAT’ => $row[‘SAT’],
‘SUN’ => $row[‘SUN’],
‘STATUS’ => $row[‘STATUS’],
‘CONTROL’ => $row[‘CONTROL’],
‘HID’ => $row[‘HID’],
‘REMARKS’ => $row[‘REMARKS’],
‘TIMESTAMP’ => $row[‘TIMESTAMP’]
);
}

echo json_encode($dutyroster);

}

?>

<!DOCTYPE html>
<html lang=”en”>
<head>
<h3>
Mahara ›
ePorfolio › <h3>
<h2>DUTY ROSTER Maintenance</h2>
<link rel=”stylesheet” href=”jqwidgets/styles/jqx.base.css” type=”text/css” />
<link rel=”stylesheet” href=”jqwidgets/styles/jqx.office.css” type=”text/css” />
<script type=”text/javascript” src=”scripts/jquery-1.11.1.min.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxcore.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxdata.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/jqxlistbox.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxdropdownlist.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxnumberinput.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.filter.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.sort.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.pager.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.selection.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.edit.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.columnsresize.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxpanel.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxcalendar.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxradiobutton.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxdatetimeinput.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxcheckbox.js”></script>
<script type=”text/javascript” src=”jqwidgets/globalization/globalize.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxdata.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxdata.export.js”></script>
<script type=”text/javascript” src=”jqwidgets/jqxgrid.export.js”></script>
<script type=”text/javascript” src=”scripts/demos.js”></script>
<?php
define(‘INTERNAL’, 1);
define(‘PUBLIC’, 1);
define(‘MENUITEM’, ”);
define(‘HOME’, 1);
require(‘../../init.php’);
include “../views/connection.php”;

// Check for whether the user is logged in, before processing the page. After
// this, we can guarantee whether the user is logged in or not for this page.
if ($USER->is_logged_in()) {

$userid = $USER->get(‘id’);

$sql= “call checkGroupInstructor($userid)”;
$query = mysql_query($sql) or die(mysql_error());

$row = mysql_fetch_array($query);
$usercount = $row[0];
//echo “user count is “.$usercount.”<br>”;

if ($usercount >= 1 ) {

?>
<script type=”text/javascript” src=”generatedata.js”></script>
<script type=”text/javascript”>
$(document).ready(function () {
// prepare the data
var theme = ‘office’;

var source =
{
datatype: “json”,
datafields: [
{ name: ‘ID’, type: ‘int’},
{ name: ‘PEOPLE_ID’, type: ‘string’},
{ name: ‘CPID’, type: ‘string’},
{ name: ‘LAST_NAME’, type: ‘string’},
{ name: ‘FIRST_NAME’, type: ‘string’},
{ name: ‘CP’, type: ‘string’},
{ name: ‘Batch’, type: ‘int’},
{ name: ‘CSRP’, type: ‘string’},
{ name: ‘CLUSTER’, type: ‘string’},
{ name: ‘WARD’, type: ‘string’},
{ name: ‘SPECIALTY’, type: ‘string’},
{ name: ‘WARDSTART’, type: ‘date’},
{ name: ‘WARDEND’, type: ‘date’},
{ name: ‘FitTest’, type: ‘string’},
{ name: ‘FIRSTDAY’, type: ‘date’},
{ name: ‘MON’, type: ‘string’},
{ name: ‘TUE’, type: ‘string’},
{ name: ‘WED’, type: ‘string’},
{ name: ‘THUR’, type: ‘string’},
{ name: ‘FRI’, type: ‘string’},
{ name: ‘SAT’, type: ‘string’},
{ name: ‘SUN’, type: ‘string’},
{ name: ‘STATUS’, type: ‘int’},
{ name: ‘CONTROL’, type: ‘int’},
{ name: ‘HID’, type: ‘int’},
{ name: ‘REMARKS’, type: ‘string’},
{ name: ‘TIMESTAMP’, type: ‘date’}
],
id: ‘ID’,
url: ‘dutydataedit.php’,
updaterow: function (rowid, rowdata, commit) {
// synchronize with the server – send update command
var data = “update=true&PEOPLE_ID=” + rowdata.PEOPLE_ID + “&CPID=” + rowdata.CPID + “&LAST_NAME=” + rowdata.LAST_NAME + “&FIRST_NAME=” + rowdata.FIRST_NAME;
data = data + “&CP=” + rowdata.CP + “&Batch=” + rowdata.Batch + “&CSRP=” + rowdata.CSRP + “&CLUSTER=” + rowdata.CLUSTER + “&WARD=” + rowdata.WARD + “&SPECIALTY’=” + rowdata.SPECIALTY;
data = data + “&WARDSTART=” + rowdata.WARDSTART + “&WARDEND=” + rowdata.WARDEND + “&FitTest=” + rowdata.FitTest + “&FIRSTDAY=” + rowdata.FIRSTDAY + “&MON=” + rowdata.MON + “&TUE’=” + rowdata.TUE;
data = data + “&WED=” + rowdata.WED + “&THUR=” + rowdata.THUR + “&FRI=” + rowdata.FRI + “&SAT=” + rowdata.SAT + “&SUN=” + rowdata.SUN + “&STATUS’=” + rowdata.STATUS;
data = data + “&CONTROL=” + rowdata.CONTROL + “&HID=” + rowdata.HID + “&REMARKS=” + rowdata.REMARKS;
data = data + “&ID=” + rowdata.ID;

$.ajax({

dataType: ‘json’,
url: ‘dutydataedit.php’,
data: data,
success: function (data, status, xhr) {
// update command is executed.
commit(true);
},

error: function () {
// cancel changes.
commit(false);
}
});
}
};

// initialize jqxGrid
$(“#jqxgrid”).jqxGrid(
{
width: 1800,
height: 800,
source: source,
theme: theme,
altrows: true,
selectionmode: ‘singlecell’,
//sorting: true,
editable: true,
columns: [
{ text: ‘ID’, editable: false, filtertype: ‘number’, datafield: ‘ID’, width: 60 },
{ text: ‘PEOPLE_ID’, editable: false, datafield: ‘PEOPLE_ID’, pinned: true, width: 80 },
{ text: ‘CPID’, filtertype: ‘checkedlist’, datafield: ‘CPID’, width: 100 },
{ text: ‘LAST_NA’, editable: false, datafield: ‘LAST_NAME’, pinned: true, width: 60 },
{ text: ‘FIRST_NAME’, editable: false, datafield: ‘FIRST_NAME’, pinned: true, width: 100 },
{ text: ‘CP’, editable: false, datafield: ‘CP’, filtertype: ‘checkedlist’, width: 40 },
{ text: ‘Batch’, datafield: ‘Batch’, filtertype: ‘checkedlist’, width: 50 },
{ text: ‘CSRP’, datafield: ‘CSRP’, filtertype: ‘checkedlist’, width: 80 },
{ text: ‘CLUSTER’, editable: false, filtertype: ‘checkedlist’, datafield: ‘CLUSTER’, width: 80 },
{ text: ‘WARD’, filtertype: ‘checkedlist’, datafield: ‘WARD’, width: 60 },
{ text: ‘SPECIALTY’, filtertype: ‘checkedlist’, datafield: ‘SPECIALTY’, width: 180 },
{ text: ‘WARDSTART’, datafield: ‘WARDSTART’, width: 80, cellsalign: ‘right’, cellsformat: ‘d’ },
{ text: ‘WARDEND’, datafield: ‘WARDEND’, width: 80, cellsalign: ‘right’, cellsformat: ‘d’ },
{ text: ‘FitTest’, datafield: ‘FitTest’, width: 60 },
{ text: ‘FIRSTDAY’, datafield: ‘FIRSTDAY’, width: 80, cellsalign: ‘right’, cellsformat: ‘d’},
{ text: ‘MON’, datafield: ‘MON’, filtertype: ‘checkedlist’, width: 40},
{ text: ‘TUE’, datafield: ‘TUE’, filtertype: ‘checkedlist’, width: 40 },
{ text: ‘WED’, datafield: ‘WED’, filtertype: ‘checkedlist’, width: 40 },
{ text: ‘THUR’, datafield: ‘THUR’, filtertype: ‘checkedlist’, width: 40 },
{ text: ‘FRI’, datafield: ‘FRI’, filtertype: ‘checkedlist’, width: 40},
{ text: ‘SAT’, datafield: ‘SAT’, filtertype: ‘checkedlist’ , width: 40},
{ text: ‘SUN’, datafield: ‘SUN’, filtertype: ‘checkedlist’, width: 40 },
{ text: ‘STATUS’, datafield: ‘STATUS’, width: 80 },
{ text: ‘CONTROL’, datafield: ‘CONTROL’, width: 80 },
{ text: ‘HID’, datafield: ‘HID’, width: 40 },
{ text: ‘REMARKS’, datafield: ‘REMARKS’, width: 100 },
{ text: ‘TIMESTAMP’, datafield: ‘TIMESTAMP’, width: 120, cellsalign: ‘right’, cellsformat: ‘S’ }
]
});

$(“#excelExport”).jqxButton();
$(“#xmlExport”).jqxButton();
$(“#csvExport”).jqxButton();
$(“#pdfExport”).jqxButton();

$(“#excelExport”).click(function () {
$(“#jqxgrid”).jqxGrid(‘exportdata’, ‘xls’, ‘jqxGrid’);
});
$(“#xmlExport”).click(function () {
$(“#jqxgrid”).jqxGrid(‘exportdata’, ‘xml’, ‘jqxGrid’);
});
$(“#csvExport”).click(function () {
$(“#jqxgrid”).jqxGrid(‘exportdata’, ‘csv’, ‘jqxGrid’);
});
$(“#pdfExport”).click(function () {
$(“#jqxgrid”).jqxGrid(‘exportdata’, ‘pdf’, ‘jqxGrid’);
});
});
</script>
</head>
<body class=’default’>
<div id=’jqxWidget’ style=”font-size: 8px; font-family: Verdana; float: left;”>
<div id=”jqxgrid”></div>
<div style=’margin-top: 20px;’>
<div style=’float: left;’>
<input type=”button” value=”Export to Excel” id=’excelExport’ />
<input type=”button” value=”Export to XML” id=’xmlExport’ />
</div>
<div style=’margin-left: 10px; float: left;’>
<input type=”button” value=”Export to CSV” id=’csvExport’ />
</div>
<div style=’margin-left: 10px; float: left;’>
<input type=”button” value=”Export to PDF” id=’pdfExport’ />
</div>
</div>
</div>
</body>
<?php
} else {echo “User not authorized to access this page, to apply for access, contact GO. <br>”;}
} else {echo “<h3>You are not yet login. To access the data, you need to login first! <br><br>To login: <br><h3>”;
?>

<?php echo “<h3>Refresh this page after login to access to the ePortfolio Main Menu. <br><h3>”;} ?>
</html>

September 8, 2015 at 4:31 am grid editing for php mysql not save #75623

admin
Keymaster

Hi amylam,

Unfortunately, I do not see the problem to be on our side here. It should be something specific to your PHP code and Update queries. The Save to DB is a custom server code, not part of jqxGrid. The PHP code should be specific to your DB and Field names and DB Structure and restrictions at all. I would suggest you to debug your PHP code and see what happens after the UPDATE query.

Best Regards,
Peter Stoev

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

Posts

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

You must be logged in to reply to this topic.