Connection via VPS is somewhat possible with SQL. Taifun has a tutorial for it but your server must be strong when using this method because you need to request every few seconds for “live” updates. Firebase will definitely be better for this stuff but you will need to pay.
I have tried with firebase , yes it fast but the datas get full within couple of days , eventually cost more also faced lag in my app -_- .
Whereas currently I am using my VPS+SQL , Questions loads very fast and no lag but I am little worried about add live features , for example how many users are spectating the game , how many users are playing live .
If anyone has a github reference/ sample apk please let me know.
Make a column for user game status (which game Id they are playing/is user playing game?)
In app code:
Use SQL WHERE command to request and filter out a specific game id.
Convert CSV, count list and that would be the number of people in that particular status
Do that for both of them maybe 2 columns for spectator status and active game id… Remember that the app must reset the status after the game ends or activity state changed if not the result number will not be accurate.