Global Login System (Implementation Discussion)

[Apocalypse]

Make sure you don't select create new profile to select add existing profile.

[jitspoe]

I prettied up the clan display a bit.  It shows the number of players in the clan as well (and sorts by that so all the 1-person clans are at the end of the list).

Can't quite figure out how to make it display empty clans, though...

SELECT `clanid`, `name`, `tag`, COUNT(1) AS `nummembers` FROM `dplogin_clanmembers` LEFT JOIN `dplogin_clans` ON `dplogin_clans`.`id` = `dplogin_clanmembers`.`clanid` WHERE (`dplogin_clanmembers`.`flags` & 1) = 1 GROUP BY `clanid` ORDER BY `nummembers` DESC

A flag of 1 is an active member.

[lekky]

put dplogin_clans on the left side of the join, that should get all instances of clans and members associated with those clans right? At the moment you are getting all members and only clans they belong to.

SELECT clanid, name, tag, COUNT(1) AS nummembers
FROM dplogin_clans
LEFT JOIN dplogin_clanmembers ON dplogin_clanmembers.clanid=dplogin_clans.id
WHERE etc

[jitspoe]

I tried that, as well as a right, outer, inner, etc. joins.  No go.  The problem is with the WHERE statement, I think.  Clans without inactive members don't get put in the list, but I need the where statement to generate accurate counts (otherwise it counts former, invited, requesting, etc. members, not just active ones).

[Apocalypse]

Very nice job I always got pissed off when I was looking through clans and I bump into clan with no people it was so annoying .

[lekky]

I tried that, as well as a right, outer, inner, etc. joins.  No go.  The problem is with the WHERE statement, I think.  Clans without inactive members don't get put in the list, but I need the where statement to generate accurate counts (otherwise it counts former, invited, requesting, etc. members, not just active ones).

cant you do:

SELECT clanid, name, tag, COUNT(1) AS nummembers
FROM dplogin_clans
LEFT JOIN dplogin_clanmembers ON dplogin_clanmembers.clanid=dplogin_clans.id AND dplogin_clanmembers.flags = 1

By my reckoning that should get every clan, and then all members with the same clan id and that are flagged as 1

[jitspoe]

MySQL said: 
#1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

[lekky]

stick your GROUP BY on the end then?!

[jitspoe]

Ok, I'm not quite sure what that did, but...

Code: [Select]

clanid name tag nummembers
NULL [i]nsane [i] 365
2 Metal Militia -)MeMi(- 8
3 Team ch1ll -ch1ll- 4
7 The Testers [test] 2
16 Navy Seals {NS} 3
17 Crossbones .xb. 1
19 God'z Militia .gM^ 1
24 Only The Best [OTB] 6
29 &T (&T) 2
31 InSanE JunCtiOn .Ij. 8

[lekky]

Add your order by clause in too.

And that works, as long as you don't have any clans with a null id, which you really shouldn't do!

If so I think you can add  AND clanid != NULL to the end of the left join. Not the most efficient code but should work.

[jitspoe]

There aren't any clans with a null id, it's a "NOT NULL" field.  Also, insane doesn't have 365 members.  It's like it's dumping everything that doesn't match into insane or something.

[lekky]

So are you sure Insane has an id? Just from looking at that it looks like its joining on all the members on who's clanid field match a clan id in the clans table. Why is it showing NULL for insanes clan id? Its matching everone who isnt in a clan to insane as they both seem to have a NULL clan id.

Otherwise i dunno :/

[b00nlander]

do you have that fixed now?
also, could you explain why sometimes the players of a clan have the clantag in front of their names and sometimes not? seems to do that randomly, as my nickname has been -)MeMi(-Pureblood once and now is b00nlander on the dplogin-website...

[jitspoe]

Code: [Select]

id timestamp name tag
  1 2006-10-09 21:39:54 [i]nsane [i]
  2 2006-10-10 02:38:04 Metal Militia -)MeMi(-
  3 2006-10-10 03:24:51 Team ch1ll -ch1ll-
...
There are, however, no active members for insane, so I guess it just lumped everything there.

b00n: The name that is displayed is whatever you logged in as last.

Edit: Update: I have it pretty close with this:

SELECT *, COUNT(1) as `count` from `dplogin_clanmembers` right join `dplogin_clans` on `dplogin_clanmembers`.`clanid` = `dplogin_clans`.`id`  and (`flags` & 1) = 1 group by `dplogin_clans`.`id`

Before I was trying to group on the clanid field (from the clanmembers table) instead of the id field from the clan table.  The former was null in the case of an empty clan because of the way right join works.  It shows all the clans now, but it also counts them, so empty clans have a player count of 1.  Not sure if there's anything I can do about that.

[Apocalypse]

The name that is displayed is whatever you logged in as last.

I always wondered that thought it was because they just registered as that for some weird reason

[lekky]

SELECT cl.id, cl.name, cl.tag, COUNT(1) AS nummembers
FROM dplogin_clans cl
LEFT JOIN dplogin_clanmembers cm ON cm.clanid=cl.id AND cm.flags = 1
GROUP BY cl.id
ORDER BY nummembers

I stand by my SQL!

[jitspoe]

SELECT cl.id, cl.name, cl.tag, COUNT(1) AS nummembers
FROM dplogin_clans cl
LEFT JOIN dplogin_clanmembers cm ON cm.clanid=cl.id AND cm.flags = 1
GROUP BY cl.id
ORDER BY nummembers

I stand by my SQL!

That's almost exactly what I have, except you swapped the tables around and did a left join.  Also, you can't use equality testing for flags, since a flag of 3 (leader and member) wouldn't be listed.  But thanks for the help.

I've added pages and searching to the clan listing.  It looks a lot nicer now.

[y00tz]

I've added pages and searching to the clan listing.  It looks a lot nicer now.

Wow, it does look a lot cleaner now, nice one.  Are you going to add a link to the GLS to the header with the rest of them when it's finished?

[jitspoe]

Are you going to add a link to the GLS to the header with the rest of them when it's finished?

You referring to the "Login" link next to "News"?

[lekky]

it looks smart. (Being able to sort by any of the columns would be nice too )