Ответов: 6
Просмотров: 78
 |
<?php
/**
* Created by PhpStorm.
* User: andrew
* Date: 8/6/2016
* Time: 2:34 PM
*/
$servername = "localhost";
$username = "root";
$password = "superuser";
$dbname = "myDB";
// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}
// Create database
$sql = "CREATE DATABASE myDB";
if ($conn->query($sql) === TRUE) {
echo "Database created successfully\n";
$conn->close();
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// sql to create table
$sql = "CREATE TABLE Log1 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
date DATE,
time INT(6),
userIP VARCHAR(50),
URLfrom VARCHAR(100),
URLto VARCHAR(100)
)";
if ($conn->query($sql) === TRUE) {
echo "Table Log1 created successfully\n";
// sql to create table
$sql = "CREATE TABLE Log2 (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
userIP VARCHAR(50),
browser VARCHAR(50),
os VARCHAR(50)
)";
if ($conn->query($sql) === TRUE) {
echo "Table Log2 created successfully\n";
$fh = fopen('Log1.txt','r');
if ($fh != FALSE) {
flock($fh,LOCK_EX) or die($php_errormsg);
while(!feof($fh)) {
$string = fgets($fh);
$s1 = strtok($string, "|");
$s2 = strtok("|");
$s3 = strtok("|");
$s4 = strtok("|");
$s5 = strtok("|");
$sql = "INSERT INTO Log1 (date, time, userIP, URLfrom, URLto)
VALUES ($s1, $s2, $s3, $s4, $s5)";
if ($conn->query($sql) === TRUE) {
echo "Log1: New record created successfully\n";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
flock($fh,LOCK_UN) or die($php_errormsg);
fclose($fh) or die($php_errormsg);
} else {
echo "Error: " . $php_errormsg . "\n";
$sql = "INSERT INTO Log1 (date, time, userIP, URLfrom, URLto)
VALUES ('2006-08-07', 16, '200.150.1.2',
'http://www.w3schools.com/',
'http://www.w3schools.com/sql/sql_datatypes.asp')";
if ($conn->query($sql) === TRUE) {
echo "Log1: New record created successfully\n";
$sql = "INSERT INTO Log1 (date, time, userIP, URLfrom, URLto)
VALUES ('2006-08-07', 17, '200.150.1.2',
'https://mail.aol.com/webmail-std/en-us/suite',
'http://www.aol.com/?molhp=txtlnkusaolp00000051&ncid=mbr_aolacqint00000001')";
if ($conn->query($sql) === TRUE) {
echo "Log1: New record created successfully\n";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
$fh = fopen('Log2.txt','r');
if ($fh != FALSE) {
flock($fh, LOCK_EX) or die($php_errormsg);
while(!feof($fh)) {
$string = fgets($fh);
$s1 = strtok($string, "|");
$s2 = strtok("|");
$s3 = strtok("|");
$sql = "INSERT INTO Log2 (userIP, browser, os)
VALUES ($s1, $s2, $s3)";
if ($conn->query($sql) === TRUE) {
echo "Log2: New record created successfully\n";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
flock($fh,LOCK_UN) or die($php_errormsg);
fclose($fh) or die($php_errormsg);
} else {
echo "Error: " . $php_errormsg . "\n";
$sql = "INSERT INTO Log2 (userIP, browser, os)
VALUES ('200.150.1.2', 'netscape 7.0',
'windows 8')";
if ($conn->query($sql) === TRUE) {
echo "Log2: New record created successfully\n";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}
}
$sql = "SELECT Log1.userIP, SUM(Log1.time) AS sumtime, Log1.URLfrom,
COUNT(Log1.URLto) AS cntURLto, Log2.browser, Log2.os
FROM (Log1 JOIN Log2 ON Log1.userIP=Log2.userIP)
GROUP BY userIP";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
$fh = fopen('Stat.html','w');
if ($fh != FALSE) {
flock($fh, LOCK_EX) or die($php_errormsg);
// output data of each row
fprintf($fh,"<!DOCTYPE HTML>\r\n");
fprintf($fh,"<html>\r\n");
fprintf($fh,"<body>\r\n");
fprintf($fh,"<div>\r\n");
fprintf($fh,"<table border='1px solid black'>\r\n");
fprintf($fh,"<tr>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh,"IP-address\r\n");
fprintf($fh,"</td>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh,"Browser\r\n");
fprintf($fh,"</td>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh,"OS\r\n");
fprintf($fh,"</td>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh,"URL from\r\n");
fprintf($fh,"</td>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh,"URL to\r\n");
fprintf($fh,"</td>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh,"Time\r\n");
fprintf($fh,"</td>\r\n");
fprintf($fh,"</tr>\r\n");
while($row = $result->fetch_assoc()) {
echo "userIP " . $row["userIP"] . " browser " .
$row["browser"] . " os " . $row["os"] . "\n" . "sum time " .
$row["sumtime"] ."\n" .
"URLfrom " . $row["URLfrom"] . "\n" .
"count URLto " . $row["cntURLto"] . "\n";
fprintf($fh,"<tr>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh, $row["userIP"]);
fprintf($fh,"</td>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh, $row["browser"]);
fprintf($fh,"</td>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh, $row["os"]);
fprintf($fh,"</td>\r\n");
fprintf($fh,"<td>\r\n");
fprintf($fh, $row["URLfrom"]);
fprintf($fh,"</td>\r\n");
$sql2 = "SELECT URLto FROM Log1 WHERE userIP='" .
$row["userIP"] . "' ORDER BY id DESC LIMIT 1";
$result2 = $conn->query($sql2);
if ($result2->num_rows > 0) {
$row2 = $result2->fetch_assoc();
fprintf($fh,"<td>\r\n");
fprintf($fh, $row2["URLto"]);
fprintf($fh,"</td>\r\n");
}
fprintf($fh,"<td>\r\n");
fprintf($fh, $row["sumtime"]);
fprintf($fh,"</td>\r\n");
fprintf($fh,"</tr>\r\n");
}
fprintf($fh,"</table>\r\n");
fprintf($fh,"</div>\r\n");
fprintf($fh,"</body>\r\n");
fprintf($fh,"</html>\r\n");
fflush($fh) or die($php_errormsg);
flock($fh,LOCK_UN) or die($php_errormsg);
fclose($fh) or die($php_errormsg);
}
} else {
echo "0 results";
}
} else {
echo "Error creating table: " . $conn->error;
}
} else {
echo "Error creating table: " . $conn->error;
}
} else {
echo "Error creating database: " . $conn->error;
$sql = "DROP DATABASE myDB";
$conn->query($sql);
}
?>
Log1.txt:
'2006-08-07'|16|'200.150.1.2'|'http://www.w3schools.com/'|'http://www.w3schools.com/sql/sql_datatypes.asp'
'2006-08-07'|17|'200.150.1.2'|'https://mail.aol.com/webmail-std/en-us/suite'|'http://www.aol.com/?molhp=txtlnkusaolp00000051&ncid=mbr_aolacqint00000001'
Log2.txt:
'200.150.1.2'|'netscape 7.0'|'windows 8' |
Главная
Помощь
Поиск
Пользователи
Найдено сообщений: 1
