[RELEASE] Script - Number of Players Online

[bulgaria]

Admin can see much faster and easier certain things like
list of online players ,Levels, IP addresses if use script:

PHP Code:

<?PHP

$dbhost=localhost
$dbuser=Shaiya
$dbpass=Shaiya123

// connect to SQL server
$link = mssql_connect("$dbhost","$dbuser","$dbpass") 
    or die ("Failed to connect to MSSQL server.");

// check who's online
$result  = mssql_query("SELECT  c.Family,c.CharName,c.Level,u.UserIP 
                        FROM [PS_GameData].[dbo].[Chars] c INNER JOIN
                       [PS_UserData].[dbo].[Users_Master] u ON 
                       c.UserUID=u.UserUID WHERE c.LoginStatus=1
                       ORDER BY u.UserIP ",$link);
                       
// initialize veriables
$online = mssql_num_rows($result);
$human = 0;
$elf = 0;
$vail = 0;
$nordein = 0;
    
if ($online==0) echo "empty server :)";
else
{
  // fill variables with data (who's online)
    
  echo "<table cellspacing=1 cellpadding=2 color=\"red\" bordercolor=#FF9933 
   border=1 style=background-color:#999966 style=\"border-style:hidden;\">";
  while($row = mssql_fetch_array($result))
    {
    switch($row[0])
        {
        case 0: $human++;$color="green";break;
        case 1: $elf++;$color="green";break;
        case 2: $vail++;$color="red";break;
        case 3: $nordein++;$color="red";break;
        };
    // Print CharName,Level,CharIP of each online player    
    echo "<tr><td><font color=\"$color\">".$row[1]."</font></td><td>
        ".$row[2]."</td><td>&nbsp;&nbsp;&nbsp;".$row[3]."</td></tr>";
   }
    echo "</table></br>";
    
    // Print number of online players
    echo "<table cellspacing=1 cellpadding=2 color=\"red\" bordercolor=#FF9933 
       border=1 style=background-color:#999966 style=\"border-style:hidden;\">
          <tr><td>Players online: </td><td>".$online."</td></tr>
          <tr><td>Human: </td><td>".$human."</td></tr>
          <tr><td>Elf: </td><td>".$elf."</td></tr>
          <tr><td>Vail: </td><td>".$vail."</td></tr>
          <tr><td>DeathEater: </td><td>".$nordein."</td></tr>
          </table>";
}
mssql_close($link);
?>

[[Dev]Ansem]

php script for showing IP adresses?
for what is it good for when admins can see all this stuff in the db itself?

[JohnHeatz]

Certainly for an admin control panel; this is something that is actually going to help me on some little things I am working on right now.

[bulgaria]

Yes, this little script can be developed further by adding a dropdown menu by adding dates of creation of characters, etc. In this variant, the program displays the players playing in multiple windows(same IP addresses), and they are 90% of potential cheaters. To correct readings are always "online" should be approached very carefully to everything written in previous posts colleagues.

[[GM]Tinkerbell]

Ok firstly really sorry to necro
But i am having a small issue, the script is working on website (thx) but for some reason my loginstatus is not changing from 0 thus showing as noone online

Any fix for this I ran both procs and obviously the add loginstatusit just doesnt seem to work for me

[bulgaria]

There is a very simple solution, when a player goes to character selection screen instead of closing the game window. Add to PS_GameLog usp_Insert_Action_Log_E following lines:

IF (@ActionType = 108)
BEGIN
UPDATE PS_GameData.dbo.Chars SET LeaveDate=GETDATE(), LoginStatus=0 WHERE CharID=@CharID
END

[[Dev]#Closed#]

Invalid column name 'LoginStatus'.

[RebeccaBlack]

Originally Posted by ProfNerwosol

Instructions:

Note that for it to work, you need to alter two procedures in PS_GameData and add one colum in PS_GameData.dbo.Chars, LoginStatus. Files with sql code are included.

Originally Posted by [Dev]#Closed#

Invalid column name 'LoginStatus'.

Do people read anymore? Or do they expect everything to be handed to them?

[[Dev]#Closed#]

ty^^
now Column names in each table must be unique. Column name 'LoginStatus' in table 'dbo.Chars' is specified more than once.

[RebeccaBlack]

PHP Code:

USE PS_GameData
IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = 'LoginStatus' AND object_id = OBJECT_ID('Chars'))
BEGIN
    ALTER TABLE Chars
        ADD LoginStatus bit
    PRINT 'Column successfully added!'
END
ELSE
BEGIN
    PRINT 'Column already exists!'
END 


[erickreq]

one? if I mark all and puts me in total = -1 or want to know why is so and not put the total of those who are online

[danatael]

Sorry if I'm upping an old topic, but I have convert mssql of the author to odbc because mssql dont work on latest php releases. It's for you devs

<?php
// replace with your own settings
$dbhost = '127.0.0.1';
$dbuser = 'Shaiya';
$dbpwd = 'shaiya123';

$conn = @odbc_connect("Driver={SQL Server};Server=$Host", $User, $Password);


// check who's online class by class
$result = @odbc_exec($conn,"SELECT LoginStatus, Family FROM PS_GameData.dbo.Chars WHERE LoginStatus=1");
$result1 = @odbc_exec($conn,"SELECT LoginStatus, Family FROM PS_GameData.dbo.Chars WHERE LoginStatus=1 AND Family=0");
$result2 = @odbc_exec($conn,"SELECT LoginStatus, Family FROM PS_GameData.dbo.Chars WHERE LoginStatus=1 AND Family=1");
$result3 = @odbc_exec($conn,"SELECT LoginStatus, Family FROM PS_GameData.dbo.Chars WHERE LoginStatus=1 AND Family=2");
$result4 = @odbc_exec($conn,"SELECT LoginStatus, Family FROM PS_GameData.dbo.Chars WHERE LoginStatus=1 AND Family=3");

// initialize veriables
$online = @odbc_num_rows($result);
$human = @odbc_num_rows($result1);
$elf = @odbc_num_rows($result2);
$vail = @odbc_num_rows($result3);
$nordein = @odbc_num_rows($result4);

// Print number of online players
print "<table style=\"border: 1px solid black;\">
<tr><td colspan=2>Players online: </td><td>".$online."</td></tr>
<tr><td>Human: </td><td>".$human."</td></tr>
<tr><td>Elf: </td><td>".$elf."</td></tr>
<tr><td>Vail: </td><td>".$vail."</td></tr>
<tr><td>DeathEater: </td><td>".$nordein."</td></tr>
</table>";

@odbc_close($conn);

?>

[sominus]

Correct me if I'm wrong:

When a player quits the game using ALT + F4, LoginStatus won't get updated and will remain '=1' and that will give you a false amount of "online players"

May be you could use the 'Leave' field (in PS_Userdata.dbo.Users_master) to check online status. (there's a post from Castor with more details about this field)

[castor4878]

You are right.

The 'LoginStatus' field simply does not exist in regular Chars table.

So there is obviously no way to have such script working unless one fullfill tons of prerequesites no listed there, nor in the full thread.

From a strict SQL point of view, performing 5 identical requests selecting fields that are never used is very awkward.

The information shall be obtained (assuming the required & not documented changes) by:

SELECT Family,count(*) FROM PS_GameData.dbo.Chars WHERE LoginStatus=1 GROUP BY Family

thinking that ODBC is smart is an error (and btw it is NEVER mandatory to use it, native drivers always work), thinking that transfering kilos or megabytes of useless recordsets has no cost is also an error.

[danatael]

If you can read all posts of this topic, you can find a fix for the loginstatus bug when player disconnected with atl+f4. It is a sql update which creates a trigger which uses the error UserLog.
If someone gets disconnected the server sends error values in this log,
this Trigger sets the User LoginStatus that got DCed back to 0 if a new error row gets added. ()

So my php script work perfectly.

ps: sorry for my english