2 SQL Queries

2 SQL Queries

yskapellyskapell Posts: 47Questions: 14Answers: 3

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.

Answers

  • allanallan Posts: 63,237Questions: 1Answers: 10,418 Site admin

    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 :)

  • yskapellyskapell Posts: 47Questions: 14Answers: 3

    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
    
  • allanallan Posts: 63,237Questions: 1Answers: 10,418 Site admin

    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

  • yskapellyskapell Posts: 47Questions: 14Answers: 3

    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 )
    );
    
  • allanallan Posts: 63,237Questions: 1Answers: 10,418 Site admin

    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

  • yskapellyskapell Posts: 47Questions: 14Answers: 3

    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

  • allanallan Posts: 63,237Questions: 1Answers: 10,418 Site admin

    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

  • yskapellyskapell Posts: 47Questions: 14Answers: 3

    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?

  • allanallan Posts: 63,237Questions: 1Answers: 10,418 Site admin

    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

  • yskapellyskapell Posts: 47Questions: 14Answers: 3

    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.

  • allanallan Posts: 63,237Questions: 1Answers: 10,418 Site admin

    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

Sign In or Register to comment.