Modify multiple columns based on one value
Modify multiple columns based on one value
I have three tables:
TABLE entrepots :
id_entrepots int(200) NOT NULL,
nom_entrepots varchar(150) NOT NULL,
TABLE equipements :
nom_equipements varchar(200) NOT NULL,
groupe_equipements varchar(200) NOT NULL,
periodicite_equipements varchar(100) NOT NULL
TABLE controles :
id_controles int(200) NOT NULL,
date_controles_realiser date NOT NULL,
date_prochain_controles date NOT NULL,
date_rdv date NOT NULL,
etat_controles varchar(10) NOT NULL,
With the right leftovers to make it all work.
My problem:
When I open the modal to edit the data, I want to update several columns after modifying the date_controles_realiser.
example
date_controles_realiser in database is 2023-06-12
If I change it to 2024-06-12 and click on UPDATE :
Datatable will then display the new date in date_prochain_controles according to the data in the periodicite_equipements column (Annual, monthly, half-yearly, ...).
EX: if it's Annual, the new date date_prochain_controles will be 2024-06-12 and etat_controles will be "GREEN" if date_controles_realiser is less than or equal to the current date AND the date_rdv will be empty.
But I have no idea how to implement this Datatable Editor (version 1.9)
my code :
var editor; // use a global for the submit and return data rendering in the examples
// Activate an inline edit on click of a table cell
$('#example').on( 'click', 'tbody td.editable', function (e) {
editor.inline( this, {
//onBlur: 'submit'
buttons: { label: ">",
className: 'btn-sm',
fn: function () { this.submit(); } }
} );
} );
$('#example').DataTable( {
initComplete: function () {
this.api().columns([1,2,3,4,5,13]).every( function () {
var column = this;
var select = $('<select><option value="">ALL</option></select>')
.appendTo( $(column.footer()).empty() )
.on( 'change', function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
column
.search( val ? ''+val+'' : '', true, false )
.draw();
} );
column.data().unique().sort().each( function ( d, j ) {
select.append( '<option value="'+d+'">'+d+'</option>' )
} );
} );
},
dom: "Bfrtip",
ajax: {
url: "Staff_4.php",
type: 'POST'
},
serverSide: false,
columns: [
{
data: null,
defaultContent: '',
className: 'select-checkbox',
orderable: false,
searchable: false
},
{ data: "controles.etat_controles", className: "dt-body-center" ,
target: [1],
type: "alt-string",
render: function ( data, type, row ) {
if (type !== 'display') {
return data;
}
const icons = {
"BLANC": '<img src="img/blanc.gif" width="34" height="18" alt="BLANC" title="BLANC"/>',
"NOIR": '<img src="img/noir.gif" width="34" height="18" alt="NOIR" title="NOIR"/>',
"VERT": '<img src="img/vert.gif" width="34" height="18" alt="VERT" title="VERT"/>',
"ORANGE": '<img src="img/orange.gif" width="34" height="18" alt="ORANGE" title="ORANGE"/>',
"ROUGE": '<img src="img/rouge.gif" width="34" height="18" alt="ROUGE" title="ROUGE"/>',
"BLEU": '<img src="img/bleu.gif" width="34" height="18" alt="BLEU" title="BLEU"/>',
};
const values = data.split(', ');
let result = '';
values.forEach((value) => {
if (value.trim() in icons) {
result += icons[value.trim()];
} else {
result += value.trim();
}
result += ' '; // add space between values
});
return result.trim(); // remove extra space at the end
}
},
{ data: "entrepots.nom_entrepots", editField: "controles.fk_entrepots", className: "dt-body-left", searchable: true },
{ data: "equipements.nom_equipements", editField: "controles.fk_equipements", className: "dt-body-left", searchable: true },
{ data: "equipements.groupe_equipements", className: "editable dt-body-center" },
{ data: "equipements.periodicite_equipements", className: "editable dt-body-center" },
{ data: "controles.date_controles_realiser", className: "editable dt-body-center" },
{ data: "controles.date_prochain_controles", className: "editable dt-body-center" },
{ data: "controles.date_rdv", className: "editable dt-body-center" },
{ data: "controles.commentaire", className: "dt-body-center"},
{ data: "controles.lien_fichier1", className: "dt-center",
render: function ( data, type, row, meta ) {
if (data.startsWith('https://')){
return '<a href="'+data+'" target="_blank"><i class="fa fa-cloud" style="font-size:30px;color:lightgreen;text-shadow:2px 2px 4px #000000;" /></a>';
}else{return data;}
}
},
{ data: "controles.lien_fichier2", className: "dt-body-center",
render: function ( data, type, row, meta ) {
if (data.startsWith('https://')){
return '<a href="'+data+'" target="_blank"><i class="fa fa-cloud" style="font-size:30px;color:lightgreen;text-shadow:2px 2px 4px #000000;" /></a>';
}else{return data;}
}
},
{ data: "controles.lien_fichier3", className: "dt-body-center",
render: function ( data, type, row, meta ) {
if (data.startsWith('https://')){
return '<a href="'+data+'" target="_blank"><i class="fa fa-cloud" style="font-size:30px;color:lightgreen;text-shadow:2px 2px 4px #000000;" /></a>';
}else{return data;}
}
},
{ data: "controles.actif_controles", className: "dt-body-center" }
],
order: [ 2, 'asc' ],
select: {
style: 'os',
selector: 'td:first-child'
},
lengthMenu: [
[ 10, 25, 50, -1 ],
[ '10 rows', '25 rows', '50 rows', 'Show all' ]
],
buttons: [
{ extend: 'create', editor: editor },
{ extend: 'edit', editor: editor },
{ extend: 'remove', editor: editor },
{
extend: 'collection',
text: 'Export',
buttons: [
'copy',
'excel',
'csv',
'pdf',
'print'
]
},
{ extend: 'pageLength', editor: editor }
]
} );
} );
And server :
Editor::inst( $db, 'controles', 'id_controles' )
->field(
//Field::inst( 'controles.id_controles' ),
Field::inst( 'controles.etat_controles' ),
Field::inst( 'controles.fk_entrepots' )
->options( Options::inst()
->table( 'entrepots' )
->value( 'id_entrepots' )
->label( 'nom_entrepots' )
)
->validator( Validate::dbValues() ),
Field::inst( 'entrepots.nom_entrepots' ),
Field::inst( 'controles.fk_equipements' )
->options( Options::inst()
->table( 'equipements' )
->value( 'id_equipements' )
->label( 'nom_equipements' )
)
->validator( Validate::dbValues() ),
Field::inst( 'equipements.nom_equipements' ),
Field::inst( 'equipements.groupe_equipements' ),
Field::inst( 'equipements.periodicite_equipements' ),
Field::inst( 'controles.date_controles_realiser' )
->validator( Validate::dateFormat( 'Y-m-d' ) )
->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
->setFormatter( Format::dateFormatToSql('Y-m-d' ) )
->getFormatter( function ( $val, $data, $opts ) {
if ($val === "0000-00-00"){ echo ""; }else{ return date( 'Y-m-d', strtotime( $val ) ); }
} ),
Field::inst( 'controles.date_prochain_controles' )
->validator( Validate::dateFormat( 'Y-m-d' ) )
->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
->setFormatter( Format::dateFormatToSql('Y-m-d' ) )
->getFormatter( function ( $val, $data, $opts ) {
if ($val === "0000-00-00"){ echo ""; }else{ return date( 'Y-m-d', strtotime( $val ) ); }
} ),
Field::inst( 'controles.date_rdv' )
->validator( Validate::dateFormat( 'Y-m-d' ) )
->getFormatter( Format::dateSqlToFormat( 'Y-m-d' ) )
->setFormatter( Format::dateFormatToSql('Y-m-d' ) )
->getFormatter( function ( $val, $data, $opts ) {
if ($val === "0000-00-00"){ echo ""; }else{ return date( 'Y-m-d', strtotime( $val ) ); }
} ),
Field::inst( 'controles.commentaire' ),
Field::inst( 'controles.lien_fichier1' ),
Field::inst( 'controles.lien_fichier2' ),
Field::inst( 'controles.lien_fichier3' ),
Field::inst( 'controles.actif_controles' )
)
->leftJoin( 'entrepots', 'entrepots.id_entrepots', '=', 'controles.fk_entrepots' )
->leftJoin( 'equipements', 'equipements.id_equipements', '=', 'controles.fk_equipements' )
->debug(false)
->process($_POST)
->json();
thank you for your help as I don't know how to go about it
Replies
Two options:
dependent(). That is the method that is providied to update the form based on the input value of a field. You can set other field values, change visibility of fields and all that. This is the way to do it if the fields you want to modify are already available at the client-side.preEditandpreCreatein this case. It can be used to set field values based on a function (i.e. your logic) which will then be written to the database.Allan
edit Link correction
@allan used the French spelling here which doesn't take you anywhere in the docs. Here is the correct link:
https://editor.datatables.net/reference/api/dependent()
I think the documentation of "dependent" is very abstract and not that helpful. It took me years to really understand "dependent" and be able to use it effectively.
In your case you could do something like this:
If you need an example for server-side manipulation of submitted data: I have plenty of examples for that as well.
Doh! Thanks for catching @rf1234! I agree, the
dependent()docs are not my finest. I've been thinking I need to have a whole section in the examples dedicated to showing what it can do. Or at the very least link to running live examples from the docs about each point.Great example - thank you.
Allan
That would be great because it is really a lot, Allan.
Hello Allan, rf1234,
Thank you very much for your answers.
I'm going to look at/learn the
dependent()side but I don't know javascript at all.Would you happen to have an online example?
One question though, how do I transform my PHP functions into javascript?
I know it's a lot to ask, but it would help me so much to understand.
Thank you in advance for taking the time to help me.
Why would you want to transform your PHP functions into Javascript? Are they supposed to run in the browser now?
Good question rf1234! Thanks. it's because I don't master
dependent()at all and even less javascript.To try to understand
dependent()in a global wayWhen the user selects a table row and clicks on Edit, the Modal window opens.
The user modifies the date
date_controles_realiserand clicks on Update.The line information is sent to the server, intercepted by dependent() and the data is modified by my PHP functions, then after the fields have been modified, everything is sent to the database.
Is this how it works or am I missing something?
thank you for your help.
"dependent" is executed when you open an Editor on the client side and also if you make changes to the relevant fields BEFORE any kind of server submission, regardless of what happens on the server. "dependent" also works if your data table isn't ajax sourced at all. So forget the server for a moment.
Once you click "save" you send the data to the server in your case. By that time "dependent" will have been executed. There are mutliple event handlers that you can use with Editor.
For example you can use "submitSuccess" if you want to do something after a successfull server submission. Or you can use "initSubmit" if you want to manipulate the Editor form prior to submission. You can also use "preSubmit" if you want to manipulate the data that will be send to the server after they have been read from the Editor form etc. etc.
Here is an overview of the Editor events client side:
https://editor.datatables.net/manual/events#Event-sequence
And the overview of the editor events server side:
https://editor.datatables.net/manual/php/events
On the server side you can use your PHP scripts and manipulate data saved to your database, read from your database and whatever. For all of this you can use specific server events as described in the docs above.
I will post a few examples from my coding below.
Front end example "dependent". This is a complex one where I create a dependency on three different fields at the same time. Setting, showing or hiding fields depending on the situation. When any of the three different fields are a) initially shown in the form, or b) change later on, the "dependent" code is being executed - regardless of what happens on the server.
Another one: Do some weird stuff to prevent the user from deleting departments for which the user only has reading rights before server submission
Now server side:
Add a summation line to the data returned from the server by Editor after reading the data from the database:
Write the log after creating, editing or removing records:
You'll find plenty of examples in the docs, too! Good luck!
And here is an online example, too.
https://editor.datatables.net/examples/api/dependentFields.html
Thank you very much for your feedback and for all this information and especially for the time you're devoting to my subject.
I'm going to look at this carefully but I doubt I'll have the necessary level to understand it all.
I'd already seen the last example quoted above, but I'm wondering if it really corresponds to what I want to do, i.e. modify the data (column) of a row after modifying a data (column) of this same row just after the update but before writing to the database.
According to the link you've given me, I'm wondering if I shouldn't use
writeEditto modify the 3 fields in my row after the user has changed the date during an Update?The easiest way for this is "dependent" on the client side, I guess.
If you just wanted to set column2 to the value of column1 it would be even easier.
for three fields
Server side solution:
You would NOT want to use "writeEdit" because then the incomplete data has already been written to the database. What you would want to use instead is "preEdit" or "validatedEdit".
On a personal note: I started with JS, jQuery and PHP in 2016. Before that I only knew Cobol, Pascal and SQL - and my last commercial coding experience had ended in 1997.
No problem to learn Javascript or anything. It is just a programming language, not rocket science! Just take a couple of online classes. Udemy.com is pretty good for this.
You should also familiarize yourself with the basic architecture: What runs in the browser? What runs on the server and why? And how does all of that stuff interact? There is plenty of training materials out there. Good luck!
Ok for this one, because I don't need the other one because no column value replaces another.
if I begin to understand and with my information :
date_controles_realiserthis is the field that will be modified by the user.I retrieve the current date,
I retrieve the periodicity to determine the future date,
I retrieve the appointment date for the control,
and I retrieve the status of my line.
In the IF, I do all my checks to find MY_RESULT,
and in
this.set({'date_controles_realiser': "MON_RESULTAT")I send my resultI don't think it's fair the way it's written, but is the process going in the right direction?
"val" is the value of the variable you are looking at. In this case it is 'date_controles_realise'. So val['periodicite_equipements'] doesn't work.
You need to get an Editor value instead.
Thanks a lot, it's becoming clearer and thanks also for the training info. I'll seriously think about it.
I'll make progress on the verification conditions and post if I run into any difficulties, thanks again.
Regarding the date: This is a bit odd in JS. Hence many people use plugins like moment.js etc.
I coded this a long time ago to make the date transformation to something more useful for today's date ... So if you are not using a plugin you might find this useful.
So MMDDYYYY would be 07012024 right now and
day+ '/' + month+ '/' + year
would be 01/07/2024.
I run this code initially when a page gets loaded and the variables are global variables. Hence I can use "MMDDYYYY", "month", "day" and "year" throughout all of my code without thinking about them.
This one got me jump started a couple of years ago. Great teacher!
https://www.udemy.com/course/the-complete-web-developer-course-2/?couponCode=LETSLEARNNOWPP
Thanks rf1234 for this date example, but I work with dates in the English format YYYY-mm-dd.
I'm going to look into training very seriously, but in the meantime, I really need to get this
dependent()working.Here I am, a lot of fruitless search time to find out how to pass javascript variables to PHP.
That's why I was thinking of converting my PHP functions to Javascript.
But in both cases I can't find it.
I'm continuing my research into adapting PHP functions to javascript.
That would be
using my code posted above.
If you need to retrieve values from the server in your "dependent" statement you can not simply embed php code. You need to make an ajax call to the server and get the information from there.
Here is a very old example from my own coding:
I am getting the default IBAN of a department if it isn't filled yet in the contract data. Ajax calls are asynchronous by default. Hence I can only set the field "contract.iban" once the call will have been completed.
Take a look at the "url": I call a server script that gets the IBAN for me and returns it to the client.
This code is part of "actions.php". Based on the value of the get variable it calls a script "getIban" that retrieves the IBAN from the database and returns the value to the client.
P.S.: I learned all of this in the class I recommended above ... You will need training - even for this seemingly "simple" task, I am afraid.
In the meantime of course there are alternatives to ajax calls. You could also use this for example:
https://medium.com/@sagarkumar2499/best-alternatives-to-ajax-dee210d71ed7#:~:text=Fetch%20API%3A%20The%20Fetch%20API,interface%20for%20making%20network%20requests.
I personally stick to jQuery and ajax-calls. Why change a winning team?
Hello rf1234,
Thank you for all this information, I'm going to have a good look at it while I'm on holiday and I'll come back to post if I get stuck somewhere.
Thanks again for your help.
Hello,
Ma dependent code :
I don't know if what I've written is correct, but it doesn't work. I think it's my need to query fields on 2 tables that's blocking it.
If you have an idea, I'd appreciate it.
Thanks
It looks like it should set the value for three fields. If you'd like me to take a look at it, can you link to a page showing the issue so I can use a debugger on it and hopefully offer some help.
Allan
Hello Allan,
The link is in private message.
Thank you very much for your precious help.
Hello,
After following Allan's advice (whom I thank),
I upgraded datatable 1.9 to 2.0.
I changed “this.val[]” to “this.val()”.
As a result, “this.val()” now works.
The problem I'm having now is with my “DateProchainControlesMaj()” function, which works only with manually entered variables, but doesn't work in datatables, and I don't understand why.
here's the code for my page:
Here's what I've got in the console (which runs in a loop):
Is the function correctly positioned?
if so, what can prevent the “DateProchainControlesMaj()” function from working?
Thanks for your help, I think I'm nearing the end.
Is this condition being met? In the three rows in the example you sent me, all three rows have
actifControles === "".It looks like you also has an infinite loop.
editor.field('controles.etat_controles').set(etatControles)is inside the event handler forcontroles.etat_controleschanging! So every time it sets, it will call itself.You may need to break that out into a different function. Or check the value of
etatControlescompared to the field's value at the moment, and only set if needed:Finally, I'm not sure about the syntax of using a comma to end the link for the setting of the field values:
I'd suggest using a semi-colon.
Allan
Hello Allan,
Thank you very much, everything is working fine now.
I modified a silly error in the name of the function and the name of 2 variables then I followed your advice by putting :
Here is the functional code of the depent :
Thanks Allan and rf1234 for your time and help.
Awesome - great to hear you got it working
Allan