[SOLVED] Troublesome query.

[DarkWater]

Hey everyone. 

 

Okay.  I have quite a dilemma with a query that I'm trying to get to work.  What I'm trying to do could be done in several queries and some array work, but I want to try to get it done in one shot.  I'll explain as best as I can and hopefully someone can help me fix this query. =/

 

So I have a table, myachs, with a gameid, a userid, an achievement id (achid), and a unique myachid just in case it's ever needed.

I have another table, achievements, with achid, gameid, achname, achdesc, achimg, and achpoints.  I have a third table, site_users, with user info.  Now, what I'm trying to do is get the amount of points that each user has in a certain game for a leaderboard.  I was trying to use SUM() for it, but it keeps adding on the same amount for each achievement, giving me a number like 3750 when it actually should be 90.  I could just be going crazy, but there has to be some way to do this.  Things I've tried have been:

 

SELECT u.username, SUM(a.achpoints) AS totalpoints FROM achievements as a LEFT JOIN myachs AS ma ON ma.gameid = a.gameid LEFT JOIN site_users AS u ON u.user_id = ma.userid WHERE AND ma.gameid = {$_GET['id']} GROUP BY u.user_id ORDER BY totalpoints

 

SELECT u.username, SUM(a.achpoints) AS totalpoints FROM site_users AS u, achievements AS a, myachs AS ma WHERE ma.userid = u.user_id AND ma.gameid = a.gameid AND ma.gameid = {$_GET['id']} GROUP BY u.user_id ORDER BY totalpoints

 

I've tried others but I don't think they were even close.

 

I could very well be going about this horribly wrong.  Would anyone care to shed some light?  I'm really trying to do this one query. @_@

 

 

Thanks,

DarkWater

[discomatt]

If you wanna post up a DB dump ( structure and some sample data ) I could play around with it. I tend to solve things better when i can see them.

[DarkWater]

Sure, I was just going to do that, I just needed to get on my phpMyAdmin.  Here you go.  I'll include two rows of data per table.  I've been playing around with this for like, 45 minutes. @_@

 

achievements

CREATE TABLE `achievements` (
  `achid` int(11) NOT NULL auto_increment,
  `gameid` varchar(255) collate latin1_general_ci NOT NULL,
  `achname` varchar(255) collate latin1_general_ci NOT NULL,
  `achdesc` varchar(255) collate latin1_general_ci NOT NULL,
  `achimg` varchar(255) collate latin1_general_ci NOT NULL,
  `achpoints` int(11) NOT NULL,
  PRIMARY KEY  (`achid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=219 ;

--
-- Dumping data for table `achievements`
--

INSERT INTO `achievements` (`achid`, `gameid`, `achname`, `achdesc`, `achimg`, `achpoints`) VALUES
(1, '1', 'Balloon Bonanza', 'Get all of the help balloons in every stage.', '/0YCLiGJhbC9DFQUXXFJRFzdDL2FjaC8wLzcAAAAA5+fn-squCw==.jpg', 25),
(2, '1', 'Hall of Fame', 'Rack up a total of 100 wins in ranked matches.', '/04CLiGJhbC83FQUXXFJRFzdDL2FjaC8wL0MAAAAA5+fn-HJecQ==.jpg', 30)

 

myachs

CREATE TABLE `myachs` (
  `myachid` int(11) NOT NULL auto_increment,
  `userid` int(11) NOT NULL,
  `gameid` int(11) NOT NULL,
  `achid` int(11) NOT NULL,
  `dateadded` datetime NOT NULL,
  PRIMARY KEY  (`myachid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ;

--
-- Dumping data for table `myachs`
--

INSERT INTO `myachs` (`myachid`, `userid`, `gameid`, `achid`, `dateadded`) VALUES
(1, 1, 4, 25, '2008-07-09 16:06:49'),
(2, 1, 4, 26, '2008-07-09 16:06:49')

 

site_users

CREATE TABLE `site_users` (
  `user_id` int(11) NOT NULL auto_increment,
  `username` varchar(255) character set latin1 collate latin1_general_ci NOT NULL,
  `password` varchar(255) character set latin1 collate latin1_general_ci NOT NULL,
  `date_reg` datetime NOT NULL,
  `email` varchar(255) character set latin1 collate latin1_general_ci NOT NULL,
  PRIMARY KEY  (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `email` (`email`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `site_users`
--

INSERT INTO `site_users` (`user_id`, `username`, `password`, `date_reg`, `email`) VALUES
(1, 'Himself0890', '---', '2008-07-08 15:20:03', '---'),
(2, 'Punisher0587', '---0', '2008-07-09 17:39:10', '---')

 

@_@  Thanks for anything you can help with.  I really hate MySQL sometimes.

[DarkWater]

You might need to change the achid to 4 in the achievements table to get some results.  I wasn't think about which rows I gave you, I just gave the first two. =P  There's actually like 50-ish for each gameid. @_@

 

[discomatt]

Lol, you shoulda given me lots of data The more the merrier... but yes

 

achid's in myach don't make sense

[DarkWater]

You can change those too.  They make sense with the full data, but it'll be a LOT of data to post.  Not sure if you need the whole thing.  You can change any IDs to match the stuff I gave, it should still work out fine.

[discomatt]

Well, it's hard ot know if it REALLY works with 1 result set.

 

SELECT
u.username,
SUM(a.achpoints) as total
FROM
myachs AS m,
achievements AS a,
site_users AS u
WHERE
m.achid = a.achid
AND
u.user_id = m.userid
GROUP BY
m.userid
ORDER BY
total DESC

 

Returns

 

username		total
Himself0890	55

 

For me

 

You can always just dump to an sql file and attach it to your post

[DarkWater]

Wow, thanks a bunch Discomatt.  I really don't know why I couldn't get this.  I really appreciate it. =)  It works like a charm.  I just tried it with using a specific gameid and it also worked. 

 

Much appreciated.

 

Thanks,

DarkWater

[discomatt]

No problem, I think you were just over-complicating it

[DarkWater]

I totally was.  What's weird is that I tried a query just like that before.  Maybe I just had my GROUP BY wrong.  But thanks. =)