Jump to content

Archived

This topic is now archived and is closed to further replies.

Stretsh

Select, count and sort

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[!--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?

Share this post


Link to post
Share on other sites
Alternatively, you could simply use a subquery.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.