Page 1 of 1
[SOLVED] mysql help :(
Posted: Sun Jul 29, 2012 6:26 pm
by 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
Re: mysql help :(
Posted: Sun Jul 29, 2012 8:22 pm
by 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?
Re: mysql help :(
Posted: Sun Jul 29, 2012 8:41 pm
by 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.
Re: mysql help :(
Posted: Mon Jul 30, 2012 12:07 am
by Callan S.
Torniquet wrote: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!
Re: mysql help :(
Posted: Mon Jul 30, 2012 1:14 pm
by 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
Re: mysql help :(
Posted: Mon Jul 30, 2012 6:45 pm
by 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.
Re: mysql help :(
Posted: Mon Jul 30, 2012 9:47 pm
by 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?
Re: mysql help :(
Posted: Tue Jul 31, 2012 7:10 pm
by 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.
Re: mysql help :(
Posted: Tue Jul 31, 2012 7:17 pm
by 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.
Re: mysql help :(
Posted: Tue Jul 31, 2012 7:48 pm
by 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