SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast thechronic's Avatar
    Join Date
    Jun 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL possible to reduce # of queries?

    Hello,

    I'm developing my first site with a MySQL-database. It is a shopping site in which all items are sorted into categories. The main categories (cat0) do not contain any items, but the lower categories (cat1 and cat2) do.

    In the format (cat0,cat1,cat2):
    - (x,0,0) is a main category
    - (x,x,0) is a lower category which can contain items and subcategories
    - (x,x,x) can only contain items

    I have two tables:
    -'cat': containing the ID's of the categories, their name and their hierarchy.
    -'stock': containing all the items in stock, with a reference 'cat' to the category ID in which the item resides

    I need to find out the number of items that are in each category, including subcategories. I have knocked up the following code which works perfectly but I am a little concerned about the number of queries that are involved (over 130 at the moment, and the site will grow considerably).

    Is there a way I can reduce the number of queries by joining databases or something else?

    Also since I have no experience at all using databases of any kind it is very well possible I have made a serious design error when I created these tables. Is this the way this is normally done, using two tables with one containing a reference to another, or is there a better way?


    Thanks for helping!!



    PHP Code:
    function calc_num_items()
    {
        
    $query 'SELECT ID, cat0, cat1 FROM cat WHERE cat1 !=0 AND cat2 = 0';
        
    $query_result mysql_query($query);
        while ( 
    $row mysql_fetch_assoc($query_result) )
        {
            
    $query2 'SELECT ID FROM cat WHERE cat0 = '.$row['cat0'].' AND cat1 = '.$row['cat1'];
            
    $query_result2 mysql_query($query2);
            while ( 
    $row2 mysql_fetch_assoc($query_result2) )
            {
                
    $query3 'SELECT cat FROM stock WHERE cat = '.$row2['ID'];
                
    $query_result3 mysql_query($query3);
                
    $cat[$row['ID']] += mysql_num_rows($query_result3);
                
    $cat[$row2['ID']] += mysql_num_rows($query_result3);
            }
        }
        return 
    $cat;


  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    > need to find out the number of items that are
    > in each category, including subcategories

    select cat,count(*) from stock group by cat

    > Is this the way this is normally done, using two
    > tables with one containing a reference to another

    looks fine to me, except for the cat table

    the normal way to design a hierarchy is

    create table cat
    ( ID integer
    , catname varchar
    , parentcat integer )

    each subcategory contains a foreign key to its parent

    cats at the top of their trees have a null parent key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast thechronic's Avatar
    Join Date
    Jun 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see the point in using a field for parentcat instead of putting the complete hierarchy in every key. Hmm should've thought about that, it would have made things easier.

    I have been reading about 'GROUP BY' but I don't understand the purpose of it at all. The MySQL manual does not even try explaining it, it's probably assumed to be common knowledge. Does it return a group of results in one row?
    I'm going to experiment with this function and try to figure it out.

    And the manual mentions this about COUNT(*):
    COUNT(*) is somewhat different in that it returns a count of the number of rows retrieved, whether they contain NULL values.
    What does this mean? Are the NULL values counted or not? I surely hope not

  4. #4
    SitePoint Enthusiast thechronic's Avatar
    Join Date
    Jun 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok I still don't really get the exact meaning of the GROUP BY thing, but I have managed to get some results from it. It does not seem to be possible to use mysql_fetch_assoc() because I don't know by what name I can access the count, $row['COUNT'] doesn't work.

    I do seem to have the solution to my endless mysql calls in sight, since it seems I can lose the inner loop of my original code using this function. I'll try to work it out. Thanks r937!!

  5. #5
    Your Lord and Master, Foamy gold trophy Hierophant's Avatar
    Join Date
    Aug 1999
    Location
    Lancaster, Ca. USA
    Posts
    12,305
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Assign your count an alias to use it elsewhere...

    ie: SELECT count(*) AS total FROM stock

    This would give you the total product count. To access it you would use $result['total'], just like any other column.

    COUNT returns all rows. If your field allows NULL as a value, it will count them as valid fields. The trick is to do the count either on the entire record [count(*)] or on a field where you no that a NULL isn't accepted like the primary index [count(catid)]. Using COUNT on the record as a whole is generally faster than using it on a single field in the record.
    Wayne Luke
    ------------


  6. #6
    SitePoint Enthusiast thechronic's Avatar
    Join Date
    Jun 2002
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm starting to understand it... when you query a "COUNT(*) GROUP BY cat" you get the number of individually existing cats back, with a count of the total amount of times this cat exists in the table. I can use the 'AS' to get an associative array back.

    So basically I can get the same results using only two queries: one to get all the categories (from the cat table) and one to get the count of items that share the same cat (from the stock table); put both results in an array and do my calculations with the arrays instead of querying all the time. This would be a major improvement

    Thanks for the insight!!


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
  •