Using "Bubble editing" to edit a field of two rows

ianbrunet

Hi!

I would like to use "Bubble editing" fields to edit a field of a row corresponding to a particular id.

Here is my table named "emails" from my database:

id name email
1 name1 email1@dn.com
2 name2 email2@dn.com

I would like to use two "Bubble editing" fields to edit both email1@dn.com and email2@dn.com.

The JS code:

    <script type="text/javascript" language="javascript">
    var editor;
 
    $(document).ready(function(){
        editor = new $.fn.dataTable.Editor({
            ajax: "../php/standalone.php",
            fields: [{
                    label: "Email1",
                    fieldInfo: "For information about email1",
                    name:  "email"
                },{
                    label:     "Email2",
                    fieldInfo: "For information about email2",
                    name:      "email"
                }
            ]
        });
     
        $('[data-editor-field]').on( 'click', function (e){
            editor.bubble( this );
        });
    });
    </script>

The standalone php file:

```
<?php

// DataTables PHP library
include( "DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

// Build our Editor instance and process the data coming from _POST
$editor = Editor::inst( $db, 'emails' )
->fields(
Field::inst( 'email' )
)
->where('courrier_entrant.sent',1)
->process( $_POST )
->json();

<?php > ``` ?>

Question 1
I am using the 'where' function to modify the email field from the row which has the id 1 but how can I distinguish if it is the Bubble editing field to modify email1 or the Bubble field to modify email2 ? How can I implement that?

Question 2
More generally, I am using Datatables several times in my web application and for each datatables, I created a php file corresponding to it. Is there a better to way to implement this? Can I gather all the php files used by the Datatables in one file?

Thank you in advance for your support.

allan

how can I distinguish if it is the Bubble editing field to modify email1 or the Bubble field to modify email2 ?

If you want to take a different action depending on the row that is being submitted, that sounds like a perfect use case for server-side events.

Can I gather all the php files used by the Datatables in one file?

If that's how you want to implement it - sure. You would just need to have some way to identify which controller is to be addressed. A query string parameter would be the normal way to do it - /api/edit?table=myTable for example.

Allan

ianbrunet

Thank you for your response.
Concerning the question 1, I read the documentation you posted and I have questions about the following code:

function logChange ( $db, $action, $id, $values ) {
    $db->insert( 'staff-log', array(
        'user'   => $_SESSION['username'],
        'action' => $action,
        'values' => json_encode( $values ),
        'row'    => $id,
        'when'   => date('c')
    ) );
}
 
Editor::inst( $db, 'staff' )
    ->fields(
        Field::inst( 'first_name' ),
        Field::inst( 'last_name' ),
        Field::inst( 'position' ),
        Field::inst( 'email' ),
        Field::inst( 'office' )
    )
    ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'create', $id, $values );
    } )
    ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
        logChange( $editor->db(), 'edit', $id, $values );
    } )
    ->on( 'postRemove', function ( $editor, $id, $values ) {
        logChange( $editor->db(), 'delete', $id, $values );
    } )
    ->process( $_POST )
    ->json();

I suppose the $id, $values used in the functions are received from the Javascript code included in the HTML page.
But how do you send the variables from the JavaScript, only by the get methods?:

...
ajax: "../process.php?id=1&values=2"
...

If yes:
- How you define the $id value corresponding to the field you are editing? In other words, how do you define the following: if $id==1, it is the email1 field that was edited, if $id==2, it corresponds to the email2 ?
- Is there an other way to send the variables from the HTML file containing the Javascript using the POST method?

I am sorry about my questions, I have surely misunderstood something...

allan

• How you define the $id value corresponding to the field you are editing?

Editor will do that for you. It includes the primary key value in the data submitted to the server and the server will act upon that. That is how it knows which row to edit in the database!

Is there an other way to send the variables from the HTML file containing the Javascript using the POST method?

I don't really understand the question I'm afraid. What's wrong with POST? You could use PUT, DELETE or any other HTTP verb you want if needed for your server.

Allan

ianbrunet

Thank you for your help again. It is helping me a lot.

ianbrunet

I try this:

JS

    <script type="text/javascript" language="javascript">
    var editor;
 
    $(document).ready(function(){
        editor = new $.fn.dataTable.Editor({
            ajax: "{{ asset('vendor/Editor-PHP/php/autres_emails.php') }}",
            fields: [{
                    label: "Email 1",
                    fieldInfo: "Information about email1",
                    name:  "email1"
                },{
                    label:     "Email 2",
                    fieldInfo: "Information about email2",
                    name:      "email2"
                }
            ]
        });
     
        $('[data-editor-field]').on( 'click', function (e){
            editor.bubble(this);
        });
    });
    </script>

PHP

