Adding Stats table to EQEmuLS For display on a webpage

[norsan]

first prepare the database

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

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

	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

NewServer.Status.num_zones = 0;

Add

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

At Line 356 after

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

Add

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

At Line 533 uncomment

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

At line 564 after

Iterator = EQServers.erase(Iterator);

Add

_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!