2 SQL Queries

yskapell

Hello guys,

I have a question....
Can I run 2 different queries in 1 PHP file and return the data ?

To have sometthing like https://datatables.net/examples/api/row_details.html but without to read from txt file.

allan

Sure. Run as many as you want (although less will be faster of course). As long as the JSON that is returned is valid, DataTables doesn't "care" what is happening at the backend

yskapell

Hello Allan,

I use this

$table = "select s.first_name, s.last_name, e.exercises_name, e.url, e.date from students s right join $student_vocabulary_table e on e.student_id = s.username where e.new='Y'";

but it return no data instead of 7 rows.

Also if I hit the button second time I get the below error

Uncaught SyntaxError: Identifier 'table' has already been declared

allan

You are going to have to give me a bit more than that . All I see is a string which contains an SQL statement. I don't see how it is executed, the data processed and then returned.

Please link to a test case showing the issue and show the full PHP code.

Thanks,
Allan

yskapell

Hello Allan,

Test case is https://live.datatables.net/gawekelu/1/edit

PHP Code is

<?php
 
 include( "../lib/DataTables.php" );
 include ("student_vocabulary.php");
 
 $get_stu = $studentUsername;
 
 $student_vocabulary_table = $get_stu."exercises";

// DB table to use
$table = "select s.first_name, s.last_name, e.exercises_name, e.url, e.date from students s right join $student_vocabulary_table e on e.student_id = s.username where e.new='Y'";
 
// Table's primary key
$primaryKey = 'id';
 
// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier - in this case object
// parameter names
$columns = array(
    array(
        'db' => 'id',
        'dt' => 'DT_RowId',
        'formatter' => function( $d, $row ) {
            // Technically a DOM id cannot start with an integer, so we prefix
            // a string. This can also be useful if you have multiple tables
            // to ensure that the id is unique with a different prefix
            return 'row_'.$d;
        }
    ),
    array( 'db' => 'first_name', 'dt' => 'first_name' ),
    array( 'db' => 'last_name',  'dt' => 'last_name' ),
    array( 'db' => 'exercises_name',   'dt' => 'exercises_name' ),
    array( 'db' => 'url',     'dt' => 'url' ),
    array(
        'db'        => 'date',
        'dt'        => 'date',
        'formatter' => function( $d, $row ) {
            return date( 'jS M y', strtotime($d));
        }
    )
);
 
$sql_details = array(
    'user' => 'xxxx',
    'pass' => 'K@xxx!',
    'db'   => 'xxxx',
    'host' => 'zzzz'
);
 
 
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */
 
require( 'ssp.class.php' );
 
echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

allan

Thank you - I had no idea you were using the demo server-side processing class. The $table variable is intended to just be a table name, not a whole query itself. I suspect you would need to modify the SSP class if you needed to do that. I presume you've done that for the join?

You've got three options:

1. Create a VIEW and use SSP on that
2. Use the Editor libraries. They support server-side processing and are free to use.
3. Modify the SSP class to support joins.

This of course is assuming you need server-side processing (you have tens of thousands of rows of data).

Allan

yskapell

Hello Allan,

I have the editor so I will use it.

Now what about the second issue, when I click it on second time I get error about the table

allan

I'd need to see a test case demonstrating that issue. There is nothing wrong with the code you have shown on the live site.

The error suggests that var table (or let table) is being defined multiple times.

Allan

yskapell

Hello Allan,

I check the editor, but in the join one of the table is created dynamically.

Editor::inst( $db, 'students', 'id' )
    ->fields(
        Field::inst( 'students.first_name' ),
        Field::inst( 'students.last_name' ),
        Field::inst( $student_vocabulary_table.'.exercises_name' ),
        Field::inst( $student_vocabulary_table.'.url' ),
        Field::inst( $student_vocabulary_table.'.date' )
    )
    ->leftJoin( $student_vocabulary_table, $student_vocabulary_table.'.id', '=',  'students.username')
    ->process( $_POST )
    ->json();

Will it work properly? How will I pass the fileds with variable to json?

allan

As in $student_vocabulary_table would be a value posted from the client-side? Or a session variable or something else?

If you want to send the table name from the client-side use ajax.data for DataTables and ajax.data for Editor. Be very certain that you validate the submitted table name though! Otherwise you've got a sure fire security hole!

Allan

yskapell

Hello Allan,

I see that there is a security hole, thus I change it to static tables.

I have the below PHP code on which I use before and I get error on line 33

<?php
 
 include( "../lib/DataTables.php" );
 include ("student_vocabulary.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;

// The following statement can be removed after the first run (i.e. the database
// table has been created). It is a good idea to do this to help improve
// performance.
/*$db->sql( "CREATE TABLE IF NOT EXISTS \"vocabulary\" (
    \"id\" serial,
    \"ideogram\" text,
    \"pinyin\" text,
    \"type\" text,
    \"meaning\" text,
    PRIMARY KEY( id )
);" );
*/

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'students', 'id' ) #In this line I get an error PHP Fatal error:  Uncaught #Error: Class 'DataTables\\Editor' not found 
    ->fields(
        Field::inst( 'students.first_name' ),
        Field::inst( 'students.last_name' ),
        Field::inst( 'georgeekpla1temp_exercises.exercises_name' ),
        Field::inst( 'georgeekpla1temp_exercises.url' ),
        Field::inst( 'georgeekpla1temp_exercises.date' )
    )
    ->rightJoin( 'georgeekpla1temp_exercises', 'georgeekpla1temp_exercises.id', '=',  'students.username')
    ->process( $_POST )
    ->json();

Until the line which I get the error (I see it as line 33) I copy/paste it from other file which is on same directory and it work fine.

allan

Could you change:

include( "../lib/DataTables.php" );

to:

require( "../lib/DataTables.php" );

Which would just mean that if that include / require fails, then it will throw a fatal error at that point. That file not being included / required would explain the error you are seeing.

Allan