[SOLVED] mysql help :(

[Torniquet]

Hey one and all.

I am currently working on a project and I need help with a query.

I need to count the number of players associated to a game, but minus 1 if your id appears in the list.

Here is my current query

Code: Select all

$query = "SELECT `games`.*, COUNT(`user_games`.`user_id`) AS `players` FROM `games`, `user_games` WHERE `duplicate`=0 AND `user_games`.`game_id`=`games`.`id` GROUP BY `app_name` ORDER BY `app_name`";
 

I am sure that the count requires an if, so I am looking for something like

if the users id appears as one of the user ids in user_games, then minus 1 from the count.

I dont really want to split the query up unless there really is no other way, but I am sure there is.

ta xx

[Jackolantern]

Do you have the player's ID in session or stored elsewhere? If so, couldn't you just pull up all of the IDs WHERE ID != $playerID?

[Torniquet]

That would cancel out any games that is played by that player. The idea is to get a count of how many people are play the games not counting the user.

[Callan S.]

That would cancel out any games that is played by that player. The idea is to get a count of how many people are play the games not counting the user.

!

I'm pretty sure both those sentences refer to the exact same thing!

[Torniquet]

using Jacks solution would remove any games that the player plays from the list. I need a count of the number of people play the games NOT including the user in the count if they play the same game.

i.e

Game A - 5 players
Game B - 9 players
Game C - 2 players

the user plays game B but not A and C, so the results need to be returned as this

Game A - 5 players
Game B - 8 players
Game C - 2 players

even though game B has 9 players in total

[Nihilant]

Torniquet, I rally don't understand why you think Jackolantern's solution is not going to work for you.

You want to display the list upon player's request, without him (the player requesting the list) being counted towards the players? That's exactly what Jack's solution does and it can't get any simpler than that. You probably misunderstood his solution for this:
- that the query of such type will select only the games that player is not playing.
But it's not what Jack's solution does: it selects all games that meet your current criteria, WHILE not counting this player towards the total number of players. So it will display exactly what you said you need.

[Jackolantern]

I actually think I understand why it won't work. So multiple players are in each game, and you want to get a count? However, you want to skip over the player, but not skip over who they are playing with?

[Torniquet]

@ Nihilant,

Jacks solution does semi work in that it returns a count minus the player, but also removes any games they are the only player of. I still want these games listed even if no one else plays them.

Orig query renders 415 results, Jacks solution only renders 408 results.

fair enough my original statement against the solution didn't put that impression across... but still lol.

[Jackolantern]

How often is this query run? Because something like this would be much more simple with a subquery or as multiple queries. If it is on every page, could you cache the results and only run them every couple of minutes? I am sure there is a way to do it all in one with only one pass, but it is definitely over my head lol.

[Torniquet]

Urgh it was staring me in the face the whole time -.-

It's amazing I work with this crap all day and come home and can't even see what I need to see right in front of me

The query is only run on 1 page (at the moment).

And if anyone else is interested...

Code: Select all

SELECT `games`.*, IF(`user_games`.`user_id`='{$user>id}', COUNT(`user_games`.`user_id`) - 1, COUNT(`user_games`.`user_id`)) AS `players`
FROM `games`, `user_games`
WHERE `duplicate`=0 AND `user_games`.`game_id`=`games`.`id` GROUP BY `app_name` ORDER BY `app_name`
 

THATS what I was after lol