Data search in a log

Question

I’m having trouble seeing where my mistake is, and also understanding and tidy up the delay of query.

I have a function in PHP that makes the parser of a log generated by OpenVpn. Below I will leave some lines of this log for you to help me.

CLIENT_LIST cliente1    120.203.57.20:56256 120.203.57.20   172756  170025  Wed May 25 08:26:39 2016    1464175599  UNDEF
CLIENT_LIST cliente2    120.203.57.20:53266 120.203.57.20   339724  293760  Wed May 25 08:56:44 2016    1464177404  UNDEF
CLIENT_LIST cliente3    120.203.57.20:49751 120.203.57.20   1083595 945154  Tue May 24 21:36:21 2016    1464136581  UNDEF
CLIENT_LIST cliente3    120.203.57.20:55161 120.203.57.20   188704  187305  Wed May 25 07:57:48 2016    1464173868  UNDEF
CLIENT_LIST cliente4    120.203.57.20:1031  120.203.57.20   529298  521955  Wed May 25 06:30:38 2016    1464168638  UNDEF
CLIENT_LIST cliente5    120.203.57.20:30721 120.203.57.20   1208231 1049827 Tue May 24 14:37:04 2016    1464111424  UNDEF
CLIENT_LIST cliente6    120.203.57.20:63653 120.203.57.20   413439  363969  Wed May 25 07:57:25 2016    1464173845  UNDEF

The function that analyzes each row of this log and stores in a table in the database:

$log = "log.log";

ovpnParser($log, $con);

function ovpnParser($log, $con)
{
  echo '<script>console.log("testing...")</script>';
  $inclients = false;
  $handle = fopen($log, "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;
        echo '<script>console.log("client is not on the list...")</script>';
      }
      else
      {
        $inclients = true;
        echo '<script>console.log("client is in the list...")</script>';
      }
    }

    if ($inclients)
    {
      preg_match("/CLIENT_LIST(.*)UNDEF/", $line, $conteudo);

      $partes = preg_split("/\t{1,}/", trim($conteudo[1]));

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

    if ($numeroDeLinhas == 0)
    {
      $sql = "INSERT INTO vpn (comName, realAddr, virtAddr, byR, byS, since, sinstamp, blockstatus)
            VALUES ('{$partes[0]}', '{$partes[1]}', '{$partes[2]}', '{$partes[3]}', '{$partes[4]}', '{$partes[5]}', '{$partes[6]}', 'true')";

      $query = mysqli_query($con, $sql);
      echo '<script>console.log("new client added to the list...")</script>';
    }
    elseif ($numeroDeLinhas > 0)
    {
      clientcmp($conexao, $partes[0]);
    }
  }
  $inclients = false;
}
echo '<script>console.log("the test is finished...")</script>';

}

Note: I put some echo so that I follow exactly where the code is currently running.

Besides doing this parser to be able to insert into a table in the database, I need to know if the client is connected or not.

The logic is this: when executing the parser, it will check at the bank if there is a customer with that name, if there is no customer, this customer is inserted in the bank, and if the customer is present in the log, it means that he is with the vpn connected.

Now the part I’m not able to do: if the client is in the database but he’s not in that log file, that means he’s with the vpn disconnected!

This log file is updated every 1 minute, that is, this parser will run every 1 minute.

In the table in the database, I added a "connectedOr" column, if connected, I have to store true, if it is not connected, I have to store false.

The function I had done for this purpose, is this:

function clientcmp($conexao)
{
  $start = microtime(true);
  $query = mysqli_query($conexao, $sql);
  $str1 = "kg-01239918000150"; // nome do log


  $x = array();
  while($row = mysqli_fetch_assoc($query))
  {
    $x = $row['comName'];
    if ($str1 == $x)
    { // está no log e está no banco de dados
      $sql = "UPDATE vpn SET connectedOr = 'true' WHERE comName = '{$x}'";
      $queryy = mysqli_query($conexao, $sql);
      echo '<script>console.log("cliente consta no log e consta no banco de dados.")</script>';
    }
    elseif ($str1 != $x)
    {
      $sql = "UPDATE vpn SET connectedOr = 'false' WHERE comName = '{$x}'";
      $queryy = mysqli_query($conexao, $sql);
      echo '<script>console.log("cliente não consta no log e consta no banco de dados.")</script>';
    }
  }
  $time_post = microtime(true);
  $exec_time = ($time_post - $start)/60;
  echo $exec_time;
}

The way I did to know if it is connected or disconnected it ALWAYS checks the customer name1, never going to other clients, already the database insertion is working properly.

Thank you!