php - Joining from another table SQL -


i need join users datapackage ip accounting following code

select ip_address, sum(upload_bytes) upload_bytes, sum(download_bytes)  download_bytes  ((select src_address ip_address, sum(bytes) upload_bytes, 0  download_bytes ipaccounting src_address between ('192.168.0.1') , ('192.168.255.254') ,  dst_address not between ('192.168.0.1') , ('192.168.255.254') group  src_address) union (select dst_address, 0 upload_bytes, sum(bytes)  download_bytes  ipaccounting dst_address between ('192.168.0.1') ,  ('192.168.255.254') , src_address not between ('192.168.0.1') ,  ('192.168.255.254') group dst_address)) group ip_address  

my sql database has 3 tables: ipaccounting, users , datapackages. each datapackage has unique id, id specified in datapackage column in users table , ip address specified in users table.

i need join users datapackage information , ip address echo:

ip address | upload bytes | download bytes | total bytes | datapackage id | username

my entire 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()); }   //convert ip addresses $iprangestart = ip2long($iprangestart); $iprangeend = ip2long($iprangeend);  //query database $query = "     select ip_address, sum(upload_bytes) upload_bytes,  sum(download_bytes) download_bytes      ((select src_address ip_address, sum(bytes) upload_bytes, 0  download_bytes     ipaccounting     src_address between inet_ntoa($iprangestart) ,  inet_ntoa($iprangeend) , dst_address not between inet_ntoa($iprangestart)  ,  inet_ntoa($iprangeend) group src_address)     union (select dst_address, 0 upload_bytes, sum(bytes)  download_bytes ipaccounting dst_address between  inet_ntoa($iprangestart) , inet_ntoa($iprangeend) , src_address not  between  inet_ntoa($iprangestart) , inet_ntoa($iprangeend) group dst_address))     group ip_address order inet_aton(ip_address)";   //execute query $result = mysqli_query($conn,$query);   //display ip information echo "<table>"; echo "<tr><th>ip address</th><th>upload</th><th>download</th><th>total</th> </tr>";  while($row = mysqli_fetch_array($result)) {         $ip_address = $row['ip_address'];         $upload_bytes = $row['upload_bytes'];         $download_bytes = $row['download_bytes'];         $total_bytes = ($upload_bytes + $download_bytes);          $total_bytes = formatbytes($total_bytes);         $upload_bytes = formatbytes($upload_bytes);         $download_bytes = formatbytes($download_bytes);         echo "<tr><td>".$ip_address."</td><td>".$upload_bytes."</td> <td>".$download_bytes."</td><td>".$total_bytes."</td></tr>"; }   echo "</table>"; mysqli_close($conn); ?> 

perhaps it:

select ip_address, sum(upload_bytes) upload_bytes, sum(download_bytes) download_bytes,  sum(upload_bytes + download_bytes) totalbytes, datapackage_id, username   (    (select ipaccounting.src_address ip_address, sum(ipaccounting.bytes) upload_bytes, 0 download_bytes,      user.username, datapackages.datapackage_id     ipaccounting     inner join user on user.ipaddress = ipaccounting.src_address     inner join datapackages on datapackages.datapackage_id = user.datapackageid     src_address between inet_ntoa($iprangestart) , inet_ntoa($iprangeend)      , dst_address not between inet_ntoa($iprangestart) , inet_ntoa($iprangeend)      group src_address) union     (select ipaccounting.dst_address ip_address, 0 upload_bytes, sum(ipaccounting.bytes) download_bytes,      user.username, datapackages.datapackage_id     ipaccounting      inner join user on user.ipaddress = ipaccounting.dst_address     inner join datapackages on datapackages.datapackage_id = user.datapackageid     dst_address between inet_ntoa($iprangestart) , inet_ntoa($iprangeend)      , src_address not between inet_ntoa($iprangestart) , inet_ntoa($iprangeend)      group dst_address) )  group ip_address  order inet_aton(ip_address) 

you might have tweak column names.


Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -