- Joined
- Oct 27, 2012
- Messages
- 112
- Reaction score
- 12
on this php code i use 2 table to fetch data from db, chatlogs and base for pulling name_char column. make a sure you have base table on your db, you can search on this forum to pulling character database
1. chat.sh
2. chatlogs.php
3. dbconfig.php
4. chatlogs.sql
Preview
note :
1. chat.sh
Bash:
#!/bin/bash
$DBHost = "localhost"; # localhost or your IP
$DBUser = "root"; # Database user
$DBPassword = ""; # Database password
$DBName = ""; # Database name
while true
do
if [ -f "/root/logs/world2.chat" ]; then
while IFS= read -r line
do
datetime=$(echo "$line" | awk '{print $1, $2}')
src=$(echo "$line" | grep -o 'src=[0-9]*' | cut -d'=' -f2)
dst=$(echo "$line" | grep -o 'dst=[0-9]*' | cut -d'=' -f2)
msg=$(echo "$line" | grep -o 'msg=.*' | cut -d'=' -f2)
if echo "$line" | grep -q "Family:"; then
chl=3
elif echo "$line" | grep -q "Guild:"; then
chl=4
elif echo "$line" | grep -q "Whisper:"; then
chl=5
else
chl=$(echo "$line" | grep -o 'chl=[0-9]*' | cut -d'=' -f2)
fi
exists=$(mysql -h $DBHost -u $DBUser -p$DBPassword $DBName -se "SELECT COUNT(*) FROM chatlogs WHERE date='$datetime';")
if [ $exists -eq 0 ]; then
mysql -h $DBHost -u $DBUser -p$DBPassword $DBName -e "INSERT INTO chatlogs (id, dst, msg, date, type) VALUES ('$src', '$dst', '$msg', '$datetime', '$chl');"
fi
done < /root/logs/world2.chat
else
echo "Input file '/root/logs/world2.chat' not found."
fi
sleep 5 # Sleep for 5 seconds before the next iteration
done2. chatlogs.php
PHP:
<?php
include('dbconfig.php');
$conn = new mysqli($DBHost, $DBUser, $DBPassword, $DBName);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$sql = "SELECT chatlogs.id,
sender.name_char as sender_name,
recipient.name_char as recipient_name,
chatlogs.dst,
chatlogs.msg,
chatlogs.date,
chatlogs.type
FROM chatlogs
INNER JOIN base sender ON sender.id = chatlogs.id
LEFT JOIN base recipient ON recipient.id = chatlogs.dst
ORDER BY chatlogs.date DESC";
$result = $conn->query($sql);
$conn->close();
?>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Chat Logs</title>
<style>
table {
border-collapse: collapse;
}
th, td {
padding: 8px;
text-align: left;
border: 1px solid #ccc; /* Add border to cells */
white-space: nowrap; /* Prevent text wrapping */
}
th {
background-color: #f2f2f2;
}
</style>
<script>
function reloadWithScrollPosition() {
var scrollPosition = window.scrollY || document.documentElement.scrollTop;
location.reload();
window.scrollTo(0, scrollPosition);
}
setInterval(reloadWithScrollPosition, 5000); // Refresh every 5 seconds (5000 milliseconds)
</script>
</head>
<body>
<h2>Chat Logs</h2>
<?php
if ($result && $result->num_rows > 0) {
echo "<table>";
echo "<tr><th>Type Chat</th><th>ID</th><th>Name</th><th>To</th><th>Message</th><th>Date</th></tr>";
while($row = $result->fetch_assoc()) {
$decoded_msg = utf8_encode(base64_decode($row["msg"]));
$formatted_date = date('h:i:s A l-d-M-Y', strtotime($row["date"]));
switch ($row["type"]) {
case 1:
$type_display = "World";
break;
case 18:
$type_display = "Team";
break;
case 3:
$type_display = "Clan";
break;
case 4:
$type_display = "Alliance";
break;
case 2:
$type_display = "Party";
break;
case 5:
$type_display = "Whisper";
break;
default:
$type_display = "Normal";
}
echo "<tr>
<td>".$type_display."</td>
<td>".$row["id"]."</td>
<td>".$row["sender_name"]."</td>
<td>".$row["recipient_name"]."</td>
<td>".$decoded_msg."</td>
<td>".$formatted_date."</td>
</tr>";
}
echo "</table>";
} else {
echo "No chat logs available.";
}
?>
</body>
</html>3. dbconfig.php
PHP:
<?php
$DBHost = "localhost"; // localhost or your IP
$DBUser = "root"; // Database user
$DBPassword = ""; // Database password
$DBName = ""; // Database name
// Create a connection
$conn = new mysqli($DBHost, $DBUser, $DBPassword, $DBName);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
SQL:
CREATE TABLE IF NOT EXISTS `chatlogs` (
`id` int(11) NOT NULL,
`dst` int(11) NOT NULL,
`type` varchar(10) NOT NULL,
`msg` varchar(255) NOT NULL,
`date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Preview
note :
note if you get error message when running this script :
run the following command:
Bash:
-bash: ./chat: /bin/bash^M: bad interpreter: No such file or directory
Bash:
dos2unix chat.sh
Last edited:
