Is this correct?

PHP
1

I’m trying to make a leaderboard but only to count the records from a certain year. I have done it like this but not sure if this is correct.


$currYear = 2011;

$sql = "SELECT cu.clubid,AVG(point_total) AS avg_stfpoints, COUNT(point_total) AS counted, c.clubname, c.ccountry, c.seoname, si.regdate FROM ".$prefix."_score_info si 
               INNER JOIN ".$prefix."_club_users cu ON cu.new_userid = si.user 
               INNER JOIN ".$prefix."_club c ON c.clubid = cu.clubid
          GROUP BY cu.clubid 
               HAVING COUNT(point_total) >= 10 AND YEAR(si.regdate) = $currYear ORDER BY avg_stfpoints DESC;";

It seems like it doesn’t do the work… What do I do wrong… Also, I only want it to count the point_total for that specific year…

2

“Doesnt do the work”.

Doesnt do… what work? Returns information you didnt expect? Throws an error? Magic unicorns and pixie dust?

3

It returns some records, but doesnt seem to return all the records from 2011?! I have a group with atleast 10 records from 2011 and they are not on the list… I think there is som error in that part but not sure…

4

If your question is about the speed or efficiency of you sql statement, then look in your mysql logs and pick up the exact query that was sent to the database.

Copy it.

Put the word DESCRIBE in front of it, and paste the whole thing directly into whatever you use to manage your database directly.

Grab the output of that.

Paste that here along with the full output of DESCRIBE <table-name> for each of your tables.

IF your question is solely about sql, then you are best asking to have this thread moved to the sql or mysql forum on this site.

or, try reading some of these links [google]understanding mysql describe[/google].

Edit:

Oh, I see you posted another reply illuminating your problem, well my advice still stands.

5

Probably need to see the entries you think are getting skipped, as well.

6

I think I’m gettin closer… Allthough I get an error whern I do this:

SELECT cu.clubid,AVG(point_total) AS avg_stfpoints, COUNT(point_total) AS counted, c.clubname, c.ccountry, c.seoname, si.regdate FROM ".$prefix."_score_info si 
    INNER JOIN ".$prefix."_club_users cu ON cu.new_userid = si.user 
    INNER JOIN ".$prefix."_club c ON c.clubid = cu.clubid WHERE YEAR(si.regdate) = $currYear 
GROUP BY cu.clubid 
	HAVING COUNT(point_total) >= 2 ORDER BY avg_stfpoints DESC;

The error is this:
Error: (1066) Not unique table/alias: ‘c’

What am I doing wrong?