SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2005
    Posts
    98
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how to pull the number of arrays within a select query

    Hi all

    I have the following php query on my mysql database

    Code MySQL:
    mysql_select_db($database_Aucconn, $Aucconn);
    $query_rsCategoryList = sprintf("SELECT * FROM aItems, aCategory WHERE aItems.aItemCalId = %s AND aItems.aItemCatId = aCategory.aCatId GROUP BY aCategory.aCatdesc ORDER BY aCategory.aCatDesc DESC", GetSQLValueString($gai_rsCategoryList, "int"));
    $rsCategoryList = mysql_query($query_rsCategoryList, $Aucconn) or die(mysql_error());
    $row_rsCategoryList = mysql_fetch_assoc($rsCategoryList);
    $totalRows_rsCategoryList = mysql_num_rows($rsCategoryList);

    $totalRows_rsCategoryList shows me the total number of categories which contain items.

    Rather than the total number of categories I need the total number of items within each category.

    I guessing that there is some sort of function I would need to add like
    $totalRows_rsCategoryList = mysql_num_arrays($rsCategoryList);

    But this is a bit beyond my understanding.

    I think the result of the query will hold all of this information, but I'm not sure how to extract it.

    Any help or guidance you can provide would be greatly appreciated

  2. #2
    Guru in training bronze trophy SoulScratch's Avatar
    Join Date
    Apr 2006
    Location
    Maryland
    Posts
    1,838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mysql_num_rows would return the number of results/rows taken ( items ) from the categories.

    Code:
    task_id    task             location
    1          walk the dog     outside
    2          eat breakfast    home
    $query = mysql_query('SELECT task_id, task from table');
    echo mysql_num_rows($query); // would return 2, the number of rows ( 1 and 2 )
    echo '<pre>', print_r( mysql_fetch_assoc($query) ), '</pre>'; // this is an associative array which has column names and values.
    echo count(mysql_fetch_assoc($query)); // would return 3, as there are 3 columns.
    Cross browser css bugs

    Dan Schulz you will be missed

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by mickthetree View Post
    Rather than the total number of categories I need the total number of items within each category.
    several significant changes are required in your query:
    Code:
    SELECT aCategory.aCatdesc
         , COUNT(aItems.aItemCatId) AS number_of_items
      FROM aCategory 
    LEFT OUTER
      JOIN aItems
        ON aItems.aItemCatId = aCategory.aCatId 
     WHERE aCategory.aCatId = %s 
    GROUP 
        BY aCategory.aCatdesc DESC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •