Chat, GUID, Stats and Mapstats Logger[1.1.0.1][BF3]

[ImportBot]

Originally Posted by ty_ger07*:

 

I would love to use this plugin, but I keep having this error:

 

 

Error: System.Security.SecurityException: Request for the permission of type 'System.Data.Odbc.OdbcPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

at System.Security.CodeAccessSecurityEngine.Check(Obj ect demand, StackCrawlMark& stackMark, Boolean isPermSet)

at System.Security.PermissionSet.Demand()

at System.Data.Common.DbConnectionOptions.DemandPermi ssion()

at System.Data.Odbc.OdbcConnection.PermissionDemand()

at System.Data.Odbc.OdbcConnectionFactory.PermissionD emand(DbConnection outerConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.Odbc.OdbcConnection.Open()

at PRoConEvents.CChatGUIDStatsLoggerBF3.tablebuilder( )

The action that failed was:

Demand

The type of the first permission that failed was:

System.Data.Odbc.OdbcPermission

The Zone of the assembly that failed was:

MyComputer

 

 

I have a MySql database v 5.1 on a web server. I have pointed the plugin to the correct web address and included the db name and password.

 

What is the problem? Do you know?

[ImportBot]

Originally Posted by FARSTAN*:

 

I also, at each round of changes, I have this message

 

Code:

[19:50:04 20] Error: SQLQuery:System.InvalidOperationException: ExecuteReader nécessite une connexion ouverte et disponible. La connexion est actuellement fermée.
   à System.Data.Odbc.OdbcConnection.SetStateExecuting(String method, OdbcTransaction transaction)
   à System.Data.Odbc.OdbcCommand.ValidateConnectionAndTransaction(String method)
   à System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   à System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   à System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
   à System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
   à System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   à System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   à System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   à System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   à PRoConEvents.CChatGUIDStatsLoggerBF3.SQLquery(OdbcCommand selectQuery)
[19:50:04 73] Error: Error in Startstreaming: 
[19:50:04 73] Index #0
[19:50:04 75] Message: [MySQL][ODBC 5.1 Driver][mysqld-5.1.56]Duplicate entry 'EA_045C12921D002D38C94482A7712E46A5' for key 'UNIQUE_playerdata'
[19:50:04 75] Native: 1062
[19:50:04 76] Source: myodbc5.dll
[19:50:04 76] SQL: 23000

[ImportBot]

Originally Posted by XpKiller*:

 

@Quax

This is no direct error of the plugin. This weapons are just missing in the procon weapon definition.

This no serious error. It will go away a soon as the bf3.def file get fixed.

 

@ty_ger07

Turn off the proconsandbox or add you mysqlserver to the trusted hosts.

 

@FARSTAN

This error can have multiply causes it could be a programm error but also an error of the mysql server (droped connections).

The plugin checks the connection before querying to prevent such an error i musst see if i have made a mistake in this point.

As i said in dont get any error since more than a week on my test platform.

[ImportBot]

Originally Posted by CptChaos*:

 

I seem to have the weaponstatslogging working, although I can't visually see any change if I would disable it. Oh well, it works. Could be a Procon issue, because I also had the problem with beta 5, but not before I reinstalled Procon.

 

I also have another question, to make it easy for me to write and in somewhat SQL: tbl_playerdata.PlayerID == tbl_playerstats.StatsID?

[ImportBot]

Originally Posted by ty_ger07*:

 

@ty_ger07

Turn off the proconsandbox or add you mysqlserver to the trusted hosts.

Thank you!

 

I am much closer to getting this plugin set up now!

 

I added the server as a trusted host in the PRoCon layer server.

[ImportBot]

Originally Posted by ty_ger07*:

 

It still seems to be having trouble updating the stats table.

 

I get this error after rounds are finished:

 

EDIT: To clarify, I am using MySql v 5.0 now instead of v 5.1. My regular server host only allows v 5.0. I tried on a free server host with v 5.1, but they didn't seem to allow external connections to the database. Are there errors below caused by trying to use v 5.0 MySql database tables instead of v 5.1+?

 

 

[18:35:45 24] Error: Error in Startstreaming:

[18:35:45 24] Index #0

[18:35:45 24] Message: [MySQL][ODBC 5.1 Driver][mysqld-5.0.91-log]Cannot add or update a child row: a foreign key constraint fails (`bf3proconstats/tbl_server_player_16_slot`, CONSTRAINT `fk_tbl_server_player_tbl_server_16_slot` FOREIGN KEY (`ServerID`) REFERENCES `tbl_server_16_slot` (`ServerID`) ON DELETE CASCADE ON UPDA)

[18:35:45 24] Native: 1452

[18:35:45 24] Source: myodbc5.dll

[18:35:45 24] SQL: HY000

 

The only tables which are updating are the chat tables and the player tables. All the stats tables have no entries and every time I type it !stats it says to wait until next round for stats.

 

 

EDIT:

 

And another error:

 

 

Error: SQLQuery:System.NullReferenceException: Object reference not set to an instance of an object.

at System.Data.Odbc.OdbcDataReader.GetRowCount()

at System.Data.Odbc.OdbcDataReader.FirstResult()

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method, Boolean needReader)

at System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior behavior)

at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

at PRoConEvents.CChatGUIDStatsLoggerBF3.SQLquery(Odbc Command selectQuery)

 

Would it be better if I placed the two server stats on separate databases? Right now I have one with the suffix of _16_slot and one with the suffix of _32_slot.

[ImportBot]

Originally Posted by traustic*:

 

Anyone working on a webpage for showing these stats? I would love something like the statistics of codwaw for this game. We could use the data collected off this plugin to create a stats page for anyone to reference.

 

Something like this?

http://www.mcgamers.net/ultrastatsCOD5/index.php?

[ImportBot]

Originally Posted by Quax*:

 

[18:35:45 24] Error: Error in Startstreaming:

[18:35:45 24] Index #0

[18:35:45 24] Message: [MySQL][ODBC 5.1 Driver][mysqld-5.0.91-log]Cannot add or update a child row: a foreign key constraint fails (`bf3proconstats/tbl_server_player_16_slot`, CONSTRAINT `fk_tbl_server_player_tbl_server_16_slot` FOREIGN KEY (`ServerID`) REFERENCES `tbl_server_16_slot` (`ServerID`) ON DELETE CASCADE ON UPDA)

[18:35:45 24] Native: 1452

[18:35:45 24] Source: myodbc5.dll

[18:35:45 24] SQL: HY000

 

These error I had yesterday the first time I updated to beta 0.6. Don't know how I fixed the problem but, I deactivated it for both layers, made a restart of Procon and suddenly it works. My first idea was that it has a problem due to the fact that two layers working on one database. Will give feedback if it occours again....

[ImportBot]

Originally Posted by BadBen*:

 

Please help help me please in german me english is to bad

 

10:19:47 04] BF3 Chat, GUID and Stats Logger Enabled

[10:19:47 04] BF3 Chat, GUID and Stats Logger: Floodprotection set to 10 Request per Round for each Player

[10:20:07 07] Error in Tablebuilder:

[10:20:07 07] Index #0

[10:20:07 08] Message: [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben

[10:20:07 08] Native: 0

[10:20:07 08] Source:

[10:20:07 08] SQL: IM002

[ImportBot]

Originally Posted by traustic*:

 

This link below goes to post about the admin page that shows information from your plugin:

www.phogue.net/forumvb/showth...Stats-Database*

 

The php is programmed originally for BFBC2 and there are a few changes in your plugin for BF3 that break the php's functionality.

 

 

Could you rename the rows in the database to the following on future releases?

 

Table tbl_playerdata

Row PBGUID is now GUID

 

Table tbl_playerstats

Row kills is now playerkills

Row headshots is now playerheadshots

Row deaths is now playerdeaths

Row playtime is now playerplaytime

Row score is now playerscore

Row rounds is now playerrounds

 

If these changes are made to your awesome plugin for stat logging then the admin pages can be used for BF3 instead of just BFBC2.

[ImportBot]

Originally Posted by MorpheusX(AUT)*:

 

You haven't installed the ODBC-driver or failed to configure it correctly.

Du hast den ODBC-Treiber nicht installiert, oder ihn falsch eingerichtet.

[ImportBot]

Originally Posted by Quax*:

 

One simple problem do I have when querying the tables.

 

There must be different playerid's for the same players on two different servers. Otherwise you will see the wrong stats.

I tried to query server one and get the summary stats of one player on both servers.....

 

Hope that I made the mistake.

BTW: I queried via Access, just for Testing....

 

AHHH I FOUND THE MISTAKE, JUST TAKE THE STATS-ID AND IT WORKS......

 

Regards Quax (Der Bruchpilot)

[ImportBot]

Originally Posted by Clay52*:

 

Still getting this often, but plugin seems to still be working fine otherwise:

 

[11:21:27 89] Error: SQLQuery:System.ObjectDisposedException: Safe handle has been closed

at System.StubHelpers.StubHelpers.SafeHandleC2NHelper (Object pThis, IntPtr pCleanupWorkList)

at System.Data.Common.UnsafeNativeMethods.SQLExecDire ctW(OdbcStatementHandle StatementHandle, String StatementText, Int32 TextLength)

at System.Data.Odbc.OdbcStatementHandle.ExecuteDirect (String commandText)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method, Boolean needReader)

at System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior behavior)

at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

at PRoConEvents.CChatGUIDStatsLoggerBF3.SQLquery(Odbc Command selectQuery)

[11:21:28 06] Error: Error in Startstreaming:

[11:21:28 06] Index #0

[11:21:28 06] Message: [MySQL][ODBC 5.1 Driver][mysqld-5.1.56]Duplicate entry 'EA_67B3BD4947E4151E02897CAB9B0CB09A' for key 'UNIQUE_playerdata'

[11:21:28 06] Native: 1062

[11:21:28 06] Source: myodbc5.dll

[11:21:28 07] SQL: 23000

[ImportBot]

Originally Posted by XpKiller*:

 

@CptChaos

That not so easy PlayerID != StatsID

 

PlayerID + ServerID --> will form a StatsID

 

So SQL Query looks like this:

SELECT tpd.*, ts.*, tps.*

FROM tbl_server_player tsp

INNER JOIN tbl_playerstats tps ON tsp.StatsID = tps.StatsID

INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID

INNER JOIN tbl_server ts ON tsp.ServerID = ts.ServerID

WHERE tpd.SoldierName = "CptChaos";

 

This i just an example but would work.

 

 

@ty_ger07

1. i have not tested on 5.0 because its very old. The InnoDB in such an old MySQL Version might be bugged because it not the default db engine in mySQL 5.0.

 

2.Please use the suffix only if necessary. There is no need to make a db for each server. The Stats dont get mixed within the db.

 

@Quax

Like ty_ger07 you have an old version of mysql too. This could be problem but i dont think that. In general there is no problem with multiply Layers using the same db. Please dont split up or use an tablesuffix! this would only cost performance.

 

@traustic

i will not change any column names. Also it wont help because the admin pages wont still not work properly because the data inside the is organized a little bit different.

 

@MorpheusX(AUT)

Thx for post :-)

ur are right BadBen you odbc driver is not configured properly.

 

Puh many questions.

[ImportBot]

Originally Posted by ty_ger07*:

 

I am seeing a lot of these sorts of errors:

 

Error: SQLQuery:System.NullReferenceException: Object reference not set to an instance of an object.

at System.Data.Odbc.OdbcDataReader.GetRowCount()

at System.Data.Odbc.OdbcDataReader.FirstResult()

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method, Boolean needReader)

at System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior behavior)

at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

at PRoConEvents.CChatGUIDStatsLoggerBF3.SQLquery(Odbc Command selectQuery)

EDIT: I am going to try to disable the instant chat logging feature. It seems that my server host (godaddy.com) isn't always reachable and sometimes the request times out. Maybe the constant querying is causing problems.

 

EDIT3: Disabling the instant chat logging feature seems to have caused the error above to go away.

 

EDIT2: Another error.

 

Error: SQLQuery:System.InvalidOperationException: The connection has been disabled. ---> System.Data.Odbc.OdbcException: ERROR [08S01] [MySQL][ODBC 5.1 Driver][mysqld-5.0.91-log]MySQL server has gone away

--- End of inner exception stack trace ---

at System.Data.Odbc.OdbcConnection.ConnectionIsAlive( Exception innerException)

at System.Data.Odbc.OdbcConnection.HandleErrorNoThrow (OdbcHandle hrHandle, RetCode retcode)

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHa ndle hrHandle, RetCode retcode)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(C ommandBehavior behavior, String method, Boolean needReader)

at System.Data.Odbc.OdbcCommand.ExecuteReader(Command Behavior behavior)

at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(C ommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbComman d.ExecuteReader(CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.FillInternal(Data Set dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

at PRoConEvents.CChatGUIDStatsLoggerBF3.SQLquery(Odbc Command selectQuery)

How long does the plugin wait to update the database before it times out and says the server has 'gone away'?

 

My godaddy.com websites (2) have always been pretty fast, but I do notice that requests to them randomly stall out from time to time for some reason. Is there a way to increase the timeout time in your plugin or make it try a second time if the first attempt fails?

[ImportBot]

Originally Posted by ty_ger07*:

 

After I have spewed all those error messages at you, I must say that your plugin is pretty impressive. It looks like one of the more complex plugins available and I am sure it took you a lot of work to make the plugin, refine it, and support it.

 

Thanks a lot!

[ImportBot]

Originally Posted by Quax*:

 

@Quax

Like ty_ger07 you have an old version of mysql too. This could be problem but i dont think that. In general there is no problem with multiply Layers using the same db. Please dont split up or use an tablesuffix! this would only cost performance.

As I declared, found the query fault by myself :-)

I saw that there was an n:n connection but didn't made it correct.

 

I'm using the newest MySQL Version and ODBC Connector.....

 

BTW: Yea its much better to use a single DB for several instances of gameservers. Better for Performance and to work with :-)

 

Till now....Great Job

 

Best Regards

[ImportBot]

Originally Posted by Clay52*:

 

So any comment as to why I get the error readout above?

 

Occasionally get this too:

 

[07:39:16 77] Error: Error in Startstreaming:

[07:39:16 77] Index #0

[07:39:16 79] Message: [MySQL][ODBC 5.1 Driver][mysqld-5.1.56]Duplicate entry 'EA_528F30C75EB5817C48A18416E6EAA47A' for key 'UNIQUE_playerdata'

[07:39:16 80] Native: 1062

[07:39:16 80] Source: myodbc5.dll

[07:39:16 80] SQL: 23000

[ImportBot]

Originally Posted by XpKiller*:

 

@Clay

Atm the problems seems only exists with remote sql server. Im looking currently for a way to make the code more robust.

 

@ty_ger07

Thanks

i learned c# with this plugin an SQL. Im getting better an better so Im rewriting many things to improve it. To bad that i dont like php. Also i dont have the time atm to write an webfrontend atm but i will help everybody if he want to.

[ImportBot]

Originally Posted by Clay52*:

 

Ok thanks, otherwise all seems to be working.

[ImportBot]

Originally Posted by FARSTAN*:

 

Hi

I have the exact same message, and my sql servers are remote

[ImportBot]

Originally Posted by CptChaos*:

 

Another question: a player's playtime, is the value in seconds?

[ImportBot]

Originally Posted by Legate*:

 

Error:

 

Message: [MySQL][ODBC 5.1 Driver][mysqld-5.5.13]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC) y

INNER JOIN tbl_playerdata pd ON y.' at line 6

 

 

But on the 0.0.04beta works without issue and is the same DB, connecto, etc.

[ImportBot]

Originally Posted by XpKiller*:

 

Another question: a player's playtime, is the value in seconds?

Yes its in seconds.

[ImportBot]

Originally Posted by XpKiller*:

 

Error:

 

Message: [MySQL][ODBC 5.1 Driver][mysqld-5.5.13]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC) y

INNER JOIN tbl_playerdata pd ON y.' at line 6

 

 

But on the 0.0.04beta works without issue and is the same DB, connecto, etc.

I need this settings:

Ranking by Score (yes/no)

overall Ranking (yes/no)

Determinate the broken query.

[ImportBot]

Originally Posted by CptChaos*:

 

XpKiller, thanks for your fast response! I am having another question, once again... With this query: Code:

SELECT `AEK-971_kills` , `AEK-971_hs` , `AEK-971_deaths` , `AN-94_Abakan_kills` , `AN-94_Abakan_hs` , `AN-94_Abakan_deaths` , `AS_Val_kills` , `AS_Val_hs` , `AS_Val_deaths` , `F2000_kills` , `F2000_hs` , `F2000_deaths` , `M16A4_kills` , `M16A4_hs` , `M16A4_deaths` , `AK74M_kills` , `AK74M_hs` , `AK74M_deaths` , `G3A3_kills` , `G3A3_hs` , `G3A3_deaths` , `KH2002_kills` , `KH2002_hs` , `KH2002_deaths` , `M416_kills` , `M416_hs` , `M416_deaths`
FROM tbl_weapons_assaultrifle
WHERE `StatsID` = STATSID-FROM-tbl_weapons_assaultrifle-WHICH-IS-EQUAL-TO-ID-IN-PLAYER-TABLE

I wouldn't get the correct assault rifle stats for a particular soldier in the database, would I?

 

I hope it's obvious I am a newb at MySQL...

[ImportBot]

Originally Posted by XpKiller*:

 

XpKiller, thanks for your fast response! I am having another question, once again... With this query: Code:

SELECT `AEK-971_kills` , `AEK-971_hs` , `AEK-971_deaths` , `AN-94_Abakan_kills` , `AN-94_Abakan_hs` , `AN-94_Abakan_deaths` , `AS_Val_kills` , `AS_Val_hs` , `AS_Val_deaths` , `F2000_kills` , `F2000_hs` , `F2000_deaths` , `M16A4_kills` , `M16A4_hs` , `M16A4_deaths` , `AK74M_kills` , `AK74M_hs` , `AK74M_deaths` , `G3A3_kills` , `G3A3_hs` , `G3A3_deaths` , `KH2002_kills` , `KH2002_hs` , `KH2002_deaths` , `M416_kills` , `M416_hs` , `M416_deaths`
FROM tbl_weapons_assaultrifle
WHERE `StatsID` = STATSID-FROM-tbl_weapons_assaultrifle-WHICH-IS-EQUAL-TO-ID-IN-PLAYER-TABLE

I wouldn't get the correct assault rifle stats for a particular soldier in the database, would I?

 

I hope it's obvious I am a newb at MySQL...

use this query instead:

Code:

SELECT `AEK-971_kills` , `AEK-971_hs` , `AEK-971_deaths` , `AN-94_Abakan_kills` , `AN-94_Abakan_hs` , `AN-94_Abakan_deaths` , `AS_Val_kills` , `AS_Val_hs` , `AS_Val_deaths` , `F2000_kills` , `F2000_hs` , `F2000_deaths` , `M16A4_kills` , `M16A4_hs` , `M16A4_deaths` , `AK74M_kills` , `AK74M_hs` , `AK74M_deaths` , `G3A3_kills` , `G3A3_hs` , `G3A3_deaths` , `KH2002_kills` , `KH2002_hs` , `KH2002_deaths` , `M416_kills` , `M416_hs` , `M416_deaths`
FROM tbl_server_player tsp
INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID
INNER JOIN tbl_weapons_assaultrifle twa ON tsp.StatsID = twa.StatsID
WHERE tpd.SoldierName = "CptChaos" AND tsp.ServerID = 1;

Then you can use the SoldierName direct in query. Replace the ServerID with the ID in the tbl_server.

Inner Joins are very fast in InnoDB so there is nor real reason to avoid it.

If have already the StatsID you could use the StatsID directly in your posted query.

[ImportBot]

Originally Posted by CptChaos*:

 

use this query instead:

Code:

