I am trying out Handsontable plugin which looks great, but I am not sure how to implement the save via the getdata function.
Is anyone aware of working examples? I could save to .json or to mysql, but I prefer to keep it simple.
The loading works fine, but I get an error on saving. I have looked around, but there isn't alot of info that I could find.
Any help is appreciated.
HTML:
<!doctype html>
<html>
<head>
<meta charset='utf-8'>
<title></title>
<!--
Loading Handsontable (full distribution that includes all dependencies apart from jQuery)
-->
<script src="js/jquery.min.js"></script>
<script src="js/jquery.handsontable.full.js"></script>
<link rel="stylesheet" media="screen" href="js/jquery.handsontable.full.css">
<!--
Loading demo dependencies. They are used here only to enhance the examples on this page
-->
<link rel="stylesheet" media="screen" href="css/samples.css">
<script src="js/samples.js"></script>
<script src="js/highlight/highlight.pack.js"></script>
<link rel="stylesheet" media="screen" href="js/highlight/styles/github.css">
</head>
<body>
<div id="container">
<div class="rowLayout">
<div class="descLayout">
<div class="pad">
</div>
</div>
</div>
<div class="rowLayout">
<div class="descLayout">
<div class="pad bottomSpace850">
<p>Note: this is a mockup..</p>
<p>
<button name="load">Load</button>
<button name="save">Save</button>
<label><input type="checkbox" name="autosave" checked="checked" autocomplete="off"> Autosave</label>
</p>
<pre id="example1console" class="console">Click "Load" to load data from server</pre>
<div id="example1"></div>
</div>
</div>
<div class="codeLayout">
<div class="pad">
<script>
var $container = $("#example1");
var $console = $("#example1console");
var $parent = $container.parent();
var autosaveNotification;
$container.handsontable({
startRows: 7,
startCols: 7,
rowHeaders: true,
colHeaders: true,
minSpareRows: 1,
contextMenu: true,
manualColumnResize: true,
columnSorting: true,
onChange: function (change, source) {
if (source === 'loadData') {
return; //don't save this change
}
if ($parent.find('input[name=autosave]').is(':checked')) {
clearTimeout(autosaveNotification);
$.ajax({
url: "json/save.json",
dataType: "json",
type: "POST",
data: change, //contains changed cells' data
complete: function (data) {
$console.text('Autosaved (' + change.length + ' cell' + (change.length > 1 ? 's' : '') + ')');
autosaveNotification = setTimeout(function () {
$console.text('Changes will be autosaved');
}, 1000);
}
});
}
}
});
var handsontable = $container.data('handsontable');
$parent.find('button[name=load]').click(function () {
$.ajax({
url: "json/load.json",
dataType: 'json',
type: 'GET',
success: function (res) {
handsontable.loadData(res.data);
$console.text('Data loaded');
}
});
});
$parent.find('button[name=save]').click(function () {
$.ajax({
url: "json/save.json",
data: {"data": handsontable.getData()}, //returns all cells' data
dataType: 'json',
type: 'POST',
success: function (res) {
if (res.result === 'ok') {
$console.text('Data saved');
}
else {
$console.text('Save error');
}
},
error: function () {
$console.text('Save error. POST method is not allowed on GitHub Pages. Run this example on your own server to see the success message.');
}
});
});
$parent.find('input[name=autosave]').click(function () {
if ($(this).is(':checked')) {
$console.text('Changes will be autosaved');
}
else {
$console.text('Changes will not be autosaved');
}
});
</script>
</div>
</div>
</div>
<div class="rowLayout">
<div class="descLayout">
</div>
</div>
</div>
</body>
</html>
Load.json
{
"data": [
["", "Kia", "Nissan", "Toyota", "Honda"],
["2008", 10, 11, 12, 13],
["2009", 20, 11, 14, 13],
["2010", 30, 15, 12, 13]
]
}
save.json
{
"data": [
["", "Kia", "Nissan", "Toyota", "Honda"],
["2008", 10, 11, 12, 13],
["2009", 20, 11, 14, 13],
["2010", 30, 15, 12, 13]
]
}
PHP/ sqlite seems to be the best solution. My working example is below.
HTML:
<!doctype html>
<html>
<head>
<meta charset='utf-8'>
<title></title>
<!--
Loading Handsontable (full distribution that includes all dependencies apart from jQuery)
-->
<script src="js/jquery.min.js"></script>
<script src="js/jquery.handsontable.full.js"></script>
<link rel="stylesheet" media="screen" href="js/jquery.handsontable.full.css">
<!--
Loading demo dependencies. They are used here only to enhance the examples on this page
-->
<link rel="stylesheet" media="screen" href="css/samples.css">
<script src="js/samples.js"></script>
<script src="js/highlight/highlight.pack.js"></script>
<link rel="stylesheet" media="screen" href="js/highlight/styles/github.css">
</head>
<body>
<div id="container">
<div class="rowLayout">
<div class="descLayout">
<div class="pad">
</div>
</div>
</div>
<div class="rowLayout">
<div class="descLayout">
<div class="pad bottomSpace850">
<p>Note: this is a mockup..</p>
<p>
<button name="load">Load</button>
<button name="save">Save</button>
<label><input type="checkbox" name="autosave" checked="checked" autocomplete="off"> Autosave</label>
</p>
<pre id="example1console" class="console">Click "Load" to load data from server</pre>
<div id="example1"></div>
</div>
</div>
<div class="codeLayout">
<div class="pad">
<script>
var $container = $("#example1");
var $console = $("#example1console");
var $parent = $container.parent();
var autosaveNotification;
$container.handsontable({
startRows: 3,
startCols: 3,
rowHeaders: true,
colHeaders: true,
minSpareRows: 1,
contextMenu: true,
manualColumnResize: true,
columnSorting: true,
onChange: function (change, source) {
if (source === 'loadData') {
return; //don't save this change
}
if ($parent.find('input[name=autosave]').is(':checked')) {
clearTimeout(autosaveNotification);
$.ajax({
url: "json/save.php",
dataType: "json",
type: "POST",
data: change, //contains changed cells' data
complete: function (data) {
$console.text('Autosaved (' + change.length + ' cell' + (change.length > 1 ? 's' : '') + ')');
autosaveNotification = setTimeout(function () {
$console.text('Changes will be autosaved');
}, 1000);
}
});
}
}
});
var handsontable = $container.data('handsontable');
$parent.find('button[name=load]').click(function () {
$.ajax({
url: "json/load.php",
dataType: 'json',
type: 'GET',
success: function (res) {
handsontable.loadData(res.data);
$console.text('Data loaded');
}
});
});
$parent.find('button[name=save]').click(function () {
$.ajax({
url: "json/save.json",
data: {"data": handsontable.getData()}, //returns all cells' data
dataType: 'json',
type: 'POST',
success: function (res) {
if (res.result === 'ok') {
$console.text('Data saved');
}
else {
$console.text('Save error');
}
},
error: function () {
$console.text('Save error. POST method is not allowed on GitHub Pages. Run this example on your own server to see the success message.');
}
});
});
$parent.find('input[name=autosave]').click(function () {
if ($(this).is(':checked')) {
$console.text('Changes will be autosaved');
}
else {
$console.text('Changes will not be autosaved');
}
});
</script>
</div>
</div>
</div>
<div class="rowLayout">
<div class="descLayout">
</div>
</div>
</div>
</body>
</html>
SAVE.PHP
<?php
/**
Working example
*/
try {
//open the database
$db = new PDO('sqlite:cars.sqlite'); //will create the file in current directory. Current directory must be writable
//create the database if does not exist
$db->exec("CREATE TABLE IF NOT EXISTS cars (id INTEGER PRIMARY KEY, ONE TEXT, TWO TEXT, THREE TEXT)");
$colMap = array(
0 => 'ONE',
1 => 'TWO',
2 => 'THREE',
);
if ($_POST['changes']) {
foreach ($_POST['changes'] as $change) {
$rowId = $change[0] + 1;
$colId = $change[1];
$newVal = $change[3];
if (!isset($colMap[$colId])) {
echo "\n spadam";
continue;
}
$select = $db->prepare('SELECT id FROM cars WHERE id=? LIMIT 1');
$select->execute(array(
$rowId
));
if ($row = $select->fetch()) {
$query = $db->prepare('UPDATE cars SET `' . $colMap[$colId] . '` = :newVal WHERE id = :id');
} else {
$query = $db->prepare('INSERT INTO cars (id, `' . $colMap[$colId] . '`) VALUES(:id, :newVal)');
}
$query->bindValue(':id', $rowId, PDO::PARAM_INT);
$query->bindValue(':newVal', $newVal, PDO::PARAM_STR);
$query->execute();
}
} elseif ($_POST['data']) {
$select = $db->prepare('DELETE FROM cars');
$select->execute();
for ($r = 0, $rlen = count($_POST['data']); $r < $rlen; $r++) {
$rowId = $r + 1;
for ($c = 0, $clen = count($_POST['data'][$r]); $c < $clen; $c++) {
if (!isset($colMap[$c])) {
continue;
}
$newVal = $_POST['data'][$r][$c];
$select = $db->prepare('SELECT id FROM cars WHERE id=? LIMIT 1');
$select->execute(array(
$rowId
));
if ($row = $select->fetch()) {
$query = $db->prepare('UPDATE cars SET `' . $colMap[$c] . '` = :newVal WHERE id = :id');
} else {
$query = $db->prepare('INSERT INTO cars (id, `' . $colMap[$c] . '`) VALUES(:id, :newVal)');
}
$query->bindValue(':id', $rowId, PDO::PARAM_INT);
$query->bindValue(':newVal', $newVal, PDO::PARAM_STR);
$query->execute();
}
}
}
$out = array(
'result' => 'ok'
);
echo json_encode($out);
// close the database connection
$db = NULL;
}
catch (PDOException $e) {
print 'Exception : ' . $e->getMessage();
}
?>