use $_GET variable on Editor::inst( $db, $sql_table, 'ID' )

fez226 · November 2019

Hello gurus, i need some help:

I have a variable on my URL call 'table' how i can passes to the local variable $sql_table for using on

Editor::inst( $db, $sql_table, 'ID' )

i try with: $sql_table = $_GET['table']; bus doesnt work: it bring me this error:

DataTables warning: table id=mant_dm - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1

how i can use $_GET??

my code:

<?php

// DataTables PHP library and database connection
include( "lib/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,
    DataTables\Editor\ValidateOptions;

    //$sql_tabla = $_GET['table'];
    //$sql_tabla = 'test_table';
    $sql_tabla = $db->quote( $_GET['table']

Editor::inst( $db, $sql_tabla, 'ID' )
    ->fields(
        Field::inst( 'Field1' ),
        Field::inst( 'Field2' )
    )
    ->process( $_POST )
    ->json();

allan · November 2019

<?php
 
// DataTables PHP library and database connection
include( "lib/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,
    DataTables\Editor\ValidateOptions;
 
$sql_tabla = $db->quote( $_GET['table'] );
 
Editor::inst( $db, $sql_tabla, 'ID' )
    ->fields(
        Field::inst( 'Field1' ),
        Field::inst( 'Field2' )
    )
    ->process( $_POST )
    ->json();

Should do it. If it doesn't, please show me the response from the server (the info in the tech note the error links to shows how to do that).

Be very sure you validate the table parameter to only tables you want people to be able to read with this method!!!!

Allan

fez226 · November 2019

Hello @allan thank you but not luck... the message of error was:

"DataTables warning: table id=mant_dm - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1"

this is my url and the table "usuarios_dm" exist on my db

fez226 · November 2019

@allan on the network panel at the time i "inspect" the page, show me:

Notice: Undefined index: table in C:\xxxxxx\php\table.mant_dm.php on line 23
{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'datos_maestros.''' doesn't exist","data":[],"ipOpts":[],"cancelled":[]}

colin · November 2019

Have you diagnosed it at all? The error is saying table/view datos_maestros doesn't exist? Does it? Is it a typo or permission error? What steps have you done so far?

fez226 · November 2019

hello @colin no. my table doesnt call it "datos_maestros"... thats the name of my Data Base.... my table names is "usuarios_dm".

and both exist and i have the permission to see it. IT works if a put the table plane on the code : $sql_tabla = 'usuarios_dm';

but when i use $sql_tabla = $db->quote( $_GET['table'] ); doesnt work

fez226 · November 2019

@allan @colin i think i understand the error

the url of the server script its different as you can see up, i think this is why the $_GET variable doesnt work

i try with this code:

$urlo = $_SERVER['REQUEST_URI'];
$urlarray = explode("=",$urlo);
$sql_tabla = end($urlarray);
    
Editor::inst( $db, $sql_tabla, 'ID' )

and now the error is about SQL error: "An SQL error occurred: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'datos_maestros.1573570916699' doesn't exist"

allan · November 2019

The timestamp is the jQuery anti-cache parameter for GET Ajax requests.

I don't see your table name at all in the URL above?

Allan

fez226 · November 2019

@allan no that not my URL of my page.. thats the url of the server script on the network panel when i inspect it.

this is my main URL (wich contain the Table and the TR)

fez226 · November 2019

in any case the cuestion is how can i update a SQL table dynamically??? with localstorage of javascript? $_GET of PHP? or maybe i create the table with php on the main page... and the editor only is for change not for create the tbody element... thank u for all the help.

allan · November 2019

You've got a space before the table parameter name in your url. That might well be what is messing this up (although its hard to be certain without a link to the page).

how can i update a SQL table dynamically?

rows().remove() and row.add() can be used to remove and add rows respectively.

Allan

fez226 · November 2019

hello @allan thank u for your help. you are right, i remove the space and the problem persist. I will explain to you my web page (sorry for my english and my messy code):

i have my first file: file_one.php

this PHP file bring me all the tables of my data base (datos_maestros):

<?php
$sql = "SHOW TABLES FROM datos_maestros";
$handle = $conexion->prepare($sql);
$handle->execute(); 
$tables = $handle->fetchAll(PDO::FETCH_NUM);

<?php
>
```
i put all the tables on `````` element inside a ``````
```php
<?php
echo "<table>
   echo "";
     foreach($tables as $table){
    echo "";
    echo "";
    echo "".$table[0].""; 
    echo "";
    }
    echo "";
echo "";
>
```
so i have a script that redirect to the second file when the user click on the `````` element before
?>


```javascript
<script type="text/javascript"> 
    function my_table(elem)
    {   
        var mytable = elem.textContent;
        window.location.href = "ventana_mant.php?table=" + mytable;
    }
</script>

so far everything works like magic... here is the problem

the file ventana_mant.php its the one that contain the code of the generator of https://editor.datatables.net/generator/index

as you can see below, i use the $_GET['table'] without problem

<!doctype html>
<html>
    <head>
        <meta http-equiv="content-type" content="text/html; charset=utf-8" />
        
        <title>my title</title>

        <link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/dt/jqc-1.12.4/moment-2.18.1/dt-1.10.20/b-1.6.1/sl-1.3.1/datatables.min.css">
        <link rel="stylesheet" type="text/css" href="css/generator-base.css">
        <link rel="stylesheet" type="text/css" href="css/editor.dataTables.min.css">

        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/dt/jqc-1.12.4/moment-2.18.1/dt-1.10.20/b-1.6.1/sl-1.3.1/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/table.mant_dm.js"></script>
    </head>
    <body class="dataTables">
        
        <div class="container">

            <h3>
                <?php
                echo "Table editor: <strong>".$_GET['table']."</strong>";
                ?>
            </h3>
            
            <table cellpadding="0" cellspacing="0" border="0" class="display" id="mant_dm" width="100%">
                <thead>
                    <tr>
                        <th>Value</th>
                        <th>Key</th>
                    </tr>
                </thead>
            </table>

        </div>
    </body>
</html>

and finally the file table.mant_dm.js contain the javascript for var table = $('#mant_dm').DataTable( { and

var editor = new $.fn.dataTable.Editor( {
            ajax: 'php/table.mant_dm.php',
            table: '#mant_dm',
            fields: [

etcetera...

anda finally the file table.mant_dm.php its that contain this code (and the error for beginning)

$sql_tabla = $db->quote( $_GET['table'] );
Editor::inst( $db, $sql_tabla, 'ID' )
    ->fields(
        Field::inst( 'Field1' ),
        Field::inst( 'Field2' )
    )
    ->process( $_POST )
    ->json();

thank you for your patience and help... So thats my problem i want to update table of my database dynamically... if you know another way PLEASE help me. i want to use the editor of datatables.net but i have this problem

allan · November 2019

Okay - so you have the table as a query parameter for your host page, but you need access to it from the Javascript as well.

One option would be to write it into a Javascript variable:

<script type="text/javascript" charset="utf-8">
  var _table = <?=json_encode($_GET['table']);?>;
</script>

Then you can use the ajax.data option with that parameter (note you should put that above the script include for your table.mant_dm.js file):

ajax: {
  url: ...,
  data: function (d) {
    d.table = _table;
  }
}

I mentioned before, but it is worth highlighting again, this approach has very significant security implications. This should only ever be used for an internal tool where no potentially bad actor could use it.

Allan

fez226 · November 2019

oh thank @allan i put the script on the ventana_mant.php file and i change the ajax on the table.mant_dm.js and the server script file table.mant_dm.php its still the same?

<script type="text/javascript" charset="utf-8">
            var _table = <?=json_encode($_GET['table']);?>;
        </script>

        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/v/dt/jqc-1.12.4/moment-2.18.1/dt-1.10.20/b-1.6.1/sl-1.3.1/datatables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/dataTables.editor.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="js/table.mant_dm.js"></script>

var editor = new $.fn.dataTable.Editor( {
            
            ajax: {
                url: 'php/table.mant_dm.php',
                data: function (d) {
                  d.table = _table;
                }
              },
            table: '#mant_dm',

$sql_tabla = $db->quote( $_GET['table'] );

Editor::inst( $db, $sql_tabla, 'ID' )
    ->fields(
        Field::inst( 'Usuarios' ),
        Field::inst( 'Password' )
    )
    ->process( $_POST )
    ->json();

i bring me another error:

DataTables warning: table id=mant_dm - An SQL error occurred: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'datos_maestros.'usuarios_dm'' doesn't exist

(mant_dm its the id of my

<

table> element)

but the only think i can see different its the double quote at the end of usuarios_dm''

what could be the error?

fez226 · November 2019

@allan i figure it out:

$sql_tabla = $db->quote( $_GET['table'] );
$sql_tabla = str_replace("'", "", $sql_tabla);

but now i can´t update a value of a table because its bring me an error:

Notice: Undefined index: table in C:\xxxxx\php\table.mant_dm.php on line 23
{"fieldErrors":[],"error":"An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name ''","data":[],"ipOpts":[],"cancelled":[]}

the line 23 of my code is: $sql_tabla = $db->quote( $_GET['table'] );

allan · November 2019

Incorrect table name ''

That suggests that $sql_tabla is just an empty string. Have a look at your browser's network inspector to make sure that a table parameter really is being added to the query string for the php/table.mant_dm.php request.

Looking at the code above, I see ajax.data being used for Editor, but ajax.data for DataTables? Its not clear from the above if you are using that or not since the DataTables code isn't there.

Allan

fez226 · November 2019

Hello @allan yeah im using ajax.data for DataTables. thats because i dont understand the variable $sql_tabla it cannot be empty, because this line of code works for filling the table (but not to update, insert or delete) $sql_tabla = $db->quote( $_GET['table'] );

this all the code for the table.mant_dm.js

(function($){

$(document).ready(function() 
    {

        var editor = new $.fn.dataTable.Editor( {
            ajax: {
                url: 'php/table.mant_dm.php',
                data: function (d) {
                  d.table = _table;
                }
              },
            table: '#mant_dm',
            fields: [
                {
                    "label": "field1",
                    "name": "field1
                },
                {
                    "label": "field2",
                    "name": "field2"
                }
            ]
        } );

        var table = $('#mant_dm').DataTable( {
            
            dom: 'Bfrtip',
            ajax: {
                url: 'php/table.mant_dm.php',
                data: function (d) {
                  d.table = _table;
                }
              },            

            columns: [
                {
                    "data": "field1"
                },
                {
                    "data": "field2"
                }
            ],
            select: true,
            buttons: [
                { extend: 'create', editor: editor },
                { extend: 'edit',   editor: editor },
                { extend: 'remove', editor: editor }
            ],

        } );
    } 
);

}(jQuery));

as you can see below, the image of network panel: for the main page and the table.mant_dm.php the variable table its good.... but for the second request (i try to delete a row) of table.mant_dm.php the variable is empty, i dont understand why.

allan · November 2019

AH! The problem isn't on the data fetch. But the edit?! Is that correct? I'm assuming that based on the fact that table.mant_dm.php is called twice there.

If so, that's because Editor sends a POST request by default! So $_GET['table'] correctly wouldn't have anything in it. You can still send a query string parameter with a POST request - just do this:

        var editor = new $.fn.dataTable.Editor( {
            ajax: {
                url: 'php/table.mant_dm.php?table=' + _table,
              },

Allan

p.s. I know I've said it before, but you've not acknowledged the potential security implications here. Please do not deploy this on the web. Keep it local only if you must do this. Allowing the table name to be submitted has very real security implications.

fez226 · November 2019

@allan everything works like magic. Thank u for all the help and let me understand better ajax and php... i understand your security worry but the web page is for internal using only... with the security right and VPN connection only. Cheers

fez226 · November 2019

@allan its to hard to change the editor to excel spreadsheet mode? i look up the documentation but i can´t understand.

https://editor.datatables.net/examples/extensions/keyTable.html

colin · November 2019

i look up the documentation but i can´t understand.

The important section in the example you posted is the KeyTable extension,

        keys: {
            columns: ':not(:first-child)',
            editor:  editor
        },

If you could be more specific on what the issue is, may be we could help.

fez226 · November 2019

sure @colin i put all the reference of the javascript on the main page ventana_mant.php:

<script type="text/javascript" charset="utf-8" src="https://code.jquery.com/jquery-3.3.1.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/buttons/1.6.1/js/dataTables.buttons.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/select/1.3.1/js/dataTables.select.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://cdn.datatables.net/keytable/2.5.1/js/dataTables.keyTable.min.js"></script>
        <script type="text/javascript" charset="utf-8" src="https://editor.datatables.net/extensions/Editor/js/dataTables.editor.min.js"></script>

and the table.mant_dm.js have the code that you mencion

(function($){

$(document).ready(function() 
    {

        var editor = new $.fn.dataTable.Editor( {
            ajax: {
                url: 'php/table.mant_dm.php?table=' + _table,
              },
            table: '#mant_dm',
            fields: [
                {
                    "label": "Value:",
                    "name": "field1"
                },
                {
                    "label": "Valor:",
                    "name": "field2"
                }
            ]
        } );

        var table = $('#mant_dm').DataTable( {
            keys: true,
            dom: 'Bfrtip',
            ajax: {
                url: 'php/table.mant_dm.php',
                data: function (e) {
                  e.table = _table;
                }
              },            

            columns: [
                {
                    "data": "field1"
                },
                {
                    "data": "field2"
                }
            ],
            keys: {
                columns: ':not(:first-child)',
                editor:  editor
            },
            select: {
                style:    'os',
                selector: 'td:first-child',
                blurable: true
            },
            buttons: [
                { extend: 'create', editor: editor },
                { extend: 'edit',   editor: editor },
                { extend: 'remove', editor: editor }
            ],

        } );
    } 
);

}(jQuery));

colin · November 2019

You've defined the keys object twice - on lines 24 and 41 - try removing the one on line 24.

fez226 · November 2019

i remove the line 24 but nothing happens, i can edit, insert and delete with the buttoms... but i cant do the other extension

kthorngren · November 2019

Are you able to edit the second column?

You have:

            keys: {
                columns: ':not(:first-child)',
                editor:  editor
            },

Which will not allow editing in the first column. See this example which is similar to yours:
http://live.datatables.net/vivumuna/1/edit

You won't be able to edit in the first column. If you remove columns: ':not(:first-child)', then you will be able to edit the first column.

Maybe this example will help:
https://editor.datatables.net/examples/inline-editing/tabControl.html

If this doesn't help then we will need a link to your page or a test case showing the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

fez226 · November 2019

yes i can edit all two columns with the buttons, but the key select doesnt work, i put all the include on my html file but at end if i a put it above the <script type="text/javascript" charset="utf-8" src="js/table.mant_dm.js"></script> doesnt work the keys, and i a put it at the bottom the data of the table doesnt show.

this link https://editor.datatables.net/examples/inline-editing/tabControl.html works great but i dont know how to put it on my web page. I load all the JS, and change mi JS file... but nothing happens

kthorngren · November 2019

Without being able to see the page it will be hard to diagnose. Can you post a link to your page or a test case replicating the problem?

You can try the Debugger and post the link for the developers. I believe it will show the Datatbles modules you have loaded. Probably won't be much help beyond that though.

Kevin

use $_GET variable on Editor::inst( $db, $sql_table, 'ID' )

use $_GET variable on Editor::inst( $db, $sql_table, 'ID' )

Answers

Howdy, Stranger!

Categories

DataTables

use $_GET variable on Editor::inst( $db, $sql_table, 'ID' )

use $_GET variable on Editor::inst( $db, $sql_table, 'ID' )

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables