MySQL Select count all colums !=0

[MariusExygo 1480]

I'm stuck on a query, i tried all the day to make it work

 

I want to count all pid1, pid2, pid3, pid4 from player.player_index with values > 0 or != to get number of total player for example: Total players chunjo

 

I'm trying to make this for website

 

Could anyone who knows to help me ?

Edited June 19, 2015 by Exygo

[JachuPL 411]

I think this solution is better.

SELECT COUNT(*) FROM player.player p, player.player_index idx WHERE p.account_id = idx.id AND idx.empire = EMPIRE_HERE;

This query joins tables player and player_index. It gives you the real amount of players for selected empire.

[MariusExygo 1480]

Could you give me more details?

player_index idx ?????

player.player p ??????

[JachuPL 411]

Could you give me more details?

player_index idx ?????

player.player p ??????

​you should read more about joining two tables here

https://dev.mysql.com/doc/refman/5.0/en/join.html

 

[pbuzz 31]

Could you give me more details?

player_index idx ?????

player.player p ??????

​'p' is assigned to 'player.player' and 'idx' to 'player_index', what is so hard?

to make it work for each empire replace "EMPIRE_HERE" with 1/2/3

1 - red

2 - yellow

3 - blue

$qRedChars = mysql_query("SELECT COUNT(*) FROM player.player p, player.player_index idx WHERE p.account_id = idx.id AND idx.empire = 1");
$qYellowChars = mysql_query("SELECT COUNT(*) FROM player.player p, player.player_index idx WHERE p.account_id = idx.id AND idx.empire = 2");
$qBlueChars = mysql_query("SELECT COUNT(*) FROM player.player p, player.player_index idx WHERE p.account_id = idx.id AND idx.empire = 3");

[MariusExygo 1480]

Could you give me more details?

player_index idx ?????

player.player p ??????

​'p' is assigned to 'player.player' and 'idx' to 'player_index', what is so hard?

to make it work for each empire replace "EMPIRE_HERE" with 1/2/3

1 - red

2 - yellow

3 - blue

$qRedChars = mysql_query("SELECT COUNT(*) FROM player.player p, player.player_index idx WHERE p.account_id = idx.id AND idx.empire = 1");
$qYellowChars = mysql_query("SELECT COUNT(*) FROM player.player p, player.player_index idx WHERE p.account_id = idx.id AND idx.empire = 2");
$qBlueChars = mysql_query("SELECT COUNT(*) FROM player.player p, player.player_index idx WHERE p.account_id = idx.id AND idx.empire = 3");

​

Could you give me more details?

player_index idx ?????

player.player p ??????

​you should read more about joining two tables here

https://dev.mysql.com/doc/refman/5.0/en/join.html

 

​

I apreciate your help guys, finished

P.S: I HAD SOME problems becouse my website was using mysqli bla bla bla

#Problem solved