Присоединение из другой таблицы SQL

Мне нужно присоединить пакет данных пользователей к учету IP со следующим кодом

SELECT ip_address, SUM(upload_bytes) as upload_bytes, SUM(download_bytes) as
download_bytes
FROM ((SELECT src_address as ip_address, SUM(bytes) AS upload_bytes, 0 as
download_bytes
FROM ipaccounting
WHERE src_address BETWEEN ('192.168.0.1') AND ('192.168.255.254') AND
dst_address NOT BETWEEN ('192.168.0.1') AND ('192.168.255.254') GROUP BY
src_address)
UNION ALL (SELECT dst_address, 0 AS upload_bytes, SUM(bytes) as
download_bytes
FROM ipaccounting WHERE dst_address BETWEEN ('192.168.0.1') AND
('192.168.255.254') AND src_address NOT BETWEEN ('192.168.0.1') AND
('192.168.255.254') GROUP BY dst_address))
a GROUP BY ip_address

Моя база данных SQL имеет 3 таблицы: ipaccounting, users и datapackages.
Каждый пакет данных имеет уникальный идентификатор, идентификатор которого указан в столбце пакета данных в таблице пользователей, а IP-адрес указан в таблице пользователей.

Мне нужно объединить информацию о пакете данных пользователей и IP-адрес, чтобы он отображался:

IP-адрес | Загрузить байты | Скачать байты | Всего байт | ID пакета данных | имя пользователя

Весь мой код:

<?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());
}//Convert IP Addresses
$IPRangeStart = ip2long($IPRangeStart);
$IPRangeEnd = ip2long($IPRangeEnd);

//Query database
$query = "SELECT ip_address, SUM(upload_bytes) as upload_bytes,
SUM(download_bytes) as download_bytes
FROM ((SELECT src_address as ip_address, SUM(bytes) AS upload_bytes, 0
as download_bytes
FROM ipaccounting
WHERE src_address BETWEEN INET_NTOA($IPRangeStart) AND
INET_NTOA($IPRangeEnd) AND dst_address NOT BETWEEN INET_NTOA($IPRangeStart)
AND
INET_NTOA($IPRangeEnd) GROUP BY src_address)
UNION ALL (SELECT dst_address, 0 AS upload_bytes, SUM(bytes) as
download_bytes FROM ipaccounting WHERE dst_address BETWEEN
INET_NTOA($IPRangeStart) AND INET_NTOA($IPRangeEnd) AND src_address NOT
BETWEEN
INET_NTOA($IPRangeStart) AND INET_NTOA($IPRangeEnd) GROUP BY dst_address))
a GROUP BY ip_address ORDER BY 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);
?>

0

Решение

Возможно, это сделает это:

SELECT ip_address, SUM(upload_bytes) as upload_bytes, SUM(download_bytes) as download_bytes,
sum(upload_bytes + download_bytes) as totalbytes, datapackage_id, username
FROM
(
(SELECT ipaccounting.src_address as ip_address, SUM(ipaccounting.bytes) AS upload_bytes, 0 as download_bytes,
user.username, datapackages.datapackage_id
FROM ipaccounting
inner join user on user.ipaddress = ipaccounting.src_address
inner join datapackages on datapackages.datapackage_id = user.datapackageid
WHERE src_address BETWEEN INET_NTOA($IPRangeStart) AND INET_NTOA($IPRangeEnd)
AND dst_address NOT BETWEEN INET_NTOA($IPRangeStart) AND INET_NTOA($IPRangeEnd)
GROUP BY src_address)
UNION ALL
(SELECT ipaccounting.dst_address as ip_address, 0 AS upload_bytes, SUM(ipaccounting.bytes) as download_bytes,
user.username, datapackages.datapackage_id
FROM ipaccounting
inner join user on user.ipaddress = ipaccounting.dst_address
inner join datapackages on datapackages.datapackage_id = user.datapackageid
WHERE dst_address BETWEEN INET_NTOA($IPRangeStart) AND INET_NTOA($IPRangeEnd)
AND src_address NOT BETWEEN INET_NTOA($IPRangeStart) AND INET_NTOA($IPRangeEnd)
GROUP BY dst_address)
) a
GROUP BY ip_address
ORDER BY INET_ATON(ip_address)

Возможно, вам придется настроить некоторые имена столбцов.

1

Другие решения

Других решений пока нет …

По вопросам рекламы ammmcru@yandex.ru
Adblock
detector