```
<?php

// DataTables PHP library
include( "DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

function emailChange($db, $email, $where ){
$db->update('emails', array(
'email' => $email
),$where);
}

$editor = Editor::inst($db, 'emails')
->on('postEdit', function ( $editor, $id){
emailChange($editor->db(), $_POST['data']['keyless']['email1'], $editor->where('emails.id', 1));
})
->process($_POST)
->json();

<?php > ``` ?>

It almost works! When I write into the field corresponding to the email1, the 'email' column in my database is filled for both email1 (id = 1) and email2 (id = 2) and not only for email1 what it should be expected.
I have the following error message after clicking on the Update button:

A system error has occurred (More information).

I tried to debug it with my browser which indicates:

<br />
<b>Warning</b>: Illegal offset type in <b>C:\xampp\htdocs\Gec\web\vendor\Editor-PHP\php\Database\Query.php</b> on line <b>1021</b><br />
{"data":[]}

I do not see where is the problem...

P.S.
I try to get additionnal information from:

https://editor.datatables.net/docs/1.5.5/php/class-DataTables.Database.html#_update

The link to Query::set and Query::where seems to be erroneous.

Thank you in advance for your response

ianbrunet

I found the mistake, it was:

emailChange($editor->db(), $_POST['data']['keyless']['email1'], array('id' => 1));

instead.

Now, I have to find how to read and display it from the database.

allan

Thanks for posting back. Good to hear you have it working now.

Allan

ianbrunet

Hi Allan!

Before loading the real data from my database, I am trying to write something in the standalone field but I have some difficulties to display the simple "test" string in the field.

Here is the code:

TWIG/HTML file:

{% extends "::layout.html.twig" %}


{% block stylesheets %}
    {{ parent() }}
    <!-- DataTables Editor CSS: design for beautiful tables with pages -->
    <link href="{{ asset('vendor/Editor-PHP/css/jquery.dataTables.min.css') }}" rel="stylesheet">
    <link href="{{ asset('vendor/Editor-PHP/css/buttons.dataTables.min.css') }}" rel="stylesheet">
    <link href="{{ asset('vendor/Editor-PHP/css/select.dataTables.min.css') }}" rel="stylesheet">
    <link href="{{ asset('vendor/Editor-PHP/css/editor.dataTables.min.css') }}" rel="stylesheet">
    <style type="text/css" class="init">
        dt { margin-top: 1em; }
        dt:first-child { margin-top: 0; }
        dd { width: 25% }

        *[data-editor-field] {
            border: 1px dashed #ccc;
            padding: 0.5em;
            margin: 0.5em;
        }

        *[data-editor-field]:hover {
            background: #f3f3f3;
            border: 1px dashed #333;
        }
    </style>
{% endblock %}


{% block wrapper%}
    <div id="page-wrapper">
        <div class="row">
            <div class="col-lg-12">
                <div class="panel panel-default">
                    <!-- /.panel-heading -->
                    <div class="panel-body">                    
                
                        <section>                       
                            <fieldset>
                                <legend>Emails</legend>
                                <dl>
                                    <dt>This is the email 1</dt>
                                    <dd div="email1" data-editor-field="email1"></dd>
                                    <dt>This is the email 2</dt>
                                    <dd div="email2" data-editor-field="email2"></dd>
                                </dl>
                            </fieldset>
                            <input type="button" id="display" value="Display All Data" />
                            <div id="responsecontainer" align="center">

                            </div>
                        </section>
                    </div>
                    <!-- /.panel-body -->
                </div>
                <!-- /.panel -->
            </div>
            <!-- /.col-lg-12 -->
        </div>      
    </div>
    <!-- /#page-wrapper -->
{% endblock %}


{% block javascripts %}

    {{ parent() }}
    
    <!-- DataTables Editor JavaScript: design for beautiful tables with pages -->
    <script type="text/javascript" language="javascript" src="{{ asset('vendor/Editor-PHP/js/jquery.dataTables.min.js') }}"></script>
    <script type="text/javascript" language="javascript" src="{{ asset('dist/js/jqueryui/1.12.1/jquery-ui.min.js') }}"></script>
    <script type="text/javascript" language="javascript" src="{{ asset('vendor/Editor-PHP/js/dataTables.editor.min.js') }}"></script>
    <script type="text/javascript" language="javascript" src="{{ asset('vendor/Editor-PHP/js/dataTables.buttons.min.js') }}"></script>
    <script type="text/javascript" language="javascript" src="{{ asset('vendor/Editor-PHP/js/dataTables.select.min.js') }}"></script>

    
    <script type="text/javascript" language="javascript">
        var editor;
     
        $(document).ready(function(){
            editor = new $.fn.dataTable.Editor({
                serverside: true,
                ajax: "{{ asset('vendor/Editor-PHP/php/autres_emails.php') }}",
                fields: [{
                        label: "Email 1",
                        fieldInfo: "The information message for email 1",
                        name:  "email1"
                    },{
                        label:     "Email 2",
                        fieldInfo: "The information message for email 2",
                        name:      "email2"
                    }
                ]
            });
         
            $('[data-editor-field]').on( 'click', function (e){
                editor.bubble(this);
            });
            
        });
        
        $(document).ready(function(){
            $("#display").click(function(){
                $.ajax({
                    type: 'post',
                    url: "{{ asset('vendor/Editor-PHP/php/autres_emails.php') }}",
                    dataType: 'json',
                    success: function(json){
                        $("#email1").html('test');
                    }
                });
            });
        });
    </script>
    
