Querying a lot of MySQL and PHP data

The problem is exactly what you mentioned. Multiple connections.

This happens because you make these connections within loops . Below I have exemplified, with your own code, how to fix this, because you are performing queries and insertions within loops 4 times. It will not be quick to fix everything. But understanding the logic, it makes it much easier.

I took the checkDiff() function to exemplify. I did not want to change your comments so the comments in UPPERCASE are mine.

The logic is simple:

• I will create a single string, concatenating the values inside the loop, to perform the insertion later.
• Out of the loop I will connect and insert the data.

See:

function checkDiff($con, &$isinlog){
    // echo '<script>console.log("**** START CHECKDIFF ****")</script>';
    $databArray = array(); // database array
    $sql = "SELECT comName FROM vpn";

    $query = mysqli_query($con, $sql);

    while ($result = mysqli_fetch_array($query)){

        array_push($databArray, $result['comName']);

    }

    // COLOQUEI A PRIMEIRA PARTE DA STRING FORA DO FOREACH

    $sql = "UPDATE vpn SET connectedOr = 'false' WHERE ";

    // AGORA VAMOS CONCATENAR  A STRING PARA FAZER A INSERÇÃO DE UMA VEZ SÓ

    foreach($databArray as $newArray){

        if(!in_array($newArray, $isinlog)){
          // echo '<script>console.log("database ok, log off...")</script>';
          // AQUI É REALIZADA A CONCATENAÇÃO
          $sql .= " comName =   '{$newArray}' OR"; // tem no bd mas não tem no log. ou seja, desconectado.

        }

    }

    // VAMOS RETIRAR O ULTIMO ''OR'' PARA NÃO HAVER BUGS
    $sql = substr($sql, 0, -2);


    // REPARE QUE EU TIREI A QUERY DO FOREACH (AGORA A CONEXÃO SERÁ EXECUTA UMA VEZ)

    $query = mysqli_query($con, $sql);
    // echo '<script>console.log("**** END CHECKDIFF****")</script>'; 
}

On all other connections you will have to do the same thing. Only the strings will be different.

In the first case we use OR to make a single UPDATE . But in the case of INSERT we will use a comma to delimit the values.

Example:

Multiple insert:

INSERT INTO 'usuarios' ('id', 'nome') VALUES (0,'Cesar'),(0, 'Andrei')

See, from now on, you should always create your scripts in this way. In addition to avoiding these problems your code will be much faster.

I hope this helps you.

Well, I do not know if I understood your problem well! But I saw two points:

1) The delay to display the logs on the screen! Well I've had problems with rendering many records, because it really crashes! Until rendering 6,000 records in the gift and via JS, it really takes a lot! The alternative is to leave paging in php. I particularly do not do any application without the doctrine, the paging system is great!

Paging with PHP - Pagination with Doctrine

2) The problem of many connections, so I understood your code whenever some action, you already run in the bank. Even within a foreach!

foreach($databArray as $newArray){
 if(!in_array($newArray, $isinlog)){
  // echo '<script>console.log("database ok, log off...")</script>';
  $sql = "UPDATE vpn SET connectedOr = 'false' WHERE  comName =    '{$newArray}'"; // tem no bd mas não tem no log. ou seja, desconectado.
$query = mysqli_query($con, $sql);
}
}

An alternative would be to concatenate the queries and then execute them in a single request:

$sql = '';
foreach($databArray as $newArray){
 if(!in_array($newArray, $isinlog)){
  // echo '<script>console.log("database ok, log off...")</script>';
  $sql .= "UPDATE vpn SET connectedOr = 'false' WHERE  comName =    '{$newArray}'; "; // tem no bd mas não tem no log. ou seja, desconectado.
}
}

and to execute this $ sql, you can do this: mysqli multiple