Thread: Counting rows

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Preston,Lancashire,United Kingdom
    Posts
    1
    Rep Power
    0
    I have a quiz database that contains three fields one called Question, one called Category1 and one called Category2.

    As we enter questions they are entered into categories such as Pop Music (Category1) and
    70's Pop Music (Category2).

    What I need to do is count the number of rows, so what I need is something that will go through the database and count how many questions have Pop Music as the first category and Pop Music 60's as the second. Then it goes through again and finds how many questions have Pop Music as the first category and 70's Pop Music as the second category and so on.

    Hopefully the results would look something like,

    Pop Music - Pop Music 60's - 324
    Pop Music - Pop Music 70's - 118
    Trivia - General - 114
    Sports - Football - 282

    etc.

    Can anybody help me with this one ??

    Graham
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2000
    Location
    Ontario, Canada
    Posts
    498
    Rep Power
    18
    If you're using MySQL, use this example:

    $row = mysql_query("select row from table");
    $mcount = mysql_num_rows($row);

    That will return the total # of rows for that specific field. I do believe, that this is what you're looking for.

    ------------------
    To alcohol! The cause of, and solution to, all of life's problems. -- Homer Simpson
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2000
    Location
    Preston,Lancashire,United Kingdom
    Posts
    1
    Rep Power
    0
    Hi,

    Thanks for the reply, but no, that doesn't really help.

    I can get the total number of rows okay, what I want is the total number of rows where Category1 = Pop Music and Category2 = 60's Pop Music, then the total number of rows where Category1 = Pop Music and Category2 = 70's Music and so on.

    I don't want to actually define what Category1 and Category2 should equal, I'd like the code to do that, basically return me the total number of rows for each unique combination of Category1 and Category2
  6. #4
  7. Wiking
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Sep 2000
    Location
    Sweden
    Posts
    3,608
    Rep Power
    26
    Try this:

    select category1, category2, count(*) as total from table
    group by category1, category2;

    And perhaps some where-condition...

    I think that should do the trick for you.

    / NoXcuz
  8. #5
  9. No Profile Picture
    Robert_J_Sherman
    Guest
    Devshed Newbie (0 - 499 posts)
    OKay, I recenlty wrote a small links app that displays the links categories from an
    "Category Array"...

    The values of the array are compared to the database table which holds the links, in addition, there is a display flag with each row of the table.. this way the present number of links under a specified category are only counted "if" the display flag is set to yes.

    for ($x = 1; $x < count($val); $x++) {
    $sql = "SELECT * FROM links WHERE category='$val[$x]' AND display='Y'";
    $result = mysql_query($sql);
    $count = mysql_num_rows($result);

    echo "<td nowrap><span class="$styles[0]">| [ </span>n<a href="$PHP_SELF?category=".urlencode($val[$x])."#$val[$x]" class="$styles[2]">$val[$x]</a>n <span class="$styles[0]">($count) ] |</span></td>nn";

    if ($x == $menu_width) {
    echo "</tr>n<tr>n";
    $menu_width = $x + $menu_width;
    }
    }

    Note that $val is the "values" of the categories array.

    In this script the categoies array looks something like so:

    $category = Array('option 1','option 2');

    A rather basic array.

    This could also be accomplished if your category choices where also stored in a database table of their own, a simlple SQL select statement could build your array, which could be useful if your categories were
    being constructed based on user input.. or something along those lines.

    ------------------
    SnR Graphics,
    Low Cost Hosting and Web Development.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2000
    Posts
    51
    Rep Power
    15
    grahamfx'

    You wrote the query in your last post,
    just tell mysql that and not us..

    IE:
    -------
    $querry = mysql_query ("select ID from TABLE where Category1 = 'Pop Music' and Category2 = '60's Pop Music' " ),db;

    $count_of_60_popmusic = mysql_num_rows ($query);
    --------

    That will give you the count of 60's pop music.

    Charlie...

Similar Threads

  1. counting amount of rows within a query
    By JasonL in forum MySQL Help
    Replies: 4
    Last Post: December 29th, 2003, 12:42 PM
  2. Counting rows in html table
    By anshul9189 in forum HTML Programming
    Replies: 2
    Last Post: October 30th, 2003, 05:29 AM
  3. counting all rows across all tables
    By suffeks in forum MySQL Help
    Replies: 2
    Last Post: March 28th, 2003, 10:37 AM
  4. Counting amount of rows for a field within mysql query
    By strange1 in forum PHP Development
    Replies: 5
    Last Post: December 27th, 2002, 08:24 AM
  5. Problem counting rows
    By bayridge in forum MySQL Help
    Replies: 2
    Last Post: July 23rd, 2002, 08:45 PM

IMN logo majestic logo threadwatch logo seochat tools logo