Combo Box in User CP (part of bigger hack)

Please help me I have tried to convert the sytle drop down hack to suit my needs for a multirank hack I am creating... This is the last stage in the hack, but I am getting an MySQL error from the modified usercp.php file.

The error is this

PHP Code:

Invalid SQL: SELECT rankcategories.cat_name, userfield.rank, COUNT(*) AS count FROM userfield LEFT JOIN rankcategories USING (rank) GROUP BY userfield.rank
mysql error: Unknown column 'rankcategories.rank' in 'on clause' 

That is from this source code;

PHP Code:

$allranks=$DB_site->query("SELECT rankcategories.cat_name, userfield.rank, COUNT(*) AS count FROM userfield LEFT JOIN rankcategories USING (rank) GROUP BY userfield.rank");
$dropdownbits='';
while ($thisrank=$DB_site->fetch_array($allranks)) {
  if ($rankid==$thisrank[cat_id]) {
    $ranksel='selected';
  } else {
    $ranksel='';
  }
  eval("\$dropdownbits .= \"".gettemplate('multirank_dropdownbit')."\";");
} 

Please could you tell me what I have done wrong. Because this peice of code will finish off my multirank hack (which I will release here, because it has proper admin section, and so if far better than the current multirank hack which is avaible.)

Thank you

Phenix

[Xenon]

the table rank does not have a field "rank"

you should add this field befor run those queries.

[USS Phenix]

well I added a field to userfield called rank. Could u explian why that the code is looking for rank>ranks (when I thought it was looking for userfield>rank & rankcatgories>cat_name)

[Xenon]

JOIN rankcategories USING (rank)

that part produces your error.

it means that userfield AND rankcategories must have a field rank, so they can be combined on this table-field

[USS Phenix]

So you suggest the php to be;

PHP Code:

$allranks=$DB_site->query("SELECT rankcategories.cat_name, userfield.rank, COUNT(*) AS count FROM userfield");
$dropdownbits='';
while ($thisrank=$DB_site->fetch_array($allranks)) {
  if ($rankid==$thisrank[cat_id]) {
    $ranksel='selected';
  } else {
    $ranksel='';
  }
  eval("\$dropdownbits .= \"".gettemplate('multirank_dropdownbit')."\";");
} 

Would that would? if not could you please show me what would.

[Xenon]

no that wouldn't work, too.

i can't tell you exactly, because i'm not a mysql guru, i just saw the mistake you made, but not how it would be correct

id suggest you should rename the field rank from userfield into cat_name

and then use this query:

SELECT rankcategories.cat_name, COUNT(*) AS count FROM userfield LEFT JOIN rankcategories USING (cat_name) GROUP BY userfield.cat_name

but as said, i don't know mysql very well, i just saw what has produced the error

[USS Phenix]

ok thx... I'll try that