[HOW TO...?] Read a DB, write table, make its cells editable, save cell value on DB
[HOW TO...?] Read a DB, write table, make its cells editable, save cell value on DB
Hi,
I've opened a request for my specific case but I'm trying hard to make DataTables+Jeditable work and I've found some problems with the initialisation.
The problem is the following: by going thru the examples/how-tos I've found some different way to do things.
I've tried them with different results: in one my aaData array is empty, in the other one is populate but in both cases the whole setup doesn't work. With the old initialisation (empty array) I can get Jeditable work but it doesn't save the content on the database, with the full array I can't get jeditable to work. It's kinda messy. Feels like I have to mix it up but still I can't make i work: I wonder if I should start from scratch by asking you the whole example without picking up various configurations and mix them up.
So the point is:
what's the basic configuration/initialisation to make a table from a DB, make the table cells editable and save the edited content onto the database?
I've opened a request for my specific case but I'm trying hard to make DataTables+Jeditable work and I've found some problems with the initialisation.
The problem is the following: by going thru the examples/how-tos I've found some different way to do things.
I've tried them with different results: in one my aaData array is empty, in the other one is populate but in both cases the whole setup doesn't work. With the old initialisation (empty array) I can get Jeditable work but it doesn't save the content on the database, with the full array I can't get jeditable to work. It's kinda messy. Feels like I have to mix it up but still I can't make i work: I wonder if I should start from scratch by asking you the whole example without picking up various configurations and mix them up.
So the point is:
what's the basic configuration/initialisation to make a table from a DB, make the table cells editable and save the edited content onto the database?
This discussion has been closed.
Replies
I've found the solution. It was not easy for a beginner but I must say that not all the examples/guides are clear. I've got to read different forum topics inside datatables.net and outside to get things working (without forum support, sadly :( ...).
Still I can't get the Themeroller themes run properly: as usual I've referred to the examples and I've imported the files from Datatables.net and the Themeroller website but I can't get it fully themed.
My page does the following:
- connects via ODBC to a Postgres DB
- draws the table reading the data from the Postgres database: assigns it a class, and ID (useless if we consider the examples in this site to locate cell using an ID) for each row
- enables datatables and jeditables with a proper initialization
- uses the server-side PHP file for Postgres found in this site to retreive data
- uses a customized Ajax PHP to connect via ODBC to Postgres and update the cell records
- uses column name and Postgres' own OID to locate the cell. Column name is obtained via JQuery but I think it should be substituted with an ID of the column. For now I am not aware of the possibility to update a column in Postgres with a query that indicates the column ID. Column name are the same column names that are present in DB's table. This is gotta be fixed
- hides the OID column from the table so the users won't be able to modify it
index.php
[code]
<!DOCTYPE html>
<!-- Scripts
-->
<!-- Styles
@import "css/tableStyle.css";
@import "css/jquery.dataTables_themeroller.css";
@import "css/jquery.dataTables.css";
@import "css/jquery-ui-1.10.2.custom.min.css";
@import "css/demo_page.css";
@import "css/demo_table_jui.css";
-->
@import "css/tableStyle.css";
$(document).ready(function() {
// Init DataTables
// Inizializzazione base con il prelievo dei dati tramite un apposito
// script in PHP che si interfaccia al DB Postgres: server_processing.php
var oTable = $('#jqtable').dataTable({
//SADLY NOT WORKING
//"bJQueryUI": true,
//"sPaginationType": "full_numbers",
"bProcessing": true,
"sAjaxSource": "server_processing.php",
// Funzione di callback ove viene abilitato il plug-in Jeditable:
// è possibile modificare le celle (TD) della tabella.
// Lo script PHP editable_ajax.php si interfaccia al DB Postgres
// tramite ODBC e restituisce messaggi di errore/successo in fase
// di debug oppure il corretto aggiornamento della cella in produzione
"fnDrawCallback": function () {
$('#jqtable tbody td').editable( 'editable_ajax.php', {
callback: function( sValue, y ) {
// Redraw the table from the new data on the server
var aPos = oTable.fnGetPosition( this );
oTable.fnUpdate( sValue, aPos[0], aPos[2] );
},//callback
submitdata: function ( value, settings ) {
var aPos = oTable.fnGetPosition( this );
var $This = $(this);
var col = $This.parent().children().index($(this));
var title = $This.closest("table").find("th").eq(col).text();
var oid = $This.parent().children().text();
var RowIndex = oTable.fnGetData(aPos[0]);
return {
pos: aPos, //just to check aPos output
row_id: RowIndex[0],
column: title,
};
},//submitdata
tooltip: 'Click to edit...',
height: "14px",
width: "100%"
} );//editable
}, //fnDrawCallback
} ); //var oTable
// Dopo l'inizializzazione la colonna OID viene celata:
// la colonna non può essere modificabile dall'utente
oTable.fnSetColumnVis( 0, false );
} );//(document).ready(function)
<?php
//phpinfo();
/*
* Connecting to PostgreSQL using ODBC
* Version: 160408
* Written by Piotr Polak
*/
/* Connection data */
$odbc_driver = 'PostgreSQL Unicode';
$odbc_database = 'xxx';
$odbc_host = 'xxx';
$odbc_user = 'xxx';
$odbc_password = 'xxx';
include(index.php);
/* ************************************************** */
/* Building DSN */
$dsn = 'DRIVER={'.$odbc_driver.'};'.
'Server='.$odbc_host.';'.
'Database='.$odbc_database.';'.
'uid='.$odbc_user.'; pwd='.$odbc_password;
/* Connecting */
$con = @odbc_connect($dsn, '', '') or die('Connection error: '.htmlspecialchars(odbc_errormsg()));
/* Prepares query */
$query = 'SELECT VERSION() AS ver';
/* Executes query */
$result = @odbc_exec($con, $query) or die('Query error: '.htmlspecialchars(odbc_errormsg()));;
/* Fetches array */
$row = odbc_fetch_array($result);
/* Printing message */
echo 'Success! Server version is: '.$row['ver'].'';
//(anno, tpdoc, nureg, tpnum, nufis, nurig, nprog, cdart)
$sqlPrimary="SELECT * FROM myschema.test;";
if(!($resPrimary = odbc_exec($con,$sqlPrimary)))
{
echo "Select error: " . odbc_error($con) . "
" .odbc_errormsg($con) . "
";
}
echo "";
echo "
oid
data
orainizio
orafine
codoperatore
codmacchina
codcommessa
";
/*
$rowId=1;
/* Body della tabella*/
while($row = odbc_fetch_row($resPrimary))
{
echo "
" . odbc_result($resPrimary, "oid") . "
" . odbc_result($resPrimary, "data") . "
" . odbc_result($resPrimary, "orainizio") . "
" . odbc_result($resPrimary, "orafine") . "
" . odbc_result($resPrimary, "codoperatore") . "
" . odbc_result($resPrimary, "codmacchina") . "
" . odbc_result($resPrimary, "codcommessa") . "
";
$rowId=$rowId+1;
}
/* Tag di chiusura */
echo "
";
odbc_close($con);
?>
[/code]
On server-side.php include all the columns, including OID!!!
Now ajax_editable.php follows but please note that it has a debugging style.
[code]
<?php
/*
* Connecting to PostgreSQL using ODBC
* Version: 160408
* Written by Piotr Polak
*/
/* Connection data */
$odbc_driver = 'PostgreSQL Unicode';
$odbc_database = 'xxx';
$odbc_host = 'xxx';
$odbc_user = 'xxx';
$odbc_password = 'xxx';
include(index.php);
/* ************************************************** */
/* Building DSN */
$dsn = 'DRIVER={'.$odbc_driver.'};'.
'Server='.$odbc_host.';'.
'Database='.$odbc_database.';'.
'uid='.$odbc_user.'; pwd='.$odbc_password;
/* Connecting */
$con = @odbc_connect($dsn, '', '') or die('Connection error: '.htmlspecialchars(odbc_errormsg()));
/* Prepares query */
$query = 'SELECT VERSION() AS ver';
/* Executes query */
$result = @odbc_exec($con, $query) or die('Query error: '.htmlspecialchars(odbc_errormsg()));;
/* Fetches array */
$row = odbc_fetch_array($result);
/* Printing message */
//echo 'Success! Server version is: '.$row['ver'].'';
/* Aggiornamento del DB */
$value = $_REQUEST['value'] ;
$column = $_REQUEST['column'] ;
$oid = $_REQUEST['row_id'] ;
echo "
Colonna: " .$column. "
Valore: " .$value ."
";
$updateSql = "UPDATE myschema.test SET " . $column . "='" . $value . "' WHERE oid=" . $oid .";";
echo $updateSql . "
";
if(!($resUpdate = odbc_exec($con,$updateSql)))
{
echo "Update error: " . odbc_error($con) . "
" .odbc_errormsg($con) . "
";
}
else
{
echo $_POST['value'].' (server updated)';
}
// Free resultset
odbc_free_result($resUpdate);
// Closing connection
odbc_close($con);
?>
[/code]
I think there should be a little clearer example section, even if I'm a beginner informations are incomplete and there are no clear directions to where you should look to understand the whole thing.
Now, I'm asking you help for Themeroller configuration.
As you may see on top I've kept everything commented because it's not working properly.
The table appears, it's formatted but the colors are wrong. I can change them manually but I'd rather prefer to be able to use a Themeroller UI (Lightness-UI) as I download it from the site without going crazy.
I hope to get an answer this time :)
Dimitri