Jump to content


Photo

Select, count and sort


  • Please log in to reply
3 replies to this topic

#1 Stretsh

Stretsh
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 23 April 2006 - 02:12 AM

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.

Example:
root1
  sub1 (5)
  sub2 (10)
  sub3 (0)
root2
  sub1 (0)
  sub2 (0)
  sub3 (5)

my script:

$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'"

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

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

This returns an "expecting ')'" error.

Help please, anyone?

#2 desithugg

desithugg
  • Members
  • PipPipPip
  • Advanced Member
  • 281 posts
  • LocationScarborough

Posted 23 April 2006 - 03:02 AM

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

i might be wrong i didnot fully understand that query

#3 Stretsh

Stretsh
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 23 April 2006 - 03:14 AM

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

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:
$qid2 = "select count(*) as cc from subcats where catid = '$subcat' order by cc";

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?

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 23 April 2006 - 05:48 PM

Alternatively, you could simply use a subquery.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users