automatic update of external json .txt file at each new record edit / insertion
automatic update of external json .txt file at each new record edit / insertion
Franq
Posts: 6Questions: 1Answers: 0
hello everyone,
I've been using for a couple of years a really basic set up of datatables on my web page, mainly for displaying some basic information and it always worked fine.
The underlying process is the simplest one: data is stored into a mysql db (via sms), retrieved and displayed on page via a standard php-mysql request whose code I'll show you in a second ..
So far so good: it all works fine. What happens now?
I need to include some new info and the solution I chose was the child row hide / show toggle function which I love; and since I don't care very much about sliding animation I've simply chosen to implement this example into my web page, and again I was able to make it work.. partially at least ..
and this the reason to my questions here, so let's begin
<?php
require_once("dbcontroller.php");
$db_handle = new DBController();
$sql = "SELECT * from table ORDER BY data DESC";
$faq = $db_handle->runQuery($sql);
header("Content-Type: text/html; charset=utf-8");
?>
<html>
<head>
<style>
td.details-control {
background: url('img/details_open.png') no-repeat center center;
cursor: pointer;
}
tr.shown td.details-control {
background: url('img/details_close.png') no-repeat center center;
}
</style>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/dt/dt-1.10.18/b-1.5.6/b-colvis-1.5.6/datatables.min.js"></script>
<script type="text/javascript" src="https://code.jquery.com/jquery-3.3.1.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script>
/* Formatting function for row details - modify as you need */
function format ( d ) {
// `d` is the original data object for the row
return '<table cellpadding="5" cellspacing="0" border="0" style="padding-left:50px;">'+
'<tr>'+
'<td>Testo:</td>'+
'<td><?php echo hello, I dont know how to insert proper php-mysql code here .. ?></td>'+
'</tr>'+
'<tr>'+
'<td>Extension number:</td>'+
'<td>'+d.extn+'</td>'+
'</tr>'+
'<tr>'+
'<td>Extra info:</td>'+
'<td>And any further details here (images etc)...</td>'+
'</tr>'+
'</table>';
}
// retrieve data from json txt file
$(document).ready(function() {
var table = $('#example').DataTable( {
//"ajax": "prova.txt",
"columns": [
{
"className": 'details-control',
"orderable": false,
"data": null,
"defaultContent": ''
},
{ "data": "id" },
{ "data": "label" },
{ "data": "text" },
{ "data": "context" },
{ "data": "date" },
{ "data": "author" },
{ "data": "gruppo" },
{ "data": "tag_1" },
{ "data": "tag_2" },
{ "data": "tag_3" }
],
"order": [[1, 'asc']]
} );
// Add event listener for opening and closing details
$('#example tbody').on('click', 'td.details-control', function () {
var tr = $(this).closest('tr');
var row = table.row( tr );
if ( row.child.isShown() ) {
// This row is already open - close it
row.child.hide();
tr.removeClass('shown');
}
else {
// Open this row
row.child( format(row.data()) ).show();
tr.addClass('shown');
}
} );
} );
</script>
<title>Index Table</title>
</head>
<body>
<div class="titolo" align="center"><br><br><u>Flowers</u> TABLE</div>
<br><br>
<table id="big-table" border=0 bordercolor=#000000 align="center">
<tr> <!-- prima riga della Tabella Contenitore -->
<td align="center"> <!-- INIZIO td contenitore -->
<table id="example" class="display" style="width:100%">
<thead>
<tr>
<th></th>
<th>id</th>
<th>label</th>
<th>text</th>
<th>context</th>
<th>data</th>
<th>autore</th>
<th>gruppo</th>
<th>tag_1</th>
<th>tag_2</th>
<th>tag_3</th>
</tr>
</thead>
<tfoot>
<tr>
<th></th>
<th>id</th>
<th>label</th>
<th>text</th>
<th>context</th>
<th>data</th>
<th>autore</th>
<th>gruppo</th>
<th>tag_1</th>
<th>tag_2</th>
<th>tag_3</th>
</tr>
</tfoot>
<tbody>
<?php
foreach($faq as $k=>$v) {
?>
<tr>
<!-- <td></*?php echo $k+1; ?*/></td> -->
<td></td>
<td><?php echo $faq[$k]["id"]; ?></td>
<td><?php echo $faq[$k]["label"]; ?></td>
<td><?php echo $faq[$k]["testo"]; ?></td>
<td><?php echo $faq[$k]["context"]; ?></td>
<td><?php echo $faq[$k]["data"]; ?></td>
<td><?php echo $faq[$k]["author"]; ?></td>
<td><?php echo $faq[$k]["gruppo"]; ?></td>
<td><?php echo $faq[$k]["tag_1"]; ?></td>
<td><?php echo $faq[$k]["tag_2"]; ?></td>
<td><?php echo $faq[$k]["tag_3"]; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</body>
</html>
as you can see the code is a bit of a mix here: it retrieves data from mysql db via some "classic" php-mysql queries, whereas I would like to be able to retrieve data from the external prova.txt json file altogether, which as you can see is commented at the moment.
I was able to retrieve data from the .txt file of course, but I don't know how to generate data into an external json-formatted .txt file instead!!
Any tutorial or example I could find on the internet does not supply this kind of information: how do I build a script that automatically converts any updated data to the db into a txt file?
Let's add another layer at this point: I have come to the conclusion that I need to add the datatables editor module in order to be able to edit and insert new data directly and easily on the web page.
What will the resulting process be like?
Whenever I insert a new piece of data or modify an existing one, some code should both insert the new data into the mysql db and at the same time overwrite or update an external json-formatted .txt file to be loaded from the table.. is this the case? Is this what happens / should happen? How do I do that?
I hope my request was clear enough..
Advice and some explanation welcome 
thanks
Answers
You would need to write the code that parses the JSON formatted text file. For Datatables you could do it in your server code and return a JSON response similar to what happens with your MySql setup. Or you could use -option ajax.dataSrc` as a function to parse the text file and return Javascript based data. There is an example in the docs for this.
You would need to do the same for the Editor. However updating a flat text file would be very inefficient. Likely you would need to read the whole file each time you process and update, create or delete then re-write the file. I would stick with using a DB which is meant to handle these operations efficiently.
Kevin
I agree that an external file would imply a havier process and work-load, but then why Datatables itself is set to work with an external file by default?? Just for an ease of use in the examples?
And besides: if I decide to switch to the classic php-mysql approach then I would need to implement php code into the javascript, which I am not able to at the moment..
could you provide me a link of the example you are talking about, since I didn't understand the first part of your answer, thanks
Kevin was referring to the example for
ajax.dataSrc- see the last one on that page.yes thank you but the point still is: if you state that an external source of data would be slower and less practical compared to retrieving it directly from the db, why would I want to use this approach?
Why would I want to convert the data from mysql database, parse it into json, load it into the table when I can pull it right from the db itself, which was supposedly disigned for this .. ?
We are not talking about pulling data from a websocket or a rest api source here, like financial markets, that needs to be parsed from json: we are talking about data which is stored into the most typical mysql db..
there must be something I am missing ..
Your example above refers to a json text file and this question:
Trying to use realtime data updates with a text file would be very slow and problematic. You shouldn't need to do any of those things with a MySql DB. Guess I misunderstood and thought you were trying to use a text file.
Kevin
There is nothing built-in to DataTables or its Editor which will overwrite or update a text file. Should you wish to code such a process yourself, Editor's postCreate method would be a starting point.
https://editor.datatables.net/reference/event/postCreate
ok guys,
thanks for the answers. It was my mistake: I was not clear in my request.
Let me ask again this time more humbly.
I would like to create a table with datatable with child rows expanding capabilities.
Installing this new feature made me realise that probably I don't know enough about how datatables manages data flow: about the interaction with javascript, ajax and mysql.
So I guess a good starting point would be there.
now, if I look at the official example from datatables i see this code
where, as you can see, is a fu*ng **.txt file so, I guess, it's perfectly licit if a user is induced to think that the interaction with an external .txt file is the standard way to go, is the right approach. Am I wrong about this?
let's start from here if you wish: could someone please explain which is the usual way datatables work, how datatables usually interacts with data?
I guess it should be something like this:
javascript makes a call to an external .php file, which in its turn connects to the db, retrieves data and parse it into json format.. this data is then passed to the table.
and this because, correct me if I am wrong, because javascript is not a server language so it needs to be supported by a server language as .php.
So, what does the fu***ng .txt file has to do with this????
thanks
ps I have started to read something here: I hope this helps
Datatables sends an ajax request to the server at the URL provided and expects a JSON response. JSON is essentially a string of data. In the case of the example you reference the object.txt contains the string of JSON data. You can look at a similar example here:
https://datatables.net/examples/ajax/objects.html
Click on the Ajax tab to see the response. More interesting would be to look at the response using the browser's developer tools > network tab.
Datatables doesn't care nor know what the source of the data is nor what code (PHP, Python, etc) is running on the server. Its happy as long as the response is JSON.
A more interesting example for you would be the Editor examples. Take a look at the Basic Example. You will see tabs for the server script (PHP), ajax load and ajax response. Not a text file
EDIT: Note that Datatables is also sending the ajax request to the same PHP script.
EDIT 2: This Editor example shows that Datatables and Editor don't need to call the same PHP (or whatever) script.
You may also be interested in reading the Editor client/server data exchange docs.
Sorry for the confusion. Thought you had a requirement to use a text not.
Kevin
Hi Franq, did you have any luck with updating datatable using external files?