php - IP Accounting display from SQL proper sql query -
i need correct way query display information database table.
my database table contains columns of id | src_address | dst_address | bytes
the information stored mikrotik router (ip accounting) script stores ip addresses integers can select ranges.
i need correct query run display data out follows:
- all bytes shown src_address dst_address upload bytes
- all bytes shown dst_address src_address download bytes
(src_address -> dst_address = upload)
(dst_address -> src_address = download)
i need script output exact following: ip address | upload bytes | download bytes | total bytes
my current script outputs upload bytes , not sure how reverse order second time display download bytes , add both display total bytes.
here code:
<?php //include needed files require ("config.php"); include ("includes/formatbytes.php"); //connect database $conn = mysqli_connect($sqlserver, $sqlusername, $sqlpassword, $sqldatabase); if (!$conn) { die("could not connect: " . mysqli_connect_error()); } //query database $query = "select * , sum(bytes) upload_bytes ipaccounting src_address between 3232235520 , 3232301055 , dst_address not between 3232235520 , 3232301055 group 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); ?>
note in integer 3232235520 192.168.0.1 , 3232301055 192.168.255.254
you need aggregate twice, once uploads , once downloads:
select address, sum(upload_bytes) upload_bytes, sum(download_bytes) download_bytes ((select src_address address, sum(bytes) upload_bytes, 0 download_bytes ipaccounting src_address between 3232235520 , 3232301055 , dst_address not between 3232235520 , 3232301055 group src_address ) union (select dst_address, 0 upload_bytes, sum(bytes) download_bytes ipaccounting dst_address between 3232235520 , 3232301055 , src_address not between 3232235520 , 3232301055 group dst_address ) ) group address;
you can display ip addresses in more reasonable format. mysql has ip address functions (see here):
select . . . , inet_ntoa(src_address) src_ip
you can @ application layer.
Comments
Post a Comment