Render Datatables from 2 tables mysql

pettedemon

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

allan

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

pettedemon

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

allan

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

pettedemon

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>';
        }

    ),

allan

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

pettedemon

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

allan

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

pettedemon

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

allan

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

pettedemon

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

allan

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

pettedemon

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