1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MYSQL Query - Selecting the count

Discussion in 'MySQL' started by Weirfire, Mar 30, 2005.

  1. #1
    I have a database of several rows where CATEGORY is one of the fields. I would like to write a query where it counts the number of items for each category. Is there a way of doing this without writing the query out each time for the required category?

    $Result = mysql_query("SELECT COUNT(*) FROM MyTable WHERE Category = '$Category'") is what I have at the moment.

    Any help would be appreciated.

    Also, how would I go about extracting the COUNT(*) into PHP.

    I've tried

    $Row = mysql_fetch_object($Result);
    $Count = $Row -> COUNT;
    SEMrush
    but it doesnt seem to like it.
     
    Weirfire, Mar 30, 2005 IP
    SEMrush
  2. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #2
    OK I've sorted out the extraction by changing the query to SELECT COUNT(*) AS counter and using $ROW -> counter to select the info.

    So all I'm interested in now is finding out a more efficient way of executing a query for a count on all the categories.
     
    Weirfire, Mar 30, 2005 IP
  3. palespyder

    palespyder Psycho Ninja

    Messages:
    1,254
    Likes Received:
    98
    Best Answers:
    0
    Trophy Points:
    168
    #3
    I do it the same way as the PHP manual

    <?php
    
    $Result = mysql_query("SELECT * FROM MyTable WHERE Category = '$Category'")
    
    $num_rows = mysql_num_rows($result);
    
    ?>
    PHP:
    This may or may not be the easiest way.
     
    palespyder, Mar 30, 2005 IP
  4. mushroom

    mushroom Peon

    Messages:
    369
    Likes Received:
    15
    Best Answers:
    0
    Trophy Points:
    0
    #4
    "SELECT COUNT(DISTINCT Category) FROM MyTable "
     
    mushroom, Mar 30, 2005 IP
  5. nfzgrld

    nfzgrld Peon

    Messages:
    524
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #5
    I usually do something like this:

    
    $catcnt = mysql_fetch_row(mysql_query("SELECT COUNT(*) AS count FROM categories"));
    
    Code (markup):
    Once finished "$catcnt[0]" contains the result.
     
    nfzgrld, Mar 30, 2005 IP
  6. search_engine_optimizer

    search_engine_optimizer Peon

    Messages:
    31
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #6
    The other option can be like this:
    SELECT COUNT(Category) as counter FROM MyTable group by Category
     
    search_engine_optimizer, Sep 30, 2006 IP
  7. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #7
    select count(id) as category_count,category from table_name group by category

    You can do an inner join on a category name table if you have one of those, something like:

    select count(tablename.id) as category_count,category_name from table_name inner join categories on table_name.category_id=categories.category_id group by category_name
     
    mnemtsas, Oct 1, 2006 IP
  8. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #8
    I cant believe you replied to a 465 day old thread Mark. lol

    I bet you feel :eek: now

    ;)
     
    Weirfire, Oct 2, 2006 IP
  9. mnemtsas

    mnemtsas Super Dud

    Messages:
    497
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    0
    #9
    465 days eh? Teach me not to look at the date. :D
     
    mnemtsas, Oct 3, 2006 IP
    Weirfire likes this.
  10. Deck.Hazen

    Deck.Hazen Peon

    Messages:
    1
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    I'm hearing that doing a select query is a better way to count elements in a database than running the records through a loop and counting the hits for the particular value you are looking for - would that be a fair conclusion? and would you imagine that this is a fairly widely held belief?

    Thanks -- how old is that?

    - Deck
     
    Deck.Hazen, May 5, 2011 IP