BeBot - An Anarchy Online and Age Of Conan chat automaton
General => General discussion => Topic started by: dillinger on September 28, 2009, 09:16:01 am
-
Hi Community!
It there a way to make online members' status (Age of Conan guild) visible on a website?
Thanx in advance!
dill
-
Try the script I posted here: http://bebot.link/ao-0-6-support/online-query/msg12818/#msg12818
Should help you get a remote query to the bot database.
-
ok, I know this is probably a mysql issue, but was wondering if you could work me through it to see what I might be missing (google is threatening to boycot me because of all the queries)
first off, running 0.6.5 bebot for ao, mysql 5.2.1 on windows box....webserver is a shared hosting on linux box.
I run bebot locally, and have my web page hosted remotely. trying out your script, I found out that I had only allowed localhost connections to the bebot db. I added a new user to the db, for the sake of argument bebot2@'%'.
this allowed me to be able to connect to the db via mysql maestro from another computer on the intranet, so I think the command took.
So I uploaded the script to my webserver, changing the parts that needed to be changed and I get this when I run the script from a browser:
Warning: mysql_connect() [function.mysql-connect]: Can't connect to MySQL server on 'xx.xx.xx.xx' (113) in /<<path to script>>/onlinestatus.php on line 6
Error connecting to mysql2
so thought it might be a firewall setting, so checked both the router and the system, and firewall is disabled on both. made sure port 3306 was forwarded to that machine. (basically have so many ports pointing to that machine, it might as well be on the DMZ)
everything should be fine, yet for some reason it does not allow me to connect.
I would rather not move the sql db to the remote hosting, as it is both slow and cumbersome for the bots. likewise, I would not want to move the hosting to the local machine as the office does not have enough bandwidth to support such an action.
Any help, nudges, pointers, subscriptions to rogaine to replace pulled hair, would be greatly appreciated..
-
update: looks like I have to pull the rest of my hair out. Found out it is a hosting issue on the linux shared server blocking all outgoing ports, back to square one. Woulda been a nice script, if I coulda used it.
-
make incomming connection then:)
update web db from your bot machine
-
I had the same problem with my host. I ended up contacting them and they unblocked the port for my account. Might be a good try.
-
yeah tried that, they said they wouldn't do it for a shared, but was a perfect time to upgrade to v-ded hosting. Gotta love a sales pitch.
-
this will create and upload your html table on web.
style it, or make xml, your choce. i made table so it will display characters immediately
calling of query file and upload, just configure it to your needs. didnt test but it should work
/*
this is part where you call query for online users
*/
ob_start();
require('file-where-query-is-made-and-table-created.php');
$data = ob_get_contents();
ob_end_clean();
// make temp file
file_put_contents('temp.php', $data);
/*
make upload here
*/
$c = ftp_connect('ftpserver') or die('Can not connect to FTP server!');
if (@!ftp_login($c, 'ftpusername', 'ftppassword')) {
@ftp_close($c);
die('Can not log onto FTP server with given data!');
}
// here you put data to some file on your webserver
ftp_put($c, 'path-to-file', 'temp.php', FTP_BINARY);
@ftp_close($c);
unset($c, $data);
file where is actual query made, used one from someones post for online
<table>
<?php
/*
this will make list of your members that are online
i used sql from http://bebot.link/ao-0-6-support/online-query/msg12818/#msg12818
*/
$sql = "select o.nickname, w.profession, w.level, w.defender_rank_id from online as o, whois as w where o.status_gc = '1' and o.nickname = w.nickname order by o.nickname ASC";
$query = mysql_query($sql) or die('error!<br>'.mysql_error());
if (mysql_affected_rows() > 0) {
while ($r = mysql_fetch_object($query)) {
?>
<tr>
<td><?=$r->nickname?></td>
<td><?=$r->profession?></td>
<td><?=$r->level?></td>
<td><?=$r->defender_rank_id?></td>
</tr>
<?php
unset($r);
}
mysql_free_result($query);
}
unset($sql);
?>
</table>
-
did you try this clash? or this is not what you were looking for?
-
I haven't had a chance as of yet snyder...hopefully I'll get a chance this weekend to play around with the scripts and work something out...
-
and still toying with the idea of getting a vded and hosting the bot on that...with a combined db...
-
ok, I got the virtual dedicated, forums is working, I can connect to teh bot's sql but running the php as either a block or standalone gives no output whatsoever..I tested teh db connection and it works fine so it is hitting the db...just not getting any output
-
Try this code?
<?php
/*
this will make list of your members that are online
i used sql from http://bebot.link/ao-0-6-support/online-query/msg12818/#msg12818
*/
print '<table>'
$sql = "SELECT o.nickname, w.profession, w.level, w.defender_rank_id FROM online AS o LEFT JOIN whois AS w ON o.nickname = w.nickname WHERE o.status_gc = '1' AND o.botname NOT LIKE '%IRC' ORDER BY o.nickname ASC";
$query = mysql_query($sql) or die('error!<br>'.mysql_error());
if (mysql_affected_rows() > 0) {
while ($r = mysql_fetch_object($query)) {
print '<tr>
<td>'.$r->nickname.'</td>
<td>'.$r->profession.'</td>
<td>'.$r->level.'</td>
<td>'.$r->defender_rank_id.'</td>
</tr>';
unset($r);
}
mysql_free_result($query);
}
unset($sql);
print '</table>';
?>
-
yeah was figuring out how to put that in to the one with the remote db call as I can do that now...so used the original code from here: http://bebot.link/ao-0-6-support/online-query/msg12818/#msg12818 (http://bebot.link/ao-0-6-support/online-query/msg12818/#msg12818). I can connect to the remot bot's db, just doesn't display any output, not even an error
-
Change
$result = mysql_query($query);To
$result = mysql_query($query) or die('error!<br>'.mysql_error());
Or even add this to afer <?php
error_reporting(E_ALL);
ini_set("display_errors", 1);
-
still nothing, just a blank page
-
Make a new .php file with this content:
<?php
phpinfo();
?>
Im starting to belive you dont have mysql client or php installed on your webserver...
-
should, it's running smf there ;)
PHP Version 5.2.6
mysql 5.0.45
-
to rehash, the php file I am using is:
<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);
//set local variables
$dbhost = "xx.xx.xx.xx:3306";
$dbuser = "xxxxx";
$dbpass = "xxxxx";
$dbname = "xxxxx";
//connect
$db = mysql_connect($dbhost,$dbuser,$dbpass) or die ('Error connecting to mysql2');
mysql_select_db("$dbname",$db) or die ("Database $dbname not select.." . mysql_error());
// $conn = mysql_connect('67.77.135.21:3306', 'bebot2', 'bebot') or die ('Error connecting to mysql2');
// mysql_select_db('bebot',$conn) or die ("Database $DB not select.." . mysql_error());
if (!isset($_GET['sorton']))
{
$sorton = 'default';
}
else
{
$sorton = $_GET['sorton'];
}
if ($sorton == 'nickname')
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY online.nickname ASC";
}
else if ($sorton == 'profession')
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY whois.profession ASC, whois.level DESC, whois.defender_rank_id DESC";
}
else if ($sorton == 'level')
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY whois.level DESC, whois.defender_rank_id DESC, whois.profession ASC";
}
else if ($sorton == 'ai')
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY whois.level DESC, whois.defender_rank_id DESC, whois.profession ASC";
}
else
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY whois.profession ASC, whois.level DESC, online.nickname ASC";
}
$result = mysql_query($query) or die('error!<br>'.mysql_error());
$total_rows = mysql_num_rows( $result );
$content = "<div style=\"text-align: center\">";
$content = $content . "<a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=default\">There are <b>$total_rows</b> people online</a><br /><br /></div>";
$content = $content . "<table border=1 cellspacing=0 style=\"margin-left:auto; margin-right:auto;\"> \n";
$content = $content . "<tr><td><b><a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=nickname\">Nickname</a></b></td></td><td><b><a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=profession\">Profession</a></b></td><td><b><a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=level\">Level</a></b></td><td><b><a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=ai\">AI</a></b></td></tr>";
while($onlinerow = mysql_fetch_row($result))
{
$content = $content . "<tr>";
foreach ( $onlinerow as $data )
$content = $content . "\t <td>$data</td>";
$content = $content . "</td>\n";
}
$content = $content . "</table>\n";
mysql_free_result($result);
mysql_close($db);
?>
and used this php page to test db connection:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>MySQL Test Script</title>
<style>
body { font-family: Verdana; font-size: 10pt; }
td { font-size: 10pt; }
th { font-size: 8pt; }
</style>
</head>
<body>
<?php
if ( isset ( $_POST['submit'] ) ) {
?>
<table border="1" summary="" cellpadding="2" cellspacing="0" style="border-collapse: collapse;">
<tr>
<th bgcolor="#F2F2F2" colspan="2"><center><b>Connection Results</b></center></td>
</tr>
<?
if (!$link = @mysql_connect($_POST['mysql_hostname'], $_POST['mysql_username'], $_POST['mysql_password'])) {
?>
<tr>
<td>Connection:</td>
<td bgcolor="red"><font color="white"><?php echo mysql_error(); ?></font></td>
</tr>
<?
} else {
?>
<tr>
<td>Connection:</td>
<td bgcolor="green"><font color="white">Successful!</font></td>
</tr>
<?
}
if (!$dblink = @mysql_select_db($_POST['mysql_database'], $link)) {
?>
<tr>
<td>Database Selection:</td>
<td bgcolor="red"><font color="white"><?php echo mysql_error(); ?></font></td>
</tr>
<?
} else {
?>
<tr>
<td>Database Selection:</td>
<td bgcolor="green"><font color="white">Successful!</font></td>
</tr>
<?
}
@mysql_close($link);
?>
</table>
<?
}
?>
<form method="post">
<table border="1" summary="" cellpadding="2" cellspacing="0" style="border-collapse: collapse;">
<tr>
<th bgcolor="#F2F2F2" colspan="2"><center><b>Enter Your MySQL Information</b></center></td>
</tr>
<tr>
<td>MySQL Hostname</td>
<td><input type="text" name="mysql_hostname" size="40" value="<?php echo $_POST['mysql_hostname']; ?>"></td>
</tr>
<tr>
<td>MySQL Username</td>
<td><input type="text" name="mysql_username" size="40" value="<?php echo $_POST['mysql_username']; ?>"></td>
</tr>
<tr>
<td>MySQL Password</td>
<td><input type="text" name="mysql_password" size="40" value="<?php echo $_POST['mysql_password']; ?>"></td>
</tr>
<tr>
<td>MySQL Database</td>
<td><input type="text" name="mysql_database" size="40" value="<?php echo $_POST['mysql_database']; ?>"></td>
</tr>
<tr>
<td colspan="2"><center><input type="submit" name="submit" value="Submit"> <input type="reset" name="reset" value="Reset"></center></td>
</tr>
</table>
</form>
</body>
</html>
-
Ah, maybe i should have been reading the whole script :P
You need print out the $content.
Put
print $content;someplace after
$content = $content . "</table>\n";
-
that was it! thanks gen
-
Ok, I took the hard work you all did, and expanded it to include org names, and the ability to sort by org names.
<?php
error_reporting(E_ALL);
ini_set("display_errors", 1);
//set local variables
$dbhost = "xx.xx.xx.xx";
$dbuser = "xxxxx";
$dbpass = "xxxxx";
$dbname = "xxxxx";
//connect
$db = mysql_connect($dbhost,$dbuser,$dbpass) or die ('Error connecting to mysql2');
mysql_select_db("$dbname",$db) or die ("Database $dbname not select.." . mysql_error());
if (!isset($_GET['sorton']))
{
$sorton = 'default';
}
else
{
$sorton = $_GET['sorton'];
}
if ($sorton == 'nickname')
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.org_name, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY online.nickname ASC";
}
else if ($sorton == 'profession')
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.org_name, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY whois.profession ASC, whois.level DESC, whois.defender_rank_id DESC";
}
else if ($sorton == 'level')
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.org_name, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY whois.level DESC, whois.defender_rank_id DESC, whois.profession ASC";
}
else if ($sorton == 'org_name')
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.org_name, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY whois.org_name ASC, online.nickname ASC";
}
else if ($sorton == 'ai')
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.org_name, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY whois.level DESC, whois.defender_rank_id DESC, whois.profession ASC";
}
else
{
$query = "SELECT online.nickname, whois.profession ,whois.level, whois.org_name, whois.defender_rank_id FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY whois.profession ASC, whois.level DESC, online.nickname ASC";
}
$result = mysql_query($query) or die('error!<br>'.mysql_error());
$total_rows = mysql_num_rows( $result );
$content = "<div style=\"text-align: center\">";
$content = $content . "<a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=default\">There are <b>$total_rows</b> people online</a><br /><br /></div>";
$content = $content . "<table border=1 cellspacing=0 style=\"margin-left:auto; margin-right:auto;\"> \n";
$content = $content . "<tr><td><b><a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=nickname\">Nickname</a></b></td></td><td><b><a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=profession\">Profession</a></b></td><td><b><a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=level\">Level</a></b></td><td><b><a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=org_name\">Org</a></b></td><td><b><a href=\"http://www.clan-ascension.org/onlinestatus.php?pid=1&sorton=ai\">AI</a></b></td></tr>";
while($onlinerow = mysql_fetch_row($result))
{
$content = $content . "<tr>";
foreach ( $onlinerow as $data )
$content = $content . "\t <td>$data</td>";
$content = $content . "</td>\n";
}
$content = $content . "</table>\n";
print $content;
mysql_free_result($result);
mysql_close($db);
?>
-
Cant get this to work, seems maybe it not ready for 0.6.5?
First when I use the script as is I get:
error!
Unknown column 'whois.profession' in 'field list'
So I check the whois table and online table and end up removing: whois.profession and whois.defender_rank_id as those doesnt exist, rest does though.. Now I get the error:
error!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickna' at line 1
"
/usr/bin/mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2
-
PHP 5.2.6-1+lenny3 with Suhosin-Patch 0.9.6.2 (cli) (built: Apr 26 2009 22:16:23)
Copyright (c) 1997-2008 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2008 Zend Technologies
"
-
post full sql pls :)
-
Hmm dont think that will help? Its the default .sql thats created when installing BeBot 0.6.5, just lots of data filled. Would prefer to not post the whole .sql due to information in it, but what I see regarding the problem is that for example whois.profession would probably be whois.craft1 and whois.craft2
--
-- Table structure for table `whois`
--
CREATE TABLE IF NOT EXISTS `whois` (
`ID` bigint(20) NOT NULL,
`nickname` varchar(15) NOT NULL default '',
`level` tinyint(3) unsigned NOT NULL default '1',
`class` enum('Commoner','Conqueror','Dark Templar','Guardian','Bear Shaman','Priest of Mitra','Scoin of Set','Tempest of Set','Assassin','Barbarian','Ranger','Demonologist','Herald of Xotli','Lich','Necromancer') NOT NULL default 'Commoner',
`craft1` enum('','Alchemist','Architect','Armorsmith','Gemcutter','Weaponsmith','None') default NULL,
`craft2` enum('','Alchemist','Architect','Armorsmith','Gemcutter','Weaponsmith','None') default NULL,
`location` int(15) NOT NULL default '0',
`online` tinyint(1) NOT NULL default '0',
`used` bigint(25) NOT NULL default '0',
`org_rank_id` int(15) NOT NULL default '0',
`org_rank` int(15) NOT NULL default '0',
`org_name` varchar(15) default '',
`updated` int(10) NOT NULL default '0',
PRIMARY KEY (`nickname`),
KEY `ID` (`ID`),
KEY `Class` (`class`),
KEY `updated` (`updated`),
KEY `used` (`used`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
But then again, I got past this issue by removing the references to the non-existing tables.
Main problem now seems to be the query lines, or maybe not? :p
error!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickna' at line 1
-
i meant whole sql query you are useing:)
-
$query = "SELECT online.nickname, whois.level, whois.org_name, FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY online.nickname ASC";
-
Corrected:
$query = "SELECT online.nickname, whois.level, whois.org_name FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY online.nickname ASC";
You got a "," to much after "whois.org_name,".. This was caused when you removed the other values...
-
Corrected:
$query = "SELECT online.nickname, whois.level, whois.org_name FROM online, whois WHERE online.status_gc = 1 AND online.nickname = whois.nickname ORDER BY online.nickname ASC";
You got a "," to much after "whois.org_name,".. This was caused when you removed the other values...
Now look at that :) Solved it
-
thats why i wanted you to post sql :)
-
module for bot, you need to make how it is saved and displayed on your web
http://www.cro-guild.com/bebot/Whoisingame.php.txt
-
Hey .. Is there anyone who can make a html or php file. who works on the
http://www.cro-guild.com/?c=bebot&m=view&title=Whoisingame