{% endblock %}

The PHP file:

```
<?php

// DataTables PHP library
include( "DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

function emailChange($db, $email, $where ){
$db->update('emails', array(
'email' => $email
),$where);
}

/*
$editor = Editor::inst($db, 'emails')
->on('postEdit', function ( $editor, $id){
emailChange($editor->db(), $_POST['data']['keyless']['email1'], array('id' => 1));
})
->process($_POST)
->json();
*/

$link = mysqli_connect("localhost", "root", "", "gec");

if (mysqli_connect_errno()) {
printf("Échec de la connexion : %s\n", mysqli_connect_error());
exit();
}

$query = mysqli_query($link, "SELECT * FROM emails");

while ($row = mysqli_fetch_array($query)){

$name[] = $row["name"];
$email[] = $row["email"];

}

mysqli_close($link);

echo json_encode(array(
"data" => array( "test" )
));

// Build our Editor instance and process the data coming from _POST
/*
$editor = Editor::inst( $db, 'emails' )
->fields(
Field::inst( 'name' ),
Field::inst( 'email' )
)
->on( 'postEdit',
$editor->where('emails.id',$id)
)
->process($_POST)
->json();*/

<?php > ``` ?>

I cannot display the "Test" string in the standalone field when clicking on the "Display All Data" button. Why??

It works if I put the text inside #responsecontainer but not in the standalone field???

allan

<dd div="email1"

Should be: <dd id="email1". Likewise with the email2 field.

Allan

ianbrunet

Thank you very much Allan. The mistake was in front of my eyes but I did not see it. Thank you for your great work and help!

ianbrunet

When I refresh the page, the data from the two "email" fields are read from the database. But I want to update a change of one of the two fields, I have the following error message: "A system error has occured". Nevertheless, the new value is well written in the database. I must refresh the page to display the new value that has been written.

I tried to debug it with my browser but I do not have an error message in the xhr response.

Twig/HTML file

{% extends "::layout.html.twig" %}
 
 
{% block stylesheets %}
    {{ parent() }}
    <!-- DataTables Editor CSS: design for beautiful tables with pages -->
    <link href="{{ asset('vendor/Editor-PHP/css/jquery.dataTables.min.css') }}" rel="stylesheet">
    <link href="{{ asset('vendor/Editor-PHP/css/buttons.dataTables.min.css') }}" rel="stylesheet">
    <link href="{{ asset('vendor/Editor-PHP/css/select.dataTables.min.css') }}" rel="stylesheet">
    <link href="{{ asset('vendor/Editor-PHP/css/editor.dataTables.min.css') }}" rel="stylesheet">
    <style type="text/css" class="init">
        dt { margin-top: 1em; }
        dt:first-child { margin-top: 0; }
        dd { width: 25% }
 
        *[data-editor-field] {
            border: 1px dashed #ccc;
            padding: 0.5em;
            margin: 0.5em;
        }
 
        *[data-editor-field]:hover {
            background: #f3f3f3;
            border: 1px dashed #333;
        }
    </style>
{% endblock %}
 
 
{% block wrapper%}
    <div id="page-wrapper">
        <div class="row">
            <div class="col-lg-12">
                <div class="panel panel-default">
                    <!-- /.panel-heading -->
                    <div class="panel-body">                   
                 
                        <section>                      
                            <fieldset>
                                <legend>Emails</legend>
                                <dl>
                                    <dt>This is the email 1</dt>
                                    <dd div="email1" data-editor-field="email1"></dd>
                                    <dt>This is the email 2</dt>
                                    <dd id="email2" data-editor-field="email2"></dd>
                                </dl>
                            </fieldset>
                        </section>
                    </div>
                    <!-- /.panel-body -->
                </div>
                <!-- /.panel -->
            </div>
            <!-- /.col-lg-12 -->
        </div>     
    </div>
    <!-- /#page-wrapper -->
{% endblock %}
 
 
{% block javascripts %}
 
    {{ parent() }}
     
    <!-- DataTables Editor JavaScript: design for beautiful tables with pages -->
    <script type="text/javascript" language="javascript" src="{{ asset('vendor/Editor-PHP/js/jquery.dataTables.min.js') }}"></script>
    <script type="text/javascript" language="javascript" src="{{ asset('dist/js/jqueryui/1.12.1/jquery-ui.min.js') }}"></script>
    <script type="text/javascript" language="javascript" src="{{ asset('vendor/Editor-PHP/js/dataTables.editor.min.js') }}"></script>
    <script type="text/javascript" language="javascript" src="{{ asset('vendor/Editor-PHP/js/dataTables.buttons.min.js') }}"></script>
    <script type="text/javascript" language="javascript" src="{{ asset('vendor/Editor-PHP/js/dataTables.select.min.js') }}"></script>
 
     
    <script type="text/javascript" language="javascript">
        var editor;
      
        $(document).ready(function(){
            editor = new $.fn.dataTable.Editor({
                serverside: true,
                ajax: "{{ asset('vendor/Editor-PHP/php/autres_emails.php') }}",
                fields: [{
                        label: "Email 1",
                        fieldInfo: "The information message for email 1",
                        name:  "email1"
                    },{
                        label:     "Email 2",
                        fieldInfo: "The information message for email 2",
                        name:      "email2"
                    }
                ]
            });
          
            $('[data-editor-field]').on( 'click', function (e){
                editor.bubble(this);
            });
             
        });
         
       $(document).ready(function(){
            $.ajax({
                type: 'post',
                url: "{{ asset('vendor/Editor-PHP/php/autres_emails.php') }}",
                dataType: 'json',
                success: function(json){
                    $("#email1").text(json.data[0]['email']);
                    $("#email2").text(json.data[1]['email']);                       
                }
            });
        });
    </script>
     
