Combine data and make less rows in table?

Question

I currently have a script that fetches data from my MikroTik router and then inserts it into my database but It creates thousands and thousands of rows.

Is there anyway to combine the data per src and dst IP address and then insert it into the database or is there a better solution to store accounting data.

My server runs a cron job every 5 minutes to run the script.

All my accounting data automatically gets a time stamp by sql when it is inserted into the database (CURRENT_TIMESTAMP).

Here is my current script:

#!/usr/bin/env php
<?php
    //Include needed files
    require 'config.php';
    require ("html/includes/routeros_api.class.php");
    require ("html/includes/formatbytes.php");
    require ("html/includes/converttobytes.php");

    //Set ini
    ini_set ("expose_php",$ExposePHP);
    ini_set ("display_errors",$DisplayErrors);
    ini_set ("max_execution_time","90000");



    //UPDATE ACCOUNTING
    //Connect to database
    $conn = mysqli_connect($SQLserver, $SQLusername, $SQLpassword, $SQLdatabase);
    if (!$conn) {
        //die("Could not connect: " . mysqli_connect_error());
        die("Error Connecting to database!");
    }

    //Fetch accounting data from router
    $routerconnect = "http://$RouterIP:$WebPort/$AccountingLocation";
    $orig = file_get_contents($routerconnect);


    foreach ( explode( "\n", $orig ) as $line ) {
        if ( trim( $line )) {
            list( $src, $dst, $bytes, $packets ) = explode( ' ', trim( strip_tags( $line )));

            //Create Insert query for accounting data
            $sql = "INSERT INTO ip_accounting (src_address, dst_address, bytes, packets) VALUES ('$src', '$dst', '$bytes', '$packets')";

            //Execute query
            $result = mysqli_query($conn,$sql);
        }
    }
    echo ("Accounting updated!");
?>

what do you mean by *Is there anyway to combine the data per src and dst IP address* ? — Ravi, Jan 15 '18 at 17:26
You can only really combine them if they are all the same. Otherwise the bytes and packets may seem useless to keep around, since it would only be storing one of those values per src/dst address. If you want to ADD bytes to bytes and packets to packets by unique src/dst address.... you would first try to UPDATE the row, and if not exist, INSERT a new row. — IncredibleHat, Jan 15 '18 at 17:28
That is pretty much what I'm wanting to do but my problem is that if I UPDATE, my accounting system will not work correctly as It will be updating with old timestamps and vica versa... I actually need the PHP script to combine the bytes with bytes and packets with packets with the all src and dst matching each other and then insert it into the database — Raymond Clayton Rudman, Jan 15 '18 at 17:45
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Jan 15 '18 at 18:05
Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. — tadman, Jan 15 '18 at 18:05
A lot of problems can be detected and resolved by [enabling exceptions in `mysqli`](https://stackoverflow.com/questions/14578243/turning-query-errors-to-exceptions-in-mysqli) so mistakes aren't easily ignored. — tadman, Jan 15 '18 at 18:05
Um okay now this got a whole lot more complicated... I am new to both PHP and MySQL — Raymond Clayton Rudman, Jan 15 '18 at 18:29

score 0 · Accepted Answer · answered Dec 19 '18 at 13:46

I have updated my code to use a different method, instead of storing IP accounting data in a table, data is accounted and then updated in a traffic_counters table

$API->write('/ip/accounting/snapshot/take',true);
        $READ = $API->read(false);
        $ARRAY = $API->parseResponse($READ);

        $API->write('/ip/accounting/snapshot/print',true);
        $READ = $API->read(false);
        $ARRAY = $API->parseResponse($READ);

        foreach($ARRAY as $ACCOUNTING) {
            $ip_src = $ACCOUNTING['src-address'];
            $ip_dst = $ACCOUNTING['dst-address'];
            $bytes = $ACCOUNTING['bytes'];

            if(isset($config['debug']) AND ($config['debug'] == true)) {
                echo "Checking $ip_src -> $ip_dst bytes: $bytes\n";
            }

            //Check if ip in use UPLOAD
            $query = "SELECT service_id, ip_address FROM service_info WHERE ip_address='$ip_src' AND deleted='0'";
            $result = mysqli_query($mysql_connection, $query);
            $row = mysqli_fetch_array($result);

            if(mysqli_num_rows($result) > 0) {
                $service_id = $row['service_id'];
                //Update Download Traffic
                $check_if_exist_query = "SELECT * FROM traffic_counters WHERE service_id='$service_id' AND date=CURRENT_DATE()";
                $check_result = mysqli_query($mysql_connection, $check_if_exist_query);
                $check_num_rows = mysqli_num_rows($check_result);

                if($check_num_rows == 0) {
                    $add_query = "INSERT INTO traffic_counters (service_id, upload_bytes, date) VALUES ('$service_id', '$bytes', CURRENT_DATE());";
                    $add_result = mysqli_query($mysql_connection, $add_query);
                } else {
                    $update_query = "UPDATE traffic_counters SET 
                                        upload_bytes = upload_bytes + $bytes
                                        WHERE service_id='$service_id' AND date=CURRENT_DATE();
                                    ";
                    $update_result = mysqli_query($mysql_connection, $update_query);
                }
            }

            //Check if ip in use DOWNLOAD
            $query = "SELECT service_id, ip_address FROM service_info WHERE ip_address='$ip_dst' AND deleted='0'";
            $result = mysqli_query($mysql_connection, $query);
            $row = mysqli_fetch_array($result);

            if(mysqli_num_rows($result) > 0) {
                $service_id = $row['service_id'];
                //Update Download Traffic
                $check_if_exist_query = "SELECT * FROM traffic_counters WHERE service_id='$service_id' AND date=CURRENT_DATE()";
                $check_result = mysqli_query($mysql_connection, $check_if_exist_query);
                $check_num_rows = mysqli_num_rows($check_result);

                if($check_num_rows == 0) {
                    $add_query = "INSERT INTO traffic_counters (service_id, download_bytes, date) VALUES ('$service_id', '$bytes', CURRENT_DATE());";
                    $add_result = mysqli_query($mysql_connection, $add_query);
                } else {
                    $update_query = "UPDATE traffic_counters SET 
                                        download_bytes = download_bytes + $bytes
                                        WHERE service_id='$service_id' AND date=CURRENT_DATE();
                                    ";
                    $update_result = mysqli_query($mysql_connection, $update_query);
                }
            }
        }
        $API->disconnect();

Combine data and make less rows in table?

1 Answers1