合并数据并减少表中的行数？

vnjpjtjt 于 2021-06-24 发布在 Mysql

关注(0)|答案(1)|浏览(369)

我目前有一个脚本，从我的mikrotik路由器获取数据，然后将其插入到我的数据库中，但它会创建成千上万的行。
不管怎样，是否有办法将每个src和dst ip地址的数据组合起来，然后将其插入数据库，或者是否有更好的解决方案来存储记帐数据。
我的服务器每5分钟运行一个cron作业来运行脚本。
当我的所有记帐数据被插入数据库时，sql会自动获取一个时间戳（当前时间戳）。
这是我目前的剧本：


# !/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!");
?>

mysql php mysqli

来源：https://stackoverflow.com/questions/48267889/combine-data-and-make-less-rows-in-table

1条答案

按热度按时间

hiz5n14c1#

我已经更新了我的代码，以使用不同的方法，而不是将ip记帐数据存储在表中，而是将数据记帐，然后在表中更新 traffic_counters 表

$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();

赞(0）回复(0）举报 2021-06-24

我来回答

合并数据并减少表中的行数？

1条答案

相关问题

热门标签

最新问答