{% endblock %}

PHP file
```
<?php

// DataTables PHP library
include( "DataTables.php" );

// Alias Editor classes so they are easy to use
use
DataTables\Editor,
DataTables\Editor\Field,
DataTables\Editor\Format,
DataTables\Editor\Mjoin,
DataTables\Editor\Options,
DataTables\Editor\Upload,
DataTables\Editor\Validate;

function emailChange($db, $email, $where){
$db->update('emails', array(
'email' => $email
),$where);
}

$link = mysqli_connect("localhost", "root", "", "gec");

if (mysqli_connect_errno()) {
printf("Échec de la connexion: %s\n", mysqli_connect_error());
exit();
}

$query = mysqli_query($link, "SELECT * FROM emails");

while ($row = mysqli_fetch_array($query)){
$email[] = $row["email"];
}

mysqli_close($link);

$editor = Editor::inst($db, 'emails')
->fields(
Field::inst('email')
)
->on('preEdit', function ($editor, $id, $values){
if(isset($_POST['data']['keyless']['email1'])){
emailChange($editor->db(), $_POST['data']['keyless']['email1'], array('id' => 1));
echo json_encode(array("data" => $_POST['data']['keyless']['email1']));
}
if(isset($_POST['data']['keyless']['email2'])){
emailChange($editor->db(), $_POST['data']['keyless']['email2'], array('id' => 2));
echo json_encode(array("data" => $_POST['data']['keyless']['email2']));
}
})
->process($_POST)
->json();

<?php > ``` Thank you in advance for your response ?>

allan

I tried to debug it with my browser but I do not have an error message in the xhr response.

What does it return? If the "system error" message is being shown, then the server isn't returning valid JSON.

Allan

ianbrunet

For example, when I modify the email1 field, it returns:

{"data":[{"DT_RowId":"row_1","email":"test@ndd.fr"}]}{"data":[]}

I joined a screenshot of the response.

Do I have to change the name of the fields? Is there a specific format in the JSON response?

I do not understand why there is an empty response {"data":[]} that is sent...

allan

Yeah - that final {"data":[]} is what is causing the issue - that makes it invalid JSON.

I think the problem is that you have JSON being echoed out in the preEdit handler and also the Editor->json() method. Together you would get invalid JSON.

I'm not 100% sure what the best way of handling this is - the Editor PHP libraries weren't designed for keyless (i.e. non-row based) editing.

Perhaps you could simply not call the Editor->json() method when an edit is done:

```php
$editor = Editor::inst($db, 'emails')
->fields(
Field::inst('email')
)
->on('preEdit', function ($editor, $id, $values){
if(isset($_POST['data']['keyless']['email1'])){
emailChange($editor->db(), $_POST['data']['keyless']['email1'], array('id' => 1));
echo json_encode(array("data" => $_POST['data']['keyless']['email1']));
}
if(isset($_POST['data']['keyless']['email2'])){
emailChange($editor->db(), $_POST['data']['keyless']['email2'], array('id' => 2));
echo json_encode(array("data" => $_POST['data']['keyless']['email2']));
}
})
->process($_POST);

if ( $_POST['action'] !== 'edit' ) {
$editor->json();
}

<?php > ``` ?>

Allan