Требуется только вбить Логин и пароль от базы данных, также можно выполнять несколько запросов из одного скрипта меняя GET значения переменной q
например так 192.168.0.1/cgi-bin/utm.cgi?q=2
Ну и сделал возможным сортировку нажимая на ссылки заголовков столбцов
Код: Выделить всё
#!/usr/bin/perl
use CGI;
my $query = new CGI;
use strict;
use DBI;
my $data_source = "DBI:mysql:UTM5";
my $username = "СЮДА ВБИТЬ ПАРОЛЬ БД"
my $password = "СЮДА ВБИТЬ ЛОГИН БД"
my $dbh = DBI->connect($data_source, $username, $password) or die $DBI::errstr;
my $ob="null";
my $order = $query->param('order');
if ( $order =~ /\d/ ) {$ob = $order}
my $squery="";
if ( $query->param('q') == 1 ) {
$squery="SELECT INET_NTOA(ig.ip) AS ip,
sd.service_name AS service
FROM service_links sl,
iptraffic_service_links isl,
ip_groups ig,
services_data sd
WHERE sl.id = isl.id
AND isl.ip_group_id = ig.ip_group_id
AND sl.service_id = sd.id
AND ig.is_deleted=0
AND sl.is_deleted=0;"
}
else {
$squery="SELECT sl.user_id,
u.full_name AS user_full_name,
ig.uname AS login,
INET_NTOA(ig.ip) AS ip,
a.is_blocked AS block_status,
ROUND(a.balance) AS balance,
sd.service_name AS service,
tc.name AS current_tariff,
tn.name AS next_tariff
FROM users u
INNER JOIN service_links sl
ON u.id = sl.user_id
INNER JOIN iptraffic_service_links isl
ON sl.id = isl.id
INNER JOIN accounts a
ON a.id = u.basic_account
AND a.is_deleted=0
INNER JOIN ip_groups ig
ON isl.ip_group_id = ig.ip_group_id
INNER JOIN services_data AS sd
ON sl.service_id = sd.id
LEFT OUTER JOIN account_tariff_link AS atl
ON atl.account_id = u.basic_account
AND atl.id=sl.tariff_link_id
AND atl.is_deleted=0
LEFT OUTER JOIN tariffs tn
ON atl.next_tariff_id=tn.id
AND atl.next_tariff_id!=atl.tariff_id
LEFT OUTER JOIN tariffs tc
ON atl.tariff_id=tc.id
WHERE ig.is_deleted=0
AND sl.is_deleted=0
ORDER BY $ob;";
}
my $sth = $dbh->prepare($squery);
$sth->execute;
print $query->header(-charset => "UTF8");
my $names = $sth->{NAME};
my $c=0;
print '<html><head><title>Данные пользователей.</title><style>td{white-space:nowrap}</style></head><body><table border="1">',"\n";
my $i=1;
print "<tr>";
foreach ( @{$names} ){ print "<td><a href=?order=$i>","$_","</a></td>"; $i++ }
print "</tr>";
while (my @ServicesData = $sth->fetchrow_array){
print "<tr>";
foreach (@ServicesData){ print "<td>","$_","</td>" }
print "</tr>";
$c++;
}
$sth->finish;
$dbh->disconnect;
print "</table>Total Count:$c</br>Order by:$ob</body></html>\n";