BeBot - An Anarchy Online and Age Of Conan chat automaton

Archive => Anarchy Online Archive => AO 0.6 support => Topic started by: Riccarr on June 02, 2009, 10:44:26 pm

Title: Online query ?
Post by: Riccarr on June 02, 2009, 10:44:26 pm

Hello,

I want to create a query to run from our website to get a list of in-game online players. Something straightforward as ...

SELECT nickname,class,level FROM whois WHERE  online = 1

Seems to bring back an incorrect list; it even shows people who are not in the guild.

Anyone want to supply a SQL to get the list of current on-line guild members? This is for Age of Conan.

Thanks,
Eric

Title: Re: Online query ?
Post by: Slacklin on June 03, 2009, 08:49:04 am

This is what I use for AO, should be just about the same though.

Code: [Select]

SELECT online.nickname, whois.profession, whois.level
FROM online, whois
WHERE online.status_gc = 1 AND online.nickname = whois.nickname
ORDER BY whois.profession ASC, whois.level DESC, online.nickname ASC
The entire script is as follows. Yes I know I am not using php to sort, mysql worked just fine since I want the list to update anytime a link is clicked....

Code: [Select]

<?php 
$dbhost = 'host:port';
$dbuser = 'username';
$dbpass = 'password';

$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die                      ('Error connecting to mysql2');

$dbname = 'database';
mysql_select_db($dbname) 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);
$total_rows = mysql_num_rows( $result );
$content = "<div style=\"text-align: center\">";
$content = $content . "<a href=\"http://www.orgsite.com/index.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.orgsite.com/index.php?pid=1&sorton=nickname\">Nickname</a></b></td></td><td><b><a href=\"http://www.orgsite.com/index.php?pid=1&sorton=profession\">Profession</a></b></td><td><b><a href=\"http://www.orgsite.com/index.php?pid=1&sorton=level\">Level</a></b></td><td><b><a href=\"http://www.orgsite.com/index.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($conn);
?>

Title: Re: Online query ?
Post by: Riccarr on June 03, 2009, 10:10:14 pm

hey, thanks. That helps.

BTW ... how/why do non-guild members get in the whois table? Is that because someone did a !whois command on them, or they interacted (tells) with them?

Title: Re: Online query ?
Post by: Alreadythere on June 03, 2009, 11:27:34 pm

Whois queries (on the table) are done at several places in the bot.

Basically everytime someone interacts directly with the bot, either in guild chat or tells, will create an entry in the whois table.

And all whois queries, and maybe some other queries, will create entries.

Title: Re: Online query ?
Post by: Tomate on June 12, 2010, 09:34:57 pm

I am getting an error here.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/online.php on line 42

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /var/www/online.php on line 47

Warning: mysql_free_result(): supplied argument is not a valid MySQL result resource in /var/www/online.php on line 55

Title: Re: Online query ?
Post by: Getrix on June 12, 2010, 09:53:20 pm

Here is a online module i made for using in a SMF SimplePortal block:
http://dump.sjef.biz/aoc/bebot/Online.phps