Author Topic: Online query ? (Read 7031 times)

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

Slacklin · « **Reply #1 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);
?>

Riccarr · « **Reply #2 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?

Alreadythere · « **Reply #3 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.

Tomate · « **Reply #4 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