Counting how many X is in a field for each id in a table

[DrkFusion]

Hi guys,

I would like to know how I would go about running a sql php code that will tell me how many times the forumidX shows up in a field in the WHOLE table.

For instance forumid 8 from the field5 in the profilefields table.

Thanks in advance, appreciate all and any help.
-Arunan

[Xenon]

hmm, not sure if i understand you right, but i think you want to have this:

$var=$DB_site->query_first("SELECT COUNT(*) as counter FROM userfields WHERE field5='8'");

$var[counter] would be your value how much users have 8 in field5

[DrkFusion]

Ah Thanks Xenon, I will try it out.

Thanks again, you always are a great help
-Arunan

[DrkFusion]

Well, I am doing a top 5 kinda thing, how would I do something like that?

I want to find out how many people have 1 forumid in field5 and make that the top forumid kinda thing. :-/

Thanks

[Xenon]

ohhh, thats a bit ahead of my sql-knowledge.
don't know if this is even possible

i just know a (real) bad but working way, but it is very very hardcoded:

$var=$DB_site->query_first("SELECT SUM(IF(field5='1',1,0) as forum1, SUM(IF(field5='2',1,0) as forum2 FROM userfields");

so you can use $var[forum1], $var[forum2] and so on (if you make a longer query ;)) and can compare all these....

i know, it's veryy bad, but i don't know another way ....

[DrkFusion]

Thanks Stefan,

Well I guess I will take an easier way out, I will make the value of the field increase by 1 every time the forumid is added to there profile field, now how do I stop users from doing it more than once?

Sorry, if I am asking too many questions :( I am yet a weee litttle Php Padwan, still learning the ways :)
-Arunan

[Xenon]

well you have to check if they already have had a id inserted, and reduce the value at this id by one and then increase the value for the new id...

with $bbuserinfo[fieldx] you can acces the old value and compare it with the new one befor the new value is entered to the db

[DrkFusion]

I put the fieldX+1, but it seems to be adding 13 :-/

-Arunan