Turn Exploded text file into table

Question

I'm running speedtest-cli on a Linux box, with a Cron job to run it regularly:

#!/bin/bash
date >> /home/blob/speedtest.log
/usr/local/bin/speedtest --simple >> /home/blob/speedtest.log

This outputs four variables, with line breaks between each:

Tue 31 Jan 20:00:01 UTC 2017
Ping: xx.xxx ms
Download: xx.xx Mbit/s
Upload: xx.xx Mbit/s

These are stored in a continuous log file.

I'm trying to store it in a five column - ID, date, ping, download, upload - database, such that I can run the cron job, read the result to the database, and then truncate the log file (so it doesn't have duplicates):

<body>
<table>

<?php
    $f = fopen("/home/blob/speedtest.log", "r") or exit("Unable to open file!");
    $arr_to_insert = array();
    // Read line by line until end of file
    while (!feof($f)) { 

    // Make an array using line break as delimiter
       $arrEx = explode('\n',fgets($f)); 
    // Put exploded data in an array
       echo '<tr><td name="date">' . $arrEx[0] . '</td><td name="ping">' . $arrEx[1] . '</td><td name="download">' . $arrEx[2] . '</td><td name="upload">' . $arrEx[3] . '</td></tr>';
       //strore text file row to an array 
       $arr_to_insert[] = $arrEx;
    }

    fclose($f);
 {

    // Connect to Database
include '../includes/connection.php';
    // Database Insert
foreach($arr_to_insert as $di){
    $sql="INSERT INTO speed (date, ping, download, upload) VALUES ('{$di[0]}','{$di[1]}','{$di[2]}','{$di[3]}')";
    if (!mysqli_query($conn,$sql))
      {
      die('Error: ' . mysqli_error());
      }

}
mysqli_close($conn);
}
?>
</table>
</form>
</body>
</html>

Which does store the data - so no error messages - but all in the one column, rather than each cron job populating a single row; date goes in date, ping in ping, etc.

ID  date    ping    download    upload
1   Sat 28 Jan          
2   Ping: xx            
3   Download: xx            
4   Upload: xx          
5   Sat 28 Jan          
6   Ping: xx            
7   Download: xx

Could someone please point out why it's not populating the table after exploding, and subsequently being stored in the database correctly. Thanks

I belive the explode function doesn't work right and all the row is still in "{$di[0]}" which insert its value to the first column. Can you share the output of "var_dump($arrEx);"? — Ofir Baruch, Feb 01 '17 at 13:50
I think you're right. Even without a var_dump, I can see from the source when the code runs it's only the date field that gets filled: '' etc

Sat 28 Jan 22:30:01 UTC 2017
Ping: 36.209 ms — Phil, Feb 01 '17 at 14:24
Now we need the var_dump to understand why the explode function does work as you've expected it to be. — Ofir Baruch, Feb 01 '17 at 14:26
array(1) { [0]=> string(29) "Sat 28 Jan 22:30:01 UTC 2017 " } array(1) { [0]=> string(16) "Ping: 36.209 ms " } array(1) { [0]=> string(23) "Download: 41.43 Mbit/s " } array(1) { [0]=> string(20) "Upload: 9.74 Mbit/s " } array(1) { [0]=> string(29) "Sat 28 Jan 22:45:01 UTC 2017 " } array(1) { [0]=> string(16) "Ping: 28.675 ms " } array(1) { [0]=> string(23) "Download: 46.67 Mbit/s " } array(1) { [0]=> string(21) "Upload: 10.86 Mbit/s " } — Phil, Feb 01 '17 at 14:30
Can you copy the first 20 lines of the .log file in a CODE block in your question? — Ofir Baruch, Feb 01 '17 at 14:32
When you say the first 20 lines, that's just the 4 lines at the top of the question repeated 5 times, right? Edit: Just seen your subsequent reply. Thanks. — Phil, Feb 01 '17 at 14:43

score 2 · Accepted Answer · answered Feb 01 '17 at 14:46

The log files contains the following:

Tue 31 Jan 20:00:01 UTC 2017

Ping: xx.xxx ms

Download: xx.xx Mbit/s

Upload: xx.xx Mbit/s

Tue 31 Jan 20:00:01 UTC 2017

Ping: xx.xxx ms

Download: xx.xx Mbit/s

Upload: xx.xx Mbit/s

And it continues.... So every line has a piece of data and each 4 lines (Date, ping, download, upload) are one "group".

In your code, you have:

$arrEx = explode('\n',fgets($f));

fgets - returns a line.

So you're actually doing:

1 round of the loop: $arrEx = explode('\n', "Tue 31 Jan 20:00:01 UTC 2017");

2 round of the loop: $arrEx = explode('\n', "Ping: xx.xxx ms"); ... ...

What you should do is:

    $arr_to_insert = array();
    $line = 1;    
// Read line by line until end of file
    while (!feof($f)) { 
       if($line == 1){
          $group = array();
       }

       $group[] = fgets($f);


       if($line == 4){
         echo '<tr><td name="date">' . $group[0] . '</td><td name="ping">' . $group[1] . '</td><td name="download">' . $group[2] . '</td><td name="upload">' . $group[3] . '</td></tr>';

         //reset lines group
         $arr_to_insert[] = $group;
         $line = 1;
         unset($group);
       } else {
         $line++;
       }
    }

Thank you, that's great. And thank you for the explanation too; I wasn't treating the data as a group but each line as a line. — Phil, Feb 01 '17 at 14:56

Turn Exploded text file into table

1 Answers1