editable datatable
editable datatable
I'm implementing the editable datatable plugin. I am having an issue with the table self updating. My AJAX call is correctly updating my DB but the edited field does not change unless I refresh the table.
In the case of both the UPDATE and DELETE events, the DB is updated but the return is displayed as an alert which I believe indicates an error from the client-page perspective. In the case of update, I am suppose to return the value of the change; and this is what is displayed in the error. In the case of DELETE, I am supposed to return "ok" and this too is displayed in the error dialog.
Any and all suggestions greatly appreciated ... Here's the code:
The test page with the table is:
- <?php
session_start();
include("connect.php");
include("titleCase.php");
$teacher = 6;
if($teacher) {
$result = mysql_query("SELECT * FROM students WHERE teacher = ".$teacher." ORDER BY lname, fname");
$num = mysql_num_rows ($result);
mysql_close();
} else $num = 0;
if ($num > 0 ) {
$i=0;
while ($i < $num) {
$id = stripslashes(mysql_result($result,$i,"id"));
$salut = strtoupper("Miss.");
$fname = strtoupper(stripslashes(mysql_result($result,$i,"fname")));
$lname = stripslashes(mysql_result($result,$i,"lname"));
$teacher = stripslashes(mysql_result($result,$i,"teacher"));
$studentname = $fname." ".$lname;
$trows .= '<tr class="odd_gradeX" id="'.$id.'">
<td >'.$salut.'</td>
<td>'.$fname.'</td>
<td>'.$lname.'</td>
</tr>';
++$i; }
}
else { $trows = '<tr class="odd_gradeX" id="0">
<td >empty</td>
<td >empty</td>
</tr>'; }
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html><head>
<meta http-equiv="Content-type" content="text/html; charset=UTF-8">
<title>DataTables (table plug-in for jQuery) Column Filter Add-on</title>
<link rel="shortcut icon" type="image/ico" href="http://www.datatables.net/media/images/favicon.ico">
<link rel="alternate" type="application/rss+xml" title="RSS 2.0" href="http://www.datatables.net/rss.xml">
<style type="text/css" media="screen">
@import "media/css/demo_page.css";
@import "media/css/demo_table.css";
@import "http://www.datatables.net/media/css/site_jui.ccss";
@import "media/css/demo_table_jui.css";
@import "media/css/themes/base/jquery-ui.css";
@import "media/css/themes/smoothness/jquery-ui-1.7.2.custom.css";
/*
* Override styles needed due to the mix of three different CSS sources! For proper examples
* please see the themes example in the 'Examples' section of this site
*/
.dataTables_info { padding-top: 0; }
.dataTables_paginate { padding-top: 0; }
.css_right { float: right; }
#example_wrapper .fg-toolbar { font-size: 0.8em }
#theme_links span { float: left; padding: 2px 10px; }
</style>
<script type="text/javascript" src="media/js/complete.js"></script>
<script type="text/javascript" src="media/js/jquery-1.4.4.min.js" ></script>
<script type="text/javascript" src="media/js/jquery.dataTables.min.js" ></script>
<script type="text/javascript" src="media/js/jquery.dataTables.editable.js"></script>
<script src="media/js/jquery.jeditable.js" type="text/javascript"></script>
<script type="text/javascript" src="media/js/jquery-ui.js" ></script>
<script type="text/javascript" src="media/js/jquery.validate.js" ></script>
<script type="text/javascript" charset="utf-8">
$(document).ready( function () {
var id = -1;//simulation of id
$('#example').dataTable({ bJQueryUI: true,
"sPaginationType": "full_numbers"
}).makeEditable({
sUpdateURL: 'update.php'
,
sAddURL: 'add.php'
,
sAddHttpMethod: "GET",
sDeleteHttpMethod: "GET",
sDeleteURL: 'delete.php',
oAddNewRowButtonOptions: { label: "Add...",
icons: {primary:'ui-icon-plus'}
},
oDeleteRowButtonOptions: { label: "Remove",
icons: {primary:'ui-icon-trash'}
},
oAddNewRowFormOptions: {
title: 'Add a new student',
show: "blind",
hide: "explode",
modal: true
} ,
sAddDeleteToolbarSelector: ".dataTables_length"
});
} );
</script>
<script type="text/javascript">
var _gaq = _gaq || [];
_gaq.push(['_setAccount', 'UA-17838786-2']);
_gaq.push(['_trackPageview']);
(function () {
var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
})();
</script>
</head>
<body id="index" class="grid_2_3">
<div class="css_clear css_spacing"></div>
<h3>Example</h3>
<div class="full_width">
<form id="formAddNewRow" action="#" title="Add a new student" style="width:600px;min-width:600px">
<label for="salut"Salutation></label><br />
<select name="salut" id="salut" rel="0">
<option>Miss.</option>
<option>Mr.</option>
<option>Mrs.</option>
</select>
<br />
<label for="fname">First Name</label><br />
<input type="text" name="fname" id="fname" rel="1" />
<br />
<label for="lname">Last Name</label><br />
<input type="text" name="lname" id="lname" rel="2" />
<br />
</form>
<table cellpadding="0" cellspacing="0" border="0" class="display" id="example">
<thead>
<tr>
<th>Salutation</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
</thead>
<tfoot>
<tr>
<th>Salutation</th>
<th>First Name</th>
<th>Last Name</th>
</tr>
</tfoot>
<tbody>
<?php echo $trows; ?>
</tbody>
</table>
</div>
</div>
<div class="css_clear"></div>
<div class="css_spacing"></div>
</div>
</body>
</html>
The AJAX code for update is:
- <?php
include('connect.php');
//get info from post
$id = mysql_real_escape_string($_REQUEST['id']) ;
$value = strtoupper(mysql_real_escape_string($_REQUEST['value'])) ;
$column = mysql_real_escape_string($_REQUEST['columnName']) ;
$columnPosition = mysql_real_escape_string($_REQUEST['columnPosition']) ;
$columnId = mysql_real_escape_string($_REQUEST['columnId']) ;
$rowId = mysql_real_escape_string($_REQUEST['rowId']) ;
switch ($columnId) {
case 0:
$field = "salut";
break;
case 1:
$field = "fname";
break;
case 2:
$field = "lname";
break;
}
mysql_query("UPDATE students SET ".$field."='".strtoupper($value)."' WHERE id='".$id."'");
mysql_close($connection);
echo $_REQUEST['value'];
?>
And the code for DELETE is:
- <?php
session_start();
function queryOrDie($query)
{
$query = mysql_query($query);
if (! $query) exit(mysql_error());
return $query;
}
include('connect.php');
//get info from post
$id = strtoupper(mysql_real_escape_string($_REQUEST['id'])) ;
/* Delete record $id */
$sql = "DELETE FROM students WHERE id = ".$id;
queryOrDie($sql);
mysql_close($connection);
echo "ok";
?>