Render Datatables from 2 tables mysql

Render Datatables from 2 tables mysql

pettedemonpettedemon Posts: 38Questions: 6Answers: 0

Hi,
I want to display a table with some fields have extra queries from another table.
If I do a query inside array as

array(
        'db'   => 'codice',
        'dt'        => 'verifica_cancellata',
        'formatter' => function ($d) {


            $data_in_elaborazione = $_GET["data_in_elaborazione"];

            $connessione = mysqli_connect('localhost', 'xxxxx', 'xxxxx', 'xxxxxxx');
            $cerca_brano = 'BRANO-' . $d;
            $query_usata_storico = "select count(nome_evento) as contatore_storico from playlist_generate where nome_evento = '$cerca_brano' and data_playlist = '$data_in_elaborazione'";
            $esegui_query_usata_storico = $connessione->query($query_usata_storico);

            $risultato_conta  = $esegui_query_usata_storico->fetch_assoc();

            $risultato_contatore = $risultato_conta['contatore_storico'];
            if ($risultato_contatore == '0') {
                // colore verde
                $colore = '0';
            } else {
                //colore nero
                $colore = '1';
            }

            return $colore;
        }

    ),

it works, but the render is very slow. There is an other solution?
thanks

This question has accepted answers - jump to:

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    You are creating a MySQL connection every time that formatter is called! Never mind the query per row, that is going to seriously slow things down!

    Can you not reuse the existing PDO connection? Or is it a completely different database you are connecting to.

    If it is the same database and same user, then I'd suggest you use a VIEW rather than doing a query per row. It will be massively faster.

    Allan

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0
    edited September 2020

    Hi @allan thanks.
    the table is in the same database but the connection available in server-side processing script
    $sql_details
    is not available inside array, so how can I do? thanks

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Change function ($d) { to be:

    function ($d) use ($db) {
    

    See the PHP documentation on anonymous functions for a detailed explanation of how you can use external variables inside another function.

    Allan

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0
    edited September 2020

    Hi @allan so I declare at the top of page

    $db = new mysqli("localhost", "xxxxxx", "xxxxxx", "xxxxxxx");
    // this is the same as $sql_details
    

    and then I use inside array?

        array(
            'db'   => 'codice',
            'dt'        => 4,
            'formatter' => function ($d) use ($db) {
    
    
                $cerca_brano = 'BRANO-' . $d;
                $query_usata_storico = "select count(nome_evento) as contatore_storico from playlist_generate where nome_evento = '$cerca_brano'";
                $esegui_query_usata_storico = $db->query($query_usata_storico);
    
                $risultato_conta  = $esegui_query_usata_storico->fetch_assoc();
    
                $risultato_contatore = $risultato_conta['contatore_storico'];
                if ($risultato_contatore == '0') {
                    // colore verde
                    $colore = 'success';
                } else {
                    //colore nero
                    $colore = 'dark';
                }
    
                return '<div class="copy_contatore_storico btn btn-' . $colore . ' btn-sm">' . $risultato_contatore . '</div>';
            }
    
        ),
    
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    You could do that yes - although given that there is already a PDO database connection (since that is what our SSP class works with), I would suggest using that rather than creating a mysqli instance, which the SSP class will not work with (unless you modify it to do so).

    I'd also encourage you to look into create a VIEW in your SQL that will do that combination logic for you, rather than running it as a query per row. For example, consider the case where you are displaying 100 rows on the page. That's 101 database queries just to display a single page!

    Allan

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0

    Hi @allan , I use the standard ssp class, I see there is a function with $db but it doesn't work if I use. Where I can found a ssp modified class to do this?

    It's my problem. I have to visualize 27000 record, paginate, every record is an other query, so it is more slow. Where I can find an example to do a VIEW in my SQL and use in Datatables?
    thanks

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    Where I can found a ssp modified class to do this?

    You'd need to modify it if you wanted to use mysqli rather than PDO.

    Where I can find an example to do a VIEW in my SQL and use in Datatables?
    thanks

    From DataTables' point of view, it will treat the VIEW just like it would do any other table that you can use SELECT to get data from. So the key is to create a VIEW that does what you need. For that, you'd need to refer to your database's documentation.

    Allan

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0

    Hi @allan ,

    You'd need to modify it if you wanted to use mysqli rather than PDO.

    thanks, but how can I modify it? I have to change?
    there is anything do I can modify?
    thanks

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    This is the file and location where you would want to start making the changes to use mysqli rather than PDO.

    I don't really understand why you wouldn't use PDO though. Also I think that is a side issue compared with the larger issue of using a query per row. I'd suggest first sorting out the VIEW.

    Allan

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0
    edited September 2020

    Hi @allan ,
    I use this file

    than I declare

    // Table's primary key
    $primaryKey = 'id';
    
    
    $sql_details = array(
        'user' => 'user',
        'pass' => 'pass',
        'db'   => 'database',
        'host' => '192.168.1.128',
    );
    
    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
     * 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-pette.php');
    $db = SSP::db($sql_details);
    
    

    So I can use the same connection in array

    $cerca_brano = 'BRANO-' . $d;
    $query_usata_storico = "select count(nome_evento) as contatore_storico from playlist_generate where nome_evento = '$cerca_brano'";
    $esegui_query_usata_storico  = $db->query($query_usata_storico);
    $risultato_conta  = $esegui_query_usata_storico->fetch(PDO::FETCH_ASSOC);
    

    So, now I use only one connection. Right?
    *** edit: so it is slow too

    I try to see what a VIEW, but I think is not possible, I have a table SONGS with a list of songs details with 10 columns an 30k items, than I have a table LIST_EVERYDAY with the details where I use the things, with date and time and the song id I use.
    So it is possible to use a VIEW?
    Thanks

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    Answer ✓

    So, now I use only one connection. Right?

    Correct.

    So it is possible to use a VIEW?

    It will be, but I'm not an expert in crafting SQL statements such as that I'm afraid. You'd need to ask on StackOverflow or similar if you need help with creating such a statement.

    Allan

  • pettedemonpettedemon Posts: 38Questions: 6Answers: 0

    Hi @allan , thanks!
    my speed problem was the INDEXES in mysql table.
    I try to create a VIEW and now with INDEXES are very fast!
    Thanks for your support and suggestions, I learned new things!
    Thanks

This discussion has been closed.