edit:mikrotikip通过phpapi记帐，只存储数据库中指定的信息

我正在编写一个脚本，将mikrotik的ip记帐数据导入mysql，前提是数据库中存在包含ip地址的行。
我有一张table services 带列 id （服务id）和 ipv4 （服务ip地址），其中包含要为其存储记帐数据的ip地址行。
我还有一张table traffic_counters 带列 id , service_id , download_bytes , upload_bytes 以及 date 当前从api获取会计数据时，如果使用 print_r($ARRAY) ，我得到如下输出：

Array ( [0] => Array ( [.id] => *0 [src-address] => 10.2.1.2 [dst-address] => 10.1.1.20 [packets] => 4 [bytes] => 4528 ) [1] => Array ( [.id] => *1 [src-address] => 10.1.1.20 [dst-address] => 18.196.198.94 [packets] => 2 [bytes] => 80 ) [2] => Array ( [.id] => *2 [src-address] => 10.2.1.2 [dst-address] => 10.100.1.1 [packets] => 16 [bytes] => 2216 )

我只是在这个阶段想保持跟踪的ip地址和字节传输。我曾经使用一个脚本，将所有会计数据保存到一个表中，然后将其排序到一个新表中，然后在完成时清除，但是这个过程花费的时间非常长，有时脚本可以运行两个小时，然后才完成将所有数据临时输入到一个表中。
我之前的问题是

SELECT 
                ip_address, 
                SUM(upload_bytes) as upload_bytes, 
                SUM(download_bytes) as download_bytes, 
                SUM(upload_bytes + download_bytes) as total_bytes, 
                timeanddate 
            FROM 
                (
                    (
                        SELECT 
                            ip_accounting.src_address as ip_address, 
                            SUM(ip_accounting.bytes) AS upload_bytes, 
                            0 as download_bytes, 
                            ip_accounting.accounting_router_ip, 
                            ip_accounting.accounting_router_id, 
                            timeanddate 
                        FROM 
                            ip_accounting 
                        GROUP BY 
                            src_address
                    ) 
                    UNION ALL 
                        (
                            SELECT 
                                ip_accounting.dst_address as ip_address, 
                                0 AS upload_bytes, 
                                SUM(ip_accounting.bytes) as download_bytes, 
                                ip_accounting.accounting_router_ip, 
                                ip_accounting.accounting_router_id, 
                                timeanddate 
                            FROM 
                                ip_accounting 
                            GROUP BY 
                                dst_address
                        )
                ) a 
            GROUP BY 
                ip_address,
                YEAR(timeanddate),
                MONTH(timeanddate),
                DAY(timeanddate) 
            ORDER BY 
                ip_address

所以基本上把源ip作为上传，目的ip作为下载
我需要修改脚本，通过api获取所有数据，就像我在开始时提到的那样，并通过foreach循环运行它，以查找数据库中匹配的ip地址 services.ipv4 表/列，然后将其插入/更新到 traffic_counters 表
关于如何在这种类型的数组输出上使用foreach命令，我有一点问题，并且不太确定要按哪个顺序编写脚本。
我当前的脚本做每日记帐，必须保持不变，例如，每次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['0']['.id'] as $ACCOUNTING) {
        $id = $ARRAY['0']['.id'];
        $ip_src = $ARRAY['0']['src-address'];
        $ip_dst = $ARRAY['0']['dst-address'];
        $bytes = $ARRAY['0']['dst-address'];
    }

但我越来越 Warning: Invalid argument supplied for foreach() in /opt/WispManager/html/admin/test.php on line 24 有人能帮我弄清楚如何以最有效的方式完成这个过程吗？因为我正在为整个过程绞尽脑汁，我不想在mysql中存储任何临时数据。
我试图复制splnyx的ip记帐方式。
编辑：我已经玩了一点，并想出了如何做foreach循环与数组，并得到了一切工作，因为我想。我只是不确定我这样做是否非常有效，请有人帮我检查一下我的代码，如果有更简单/更快的解决方案，请给我指出正确的方向。
我的新剧本：

<?php
//Require admin
require_once("inc/admin.php");
require_once ("../includes/routeros_api.class.php");

//SET 
$ip = "10.100.1.1";

//Connect to MikroTik API
$API = new RouterosAPI();
$API->debug = $config['api']['debug'];
if (!$API->connect($ip, $config['api']['username'], $config['api']['password'])) {
    echo "Could not connect to RouterOS API";
} else {
    $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'];

        //Check if ip in use UPLOAD
        $query = "SELECT id, ipv4 FROM services WHERE ipv4='$ip_src' AND deleted !='1'";
        $result = mysqli_query($conn, $query);
        $row = mysqli_fetch_array($result);

        if(mysqli_num_rows($result) > 0) {
            $service_id = $row['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($conn, $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($conn, $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($conn, $update_query);
            }
        }

        //Check if ip in use DOWNLOAD
        $query = "SELECT id, ipv4 FROM services WHERE ipv4='$ip_dst' AND deleted !='1'";
        $result = mysqli_query($conn, $query);
        $row = mysqli_fetch_array($result);

        if(mysqli_num_rows($result) > 0) {
            $service_id = $row['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($conn, $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($conn, $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($conn, $update_query);
            }
        }
    }
    $API->disconnect();
}
?>

edit:mikrotikip通过phpapi记帐，只存储数据库中指定的信息

暂无答案！

相关问题

热门标签

最新问答