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
Post a Comment