Query of many Mysql and PHP data

Question

Personal the code below parses the data that is in the openvpn log and stores in the database that by other codes are shown on a table page. But that’s a lot of records in a log file! There are more than 6 thousand (and will go up with time) and this is taking a little while and ended up breaking the database with "Too Many Conections" and also to show the data in a table I use the following:

$sql = "SELECT idvpn, comName, realAddr, byS, byR, virtAddr, since,    blockstatus, connectedOr FROM vpn";
$query = mysqli_query($conexao, $sql);
return $query;

Only with this code above, it takes almost 2 minutes to display the data in a table (where there is that next page with a plugin called Datatable). Because of numerous requests - I think - I couldn’t even log on to the site anymore and the data started to be entered all broken in the database... I need urgent help, I need to know what I’m doing wrong!

Here is the code of the parser that breaks each line of the log and takes the information, checks if it already exists in the database, if there is no it adds, if there is it checks if it is in the log and in the database, if yes it updates to connected, if not it updates to disconnected.

<?php
$con = mysqli_connect("*******", "root", "*******", "*******");
set_time_limit(0);
$pathlog = "log2.txt";

ovpnParser($con, $pathlog);

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']);
}
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);
}
}
// echo '<script>console.log("**** END CHECKDIFF****")</script>'; 
}

function ovpnParser($con, $pathlog){
// echo '<script>console.log("**** START OVPN PARSER****")</script>';
$isinlog = array(); // log array

$inclients = false;
$handle = fopen($pathlog, "r");
$inclients = false;

while(!feof($handle)){
$line = fgets($handle, 4096);
if (substr($line, 0, 11) == "CLIENT_LIST"){
  if (preg_match("/CLIENT_LIST\t{1,}UNDEF(.*)UNDEF/", $line)){
    $inclients = false;
   }
   else{
    $inclients = true;
   }
  }
  if ($inclients){
  preg_match("/CLIENT_LIST(.*)UNDEF/", $line, $conteudo);
  $partes = preg_split("/\t{1,}/", trim($conteudo[1]));
  array_push($isinlog, $partes[0]);

  $sql = "SELECT comName FROM vpn WHERE  comName = '{$partes[0]}'";
  $query = mysqli_query($con, $sql);
  $rows = mysqli_num_rows($query);

  if ($rows == 0){
    // echo '<script>console.log("new register")</script>';
    $sql = "INSERT INTO vpn (comName, realAddr, virtAddr, byR, byS, since, blockstatus, connectedOr)
    VALUES ('{$partes[0]}', '{$partes[1]}', '{$partes[2]}', '{$partes[3]}', '{$partes[4]}', '{$partes[5]}', 'true', 'true')";
    $query = mysqli_query($con, $sql);
    }
  elseif ($rows > 0) {
    // echo '<script>console.log("log and database ok...")</script>';
    $sql = "UPDATE vpn SET byR = '{$partes[3]}', byS = '{$partes[4]}', connectedOr = 'true' WHERE  comName = '{$partes[0]}'";
    $query = mysqli_query($con, $sql);
  }
 }
 $inclients = false;
 }
// echo '<script>console.log("**** END OVPN PARSER ****")</script>';
checkDiff($con, $isinlog);
}

Answer 1

Well I don’t know if I understood your problem well! But I saw two points:

1) The delay to display the records on the screen! Well I’ve had problems with rendering many records because it hangs even! Until you render 6,000 records in the gift and via JS, it really takes a long time! The alternative is to leave the pagination in php. I particularly don’t do any application without Doctrine, his paging system is great!

Paging with PHP - Pagination with Doctrine

2) The problem of many connections, so I understood from your code whenever some action, you already run in the bank. Including 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 querys 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 run this $sql, you can do so: mysqli Multiple

Answer 2

The problem is exactly what you mentioned. Multiple connections.

It happens because you make these connections inside the loops. Below I exemplified, with your own code, how to fix this, because you are performing queries and inserts within loops 4 times. It will not be quick to tidy up everything. But understanding the logic, already facilitates a lot.

I took the job checkDiff() to illustrate. Didn’t want to change your comments so the comments on CAPITAL LETTERS are my.

The logic is simple:

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

Behold:

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

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

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

Example:

Multiple insertion:

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

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

I hope it helps you.