Datatable dynamic conditional format

Lapointe

Hello all
I have a question.

A datatable (prog1) open another prog (onClick or other) that change some values in database.
After closing prog2 I'd like to reflect changes made by prog2 into datatable displayed by prog1 (as background color perhaps or else).
Is it possible ?

Lapointe

I have been to much long to edit this post.. sorry:

Hello all
I have 2 questions.

First one
Tbl2 has multiple rows connected with tbl1.ID
I need to know if in Tbl2 one (or more) row connected with Tbl1 has a particular property.
In SQL I'd write

$BoolRes = count(
    $db->sql(
        "SELECT ID from Tbl2 WHERE LinkField = IDParam and DateField > now()"
     )->fetchAll()
)>0;

and I 'd like to inst this bool value to editor (to format table at start for example)...
How to ?

Other question:
A datatable (prog1) open another prog (onClick or other) that change some values in database.
After closing prog2 I'd like to reflect changes made by prog2 into datatable displayed by prog1 (as background color perhaps or else).
In fact prog2 can add or edit a record that may change the $BoolRes of the first question...

Is it possible ?

allan

I'd like to reflect changes made by prog2 into datatable displayed by prog1 (as background color perhaps or else).

You'd need to know what rows have been effected in prog1 based on the changes. Possibly a SELECT for the row id's based on the joined ids from prog2? With that information you can then add a class to the rows / cells which have changed, but you'd need to know that information.

and I 'd like to inst this bool value to editor (to format table at start for example)...

Two options here:

1. Do to it in SQl, you'd need to create a suitable VIEW and query the data to display from that.
2. Use an Mjoin to get the records from the join and then use the length of the array to get the count. I'd probably do it that way myself.

Allan

Lapointe

Hello @allan
Thanks for your answer.
1- Yes I can know if I need to refresh display, but only using a form timer and then refresh the entire form. I don't know how to query again the database from JS...
As I said there is a month, I'd never use JS to work with databases so I'm an old newbie...
If I place a form timer starting a function each xxx seconds, how to scan again database without refreshing the whole page, just changing background color if needed ?

2- In SQL it's not really difficult, but... in JS actual getdata is a little bit complex

    $ExcludeLotsSql="(SELECT Lot FROM options WHERE ((Date_Signature IS NOT NULL) OR (Date_Signature_Prev > Now())  OR (Validite > Now()) ))";  

....
            ->leftJoin( 'operations', 'operations.ID', '=', 'lots.Operation' )
            ->leftJoin( 'types_operations', 'types_operations.ID', '=', 'operations.Type' )
            ->leftJoin( 'types_lots', 'types_lots.ID', '=', 'lots.Type' )
            ->leftJoin( 'types_logements', 'types_logements.ID', '=', 'lots.Typologie' )
            ->leftJoin( 'niveaux', 'niveaux.ID', '=', 'lots.Niveau' )
            ->leftJoin( 'orientations', 'orientations.ID', '=', 'lots.Orientation' )
            
            ->where( function ( $q ) use ($OpIn, $ExcludeLotsSql) {
                $q->where( function($r) use ($OpIn, $ExcludeLotsSql) {
                    $r->where('lots.Actif','1','=');
                    $r->where( 'lots.Operation',$OpIn,'IN',false);
                    $r->where( 'lots.ID',$ExcludeLotsSql,'NOT IN',false);
                });
            })

So i'm afraid of result adding an mjoin to the actual query

Do you think there is no risk ?

No way to set an instance as a function(Row.id) ?

allan

I don't know how to query again the database from JS...

If you've got DataTables setup to use ajax to read the data source, then use ajax.reload() to reload the data from the server.

So i'm afraid of result adding an mjoin to the actual query

In what way? Do you mean performance or something else? Performance-wise it should be fine unless you have a really large data set. We use two queries to get the data and then combine it in PHP, rather than attempting to do one query per row to get the joined data.

Allan