Adding Stats table to EQEmuLS For display on a webpage

norsan · #1 08-03-2009, 10:34 AM

first prepare the database

Code:

CREATE TABLE IF NOT EXISTS `LSStats` (
  `ServerID` int(10) unsigned NOT NULL default '0',
  `worldname` varchar(201) NOT NULL default '',
  `numplayers` int(10) unsigned NOT NULL default '0',
  `numzones` int(10) unsigned NOT NULL default '0',
  `worldstatus` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ServerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Now at the end of /EQEmuLoginServer/EQEmuDatabase.cpp add the following

Code:

void EQEmuDatabase::AddWorldServerStatus(uint32 serverId, std::string longName)
{
	if(!_mysql)
	{
		cout << "NOT CONNECTED TO MYQSL: " << endl;
		return;
	}

	char * mQuery = 0;
	MakeAnyLenString(&mQuery, "INSERT INTO LSStats VALUES(%u, '%s', 0, 0, 3)", serverId,  longName.c_str());
	if (mysql_query(_mysql, mQuery)) 
	{
		safe_delete_array(mQuery);
		cout << "Query failed: " << mQuery << endl;
	}
	safe_delete_array(mQuery);
	return;
}

void EQEmuDatabase::UpdateWorldServerStatus(uint32 serverId, sint32 players, sint32 zones, sint32 status)
{
	if(!_mysql)
	{
		cout << "NOT CONNECTED TO MYQSL: " << endl;
		return;
	}

	char * mQuery = 0;
	MakeAnyLenString(&mQuery, "UPDATE LSStats SET numplayers=%i, numzones=%i, worldstatus=%i WHERE serverid=%u", players, zones, status, serverId);
	if (mysql_query(_mysql, mQuery)) 
	{
		safe_delete_array(mQuery);
		cout << "Query failed: " << mQuery << endl;
	}
	safe_delete_array(mQuery);
	return;
}

void EQEmuDatabase::RemoveWorldServerStatus(uint32 serverId)
{
	if(!_mysql)
	{
		cout << "NOT CONNECTED TO MYQSL: " << endl;
		return;
	}

	char * mQuery = 0;
	MakeAnyLenString(&mQuery, "DELETE FROM LSStats WHERE serverid=%u", serverId);
	if (mysql_query(_mysql, mQuery)) 
	{
		safe_delete_array(mQuery);
		cout << "Query failed: " << mQuery << endl;
	}
	safe_delete_array(mQuery);
	return;
}

Now in /EQEmuLoginServer/EQEmuDatabase.h around line 62 add

Code:

	void AddWorldServerStatus(uint32 serverId, string longName);
	void UpdateWorldServerStatus(uint32 serverId, sint32 players, sint32 zones, sint32 status);
	void RemoveWorldServerStatus(uint32 serverId);

Finally in /EQEmuLoginserver/ServerList.cpp
around line 340 After

Code:

NewServer.Status.num_zones = 0;

Add

Code:

_db->RemoveWorldServerStatus(NewServer.WorldServerReg->GetServerID());
	_db->AddWorldServerStatus(NewServer.WorldServerReg->GetServerID(), NewServer.WorldServerReg->GetLongServerName());

At Line 356 after

Code:

(*Iterator).Status.num_zones = Status->num_zones;

Add

Code:

_db->UpdateWorldServerStatus((*Iterator).WorldServerReg->GetServerID(), (*Iterator).Status.num_players, (*Iterator).Status.num_zones, (*Iterator).WorldServerReg->GetServerListTypeID());

At Line 533 uncomment

Code:

RemoveServerEntry((*Iterator)->GetrIP());

At line 564 after

Code:

Iterator = EQServers.erase(Iterator);

Add

Code:

_db->RemoveWorldServerStatus((*Iterator).WorldServerReg->GetServerID());

I reccomend you compile in RELEASE (Seems to work much better for me)

What this will do is allow you to pull the information for all connected world servers (worldstatus field is the list id in the serverlisttype table so you can easily show prefered legends or standard server on your web page) the number of players logged in to the world playing currently and how many zones are loaded on the world server. It automaticly updates so it only shows the worlds currently online ( It should atleast i havent really tested that part to make sure its removing the entry for disconnected worlds from the db)

Enjoy!

norsan · #2 08-03-2009, 10:35 AM

NOTE Special thanks go to sesmar for helping me with this ( he did most of the work)

norsan · #3 08-03-2009, 10:55 AM

Below is a simple stats script for your site ( I used a dbi i already had written hence the class for it)

Code:

<head>
<style type =text/css>
BODY {
scrollbar-arrow-color : goldenrod; 
scrollbar-base-color : #000000; 
scrollbar-dark-shadow-color : #000000; 
scrollbar-face-color : #000000; 
scrollbar-highlight-color : silver; 
scrollbar-shadow-color : silver; 
scrollbar-3d-light-color : ;
scrollbar-track-color:#000000;
background-color: #000000;
background-image: url();
background-attachment: fixed;
color:#FFFFFF; 
font-style:None; 
font-family:Arial; 
font-weight:none;
font-size:13px;
text-decoration: ;
}

</style></head><body>
Login Server Information:<BR>
Login Server Name :Name of your loginserver<Br>
Registration URL: Url of registration Page<Br>
EQHost.txt Information: addressofls:5998<hr>
<?PHP
class DB

     {

        ///Declaration of variables



        var $host="localhost";

        var $user="wisler";

        var $password="cdered";

        var $persist=false;

        var $database="wisler_ls1";



        var $conn=NULL;

        var $result=false;

        var $fields;

        var $check_fields;

        var $tbname;

        var $addNewFlag=false;

        ///End



        function addNew($table_name)

        {

           $this->fields=array();

           $this->addNewFlag=true;

           $this->tbname=$table_name;

        }

        function edit($table_name)

        {

           $this->fields=array();

           $this->check_fields=array();

           $this->addNewFlag=false;

           $this->tbname=$table_name;

        }

        function update()

        {

         foreach($this->fields as $field_name=>$value)

           $qry.=$field_name."='".$value."',";

         $qry=substr($qry,0,strlen($qry)-1);



          if($this->addNewFlag)

            $qry="INSERT INTO ".$this->tbname." SET ".$qry;

          else

          {

           $qry="UPDATE ".$this->tbname." SET ".$qry;

           if(count($this->check_fields)>0)

           {

               $qry.=" WHERE ";

               foreach($this->check_fields as $field_name=>$value)

                   $qry.=$field_name."='".$value."' AND ";

               $qry=substr($qry,0,strlen($qry)-5);

           }

          }



         return $this->query($qry);

        }



        function DB($host="",$user="",$password="",$dbname="",$open=false)

        {

         if($host!="")

            $this->host=$host;

         if($user!="")

            $this->user=$user;

         if($password!="")

            $this->password=$password;

         if($dbname!="")

            $this->database=$dbname;



         if($open)

           $this->open();

        }

        function open($host="",$user="",$password="",$dbname="") //

        {

         if($host!="")

            $this->host=$host;

         if($user!="")

            $this->user=$user;

         if($password!="")

            $this->password=$password;

         if($dbname!="")

            $this->database=$dbname;



         $this->connect();

         $this->select_db();

        }

        function set_host($host,$user,$password,$dbname)

        {

         $this->host=$host;

         $this->user=$user;

         $this->password=$password;

         $this->database=$dbname;

        }

        function affectedRows() //-- Get number of affected rows in previous operation

        {

         return @mysql_affected_rows($this->conn);

        }

        function close()//Close a connection to a  Server

        {

         return @mysql_close($this->conn);

        }

        function connect() //Open a connection to a Server

        {

          // Choose the appropriate connect function

          if ($this->persist)

              $func = 'mysql_pconnect';

          else

              $func = 'mysql_connect';



          // Connect to the database server

          $this->conn = $func($this->host, $this->user, $this->password);

          if(!$this->conn)

             return false;



        }

        function select_db($dbname="") //Select a databse

        {

          if($dbname=="")

             $dbname=$this->database;

          mysql_select_db($dbname,$this->conn);

        }

        function create_db($dbname) //Create a database

        {

          return @mysql_create_db($dbname,$this->conn);

        }

        function drop_db($dbname) //Drop a database

        {

         return @mysql_drop_db($dbname,$this->conn);

        }

        function data_seek($row) ///Move internal result pointer

        {

         return mysql_data_seek($this->result,$row);

        }

        function error() //Get last error

        {

            return (mysql_error());

        }

        function errorno() //Get error number

        {

            return mysql_errno();

        }

        function query($sql = '') //Execute the sql query

        {

            $this->result = @mysql_query($sql, $this->conn);

            return ($this->result != false);

        }

        function numRows() //Return number of rows in selected table

        {

            return (@mysql_num_rows($this->result));

        }



		function fieldName($field)

        {

           return (@mysql_field_name($this->result,$field));

        }



		function insertID()

        {

            return (@mysql_insert_id($this->conn));

        }

        function fetchObject()

        {

            return (@mysql_fetch_object($this->result, MYSQL_ASSOC));

        }

        function fetchArray()

        {

            return (@mysql_fetch_array($this->result));

        }

        function fetchAssoc()

        {

            return (@mysql_fetch_assoc($this->result));

        }

        function freeResult()

        {

            return (@mysql_free_result($this->result));

        }

        function fetchRow()

        {

            return (@mysql_fetch_row($this->result));

        }

        function tablesList()

        {

            return (@mysql_list_tables($this->result));

        }

     }

	$db=new DB();
	$db->open();
		$query = "SELECT * FROM LSStats WHERE worldstatus = '1'";
		$result = $db->query($query);
		$num_results = $db->numRows($result);
		if ($DEBUG) echo $query . "<br>\n";
		if ($DEBUG) echo('Invalid query: ' . mysql_error() . "<br>\n");
		echo ('<font size=+2>Legends Servers:<font>
		 <table width=100%><tr>
			<td valigh=top width=100%>World Name:</td>
			<td width=100%>Player Count</td>
			<td valigh=top width=100>Total Zones</td></tr><tr>
			<td>
			');
		for ($i=0; $i <$num_results; $i++)
		{
			$row = $db->fetchArray($result);
			$color = '<font color=#618c3c><B><i>';
			echo('<tr>
			<td>
			'.$color.'
			'.$row[worldname].'	
			</b></i></font></td>
						<td>'.$row[numplayers].'</td>
						<td>'.$row[numzones].'</td></tr>

            </td>
        </tr>
');
		}
		echo '</table><hr>';
		$query1 = "SELECT * FROM LSStats WHERE worldstatus = '2'";
		$result1 = $db->query($query1);
		$num_results1 = $db->numRows($result1);
		if ($DEBUG) echo $query1 . "<br>\n";
		if ($DEBUG) echo('Invalid query: ' . mysql_error() . "<br>\n");
		echo ('<font size=+2>Prefered Servers:</font>
		 <table width=100%><tr>
			<td valigh=top width=100%>World Name:</td>
			<td width=100%>Player Count</td>
			<td valigh=top width=100>Total Zones</td></tr><tr>
			<td>
			');
		for ($i=0; $i <$num_results1; $i++)
		{
			$row1 = $db->fetchArray($result1);
			$color1 = '<font color=#618c3c><B><i>';
			echo('<tr>
			<td>
			'.$color1.'
			'.$row1[worldname].'	
			</b></i></font></td>
						<td>'.$row1[numplayers].'</td>
						<td>'.$row1[numzones].'</td></tr>

            </td>
        </tr>
');
		}
		echo '</table><hr>';
		$query2 = "SELECT * FROM LSStats WHERE worldstatus = '3'";
		$result2 = $db->query($query2);
		$num_results2 = $db->numRows($result2);
		if ($DEBUG) echo $query . "<br>\n";
		if ($DEBUG) echo('Invalid query: ' . mysql_error() . "<br>\n");
		echo ('<font size=+2>Standard Servers:</font>
		 <table width=100%><tr>
			<td valigh=top width=100%>World Name:</td>
			<td width=100%>Player Count</td>
			<td valigh=top width=100>Total Zones</td></tr><tr>
			<td>
			');
		for ($i=0; $i <$num_results2; $i++)
		{
			$row2 = $db->fetchArray($result2);
			$color2 = '<font color=#ffffff><B><i>';
			echo('<tr>
			<td>
			'.$color2.'
			'.$row2[worldname].'	
			</b></i></font></td>
						<td>'.$row2[numplayers].'</td>
						<td>'.$row2[numzones].'</td></tr>

            </td>
        </tr>
');
		}
		echo '</table><hr>';
		
	?></body>