Select, count and sort

[Stretsh]

Hi there

I have a php function called countincat that counts the number of entries in a specific category.

I have main cats and sub cats. The main cats are listed, each with it's sub sub cat followed by the number of entries in that subcat between brackets.

[code]Example:
root1
  sub1 (5)
  sub2 (10)
  sub3 (0)
root2
  sub1 (0)
  sub2 (0)
  sub3 (5)[/code]

my script:

[code]$qid = "select id, name from cats where parent=0";
while ($roots = mysql_fetch_obect($qid)) {
  $main = $roots->id;
  $qid2 = "select id, name from subcats where parent = '$main'"[/code]

Now I want to sort the sub cats by the highest number of entries in the subcat.

I tried:
[code]
  $qid2 = "select id, name, ".countincat(."id".)." as cc from subcats where parent = '$main order by cc'";[/code]

This returns an "expecting ')'" error.

Help please, anyone?

[desithugg]

fot the last 1 i sujjest making 2 queries
[code]$qid2 = mysql_query"select id, name, ".countincat(."id".)." as cc from subcats where parent = '$main order by cc'";[/code]
[code]$qid2 = "select id,name from subcats where parent = '$main'";[/code]
[code]$qid2 = "select count(id) as cc from subcats where parent = '$main order by cc'";[/code]

i might be wrong i didnot fully understand that query

[Stretsh]

[!--quoteo(post=367587:date=Apr 23 2006, 12:02 AM:name=desithugg)--][div class=\'quotetop\']QUOTE(desithugg @ Apr 23 2006, 12:02 AM) [snapback]367587[/snapback][/div][div class=\'quotemain\'][!--quotec--]
fot the last 1 i sujjest making 2 queries
[code]$qid2 = mysql_query"select id, name, ".countincat(."id".)." as cc from subcats where parent = '$main order by cc'";[/code]
[code]$qid2 = "select id,name from subcats where parent = '$main'";[/code]
[code]$qid2 = "select count(id) as cc from subcats where parent = '$main order by cc'";[/code]

i might be wrong i didnot fully understand that query
[/quote]

I made a mistake: all categories are in one table, with catid and parent

Thinking out loud:
First get the subcats, then in the while statement I query the number of entries for each subcat
but the I would use your statement differently:
[code]$qid2 = "select count(*) as cc from subcats where catid = '$subcat' order by cc";[/code]

But still, how would I get the subcats listed by the number of entries IN each subcat? Can "foreach" or another statment be used to sort the results by their entries?

[fenway]

Alternatively, you could simply use a subquery.