SELECT `AEK-971_kills` , `AEK-971_hs` , `AEK-971_deaths` , `AN-94_Abakan_kills` , `AN-94_Abakan_hs` , `AN-94_Abakan_deaths` , `AS_Val_kills` , `AS_Val_hs` , `AS_Val_deaths` , `F2000_kills` , `F2000_hs` , `F2000_deaths` , `M16A4_kills` , `M16A4_hs` , `M16A4_deaths` , `AK74M_kills` , `AK74M_hs` , `AK74M_deaths` , `G3A3_kills` , `G3A3_hs` , `G3A3_deaths` , `KH2002_kills` , `KH2002_hs` , `KH2002_deaths` , `M416_kills` , `M416_hs` , `M416_deaths`
FROM tbl_server_player tsp
INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID
INNER JOIN tbl_weapons_assaultrifle twa ON tsp.StatsID = twa.StatsID
WHERE tpd.SoldierName = "CptChaos" AND tsp.ServerID = 1;

Then you can use the SoldierName direct in query. Replace the ServerID with the ID in the tbl_server.

Inner Joins are very fast in InnoDB so there is nor real reason to avoid it.

If have already the StatsID you could use the StatsID directly in your posted query.

Thanks for the query, if I remove the "and tsp.serverid" from the where clause, I get every result. Then I also need the server name, would that be needed by a subquery?

[ImportBot]

Originally Posted by XpKiller*:

 

Code:

SELECT SUM(`AEK-971_kills`) ,SUM( `AEK-971_hs`) , SUM(`AEK-971_deaths`) , SUM(`AN-94_Abakan_kills`) , SUM(`AN-94_Abakan_hs`) , SUM(`AN-94_Abakan_deaths`) ,SUM( `AS_Val_kills`) ,SUM( `AS_Val_hs`) ,SUM( `AS_Val_deaths`) ,SUM( `F2000_kills`) , SUM(`F2000_hs`) , SUM(`F2000_deaths`) , SUM(`M16A4_kills`) ,SUM( `M16A4_hs`) , SUM(`M16A4_deaths`) , SUM(`AK74M_kills`) , SUM(`AK74M_hs`) , SUM(`AK74M_deaths`) , SUM(`G3A3_kills`) , SUM(`G3A3_hs`) , SUM(`G3A3_deaths`) , SUM(`KH2002_kills`) ,SUM( `KH2002_hs`) , SUM(`KH2002_deaths`) , SUM(`M416_kills`) , SUM(`M416_hs`) , SUM(`M416_deaths`)
FROM tbl_server_player tsp
INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID
INNER JOIN tbl_weapons_assaultrifle twa ON tsp.StatsID = twa.StatsID
WHERE tpd.SoldierName = "CptChaos"
GROUP BY tsp.PlayerID;

Tada!

 

if need other columnnames just add it with "AS": SUM(`G3A3_hs`) AS G3A3_hs

 

for you servername question:

if you want to use you server name in the query you must inner join the tbl_server too this would be faster then a subquery in the most cases(not sure my rig is so fast i dont see any difference yet)

then just add you servername condition the the WHERE part

 

Code:

SELECT `AEK-971_kills` , `AEK-971_hs` , `AEK-971_deaths` , `AN-94_Abakan_kills` , `AN-94_Abakan_hs` , `AN-94_Abakan_deaths` , `AS_Val_kills` , `AS_Val_hs` , `AS_Val_deaths` , `F2000_kills` , `F2000_hs` , `F2000_deaths` , `M16A4_kills` , `M16A4_hs` , `M16A4_deaths` , `AK74M_kills` , `AK74M_hs` , `AK74M_deaths` , `G3A3_kills` , `G3A3_hs` , `G3A3_deaths` , `KH2002_kills` , `KH2002_hs` , `KH2002_deaths` , `M416_kills` , `M416_hs` , `M416_deaths`
FROM tbl_server_player tsp
INNER JOIN tbl_playerdata tpd ON tsp.PlayerID = tpd.PlayerID
INNER JOIN tbl_weapons_assaultrifle twa ON tsp.StatsID = twa.StatsID
INNER JOIN tbl_server ts ON tsp.ServerID = ts.ServerID
WHERE tpd.SoldierName = "CptChaos" AND ts.ServerName = "Your Server name"

[ImportBot]

Originally Posted by CptChaos*:

 

XP, thanks for the queries once again! For the second query of your post: I don't want to filter (where-clause) with servername, I want to see it back in the results. I can't seem to get that working as well...

 

So what I would like to have is the servername and then the stats fom the player.