php - 来自 SQL 正确 sql 查询的 IP 会计显示

Question

我需要有关正确查询方式的帮助，以显示数据库表中的信息。

我的数据库表包含 id | 的列源地址 |目的地地址 |字节

信息存储在我的 MikroTik 路由器(IP 会计)中，但我的脚本将 IP 地址存储为整数，因此我可以选择范围。

我需要运行正确的查询来显示如下数据:

• 从 src_address 到 dst_address 显示的所有字节都将是上传字节
• 从 dst_address 到 src_address 显示的所有字节将是下载字节

(源地址 -> 目标地址 = 上传)

(dst_address -> src_address = 下载)

我需要脚本输出以下内容:IP 地址 |上传字节 |下载字节 |总字节数

我当前的脚本只输出上传字节，我不确定如何再次颠倒顺序以显示下载字节，然后将两者相加以显示总字节。

这是我的代码:

<?php
//Include needed files
require ("config.php");
include ("includes/formatbytes.php");

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

//Query database
$query = "SELECT * , SUM(bytes) AS upload_bytes FROM ipaccounting WHERE 
src_address BETWEEN 3232235520 AND 3232301055 AND dst_address NOT BETWEEN 
3232235520 AND 3232301055 GROUP BY src_address";


//Execute query
$result = mysqli_query($conn,$query);


//Display IP information
echo "<table>";
echo "<tr><th>IP Address</th><th>Upload</th></tr>";

while($row = mysqli_fetch_array($result)) {
    if (isset($row['src_address'])) {
        $src_address = $row['src_address'];
        $src_address = long2ip($src_address);
    }
    /*if (isset($row['dst_address'])) {
        $dst_address = $row['dst_address'];
    } */
    if (isset($row['upload_bytes'])) {
        $uploadbytes = $row['upload_bytes'];
        $uploadbytes = formatBytes($uploadbytes);
    } 

    echo "<tr><td>".$src_address."</td><td>".$uploadbytes."</td></tr>";
} 

echo "</table>";
mysqli_close($conn);
?>

请注意，整数 3232235520 是 192.168.0.1，3232301055 是 192.168.255.254

Answer 1

您需要聚合两次，一次用于上传，一次用于下载:

SELECT address, SUM(upload_bytes) as upload_bytes, 
       SUM(download_bytes) as download_bytes
FROM ((SELECT src_address as address, SUM(bytes) AS upload_bytes, 0 as download_bytes
       FROM ipaccounting
       WHERE src_address BETWEEN 3232235520 AND 3232301055 AND
             dst_address NOT BETWEEN 3232235520 AND 3232301055
       GROUP BY src_address
      ) UNION ALL
      (SELECT dst_address, 0 AS upload_bytes, SUM(bytes) as download_bytes
       FROM ipaccounting
       WHERE dst_address BETWEEN 3232235520 AND 3232301055 AND
             src_address NOT BETWEEN 3232235520 AND 3232301055
       GROUP BY dst_address
      )
     ) a
GROUP BY address;

您还可以以更合理的格式显示 ip 地址。 MySQL 具有 IP 地址函数(参见 here):

select . . . , inet_ntoa(src_address) as src_ip

您也可以在应用层执行此操作。