Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2009
    Posts
    215
    Thanks
    74
    Thanked 0 Times in 0 Posts

    Lisit Results By categorys

    Hello

    I have Made Small Module , there is Small Problem i'm stuck with ,

    I have 2 Tables 1- for Categorys Name 2- for Names ..

    Now i need to list names in Categorys .. for example i have 3 Categorys .

    Cat1 , Cat2, Cat3,

    Each Has 3 Names .. Now i need to list this Names under the Category they Belong too .. like

    Cate1
    ------
    1-Name1
    2-Name 2
    3-Name3

    Cate2
    -----
    1-Name1
    2-Name 2
    3-Name3

    Cate3
    ----
    1-Name1
    2-Name 2
    3-Name3

    Any idea?

  • #2
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    I do not post the final solution. But I hope it would give you an idea...

    The table `names` should contain the field `category_id` - id of the category to which it belongs.

    You simply names like this:
    Code:
    SELECT n.name
         , c.category_name
         , c.id
      FROM names AS n
    INNER
      JOIN categories AS c
        ON c.id=n.category_id
    ORDER
        BY c.id
         , n.name
    Here I assumed that category_id is always set to some category id and could not be null.

    Then you pass the resultset in a loop and show the names for each category.

    You could even do most of the work right in the SQL by using GROUP_CONCAT() ... (provided the DB is MySQL I mean)

  • #3
    Regular Coder
    Join Date
    Aug 2009
    Posts
    215
    Thanks
    74
    Thanked 0 Times in 0 Posts
    Thx,
    and i had this idea before but i did not know wht after that .. this is SQL Select.

    PHP Code:
              $result mysql_query"SELECT c.c_id,c.c_name,COUNT(p_id)
                            FROM "
    .$DB->prefix("category")." as c
                            LEFT JOIN "
    .$DB->prefix("name")." as p ON p.category = c.c_id
                            GROUP BY c.c_id" 
    ); 
    Then wht? Loop?

  • #4
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    This query would not list names under categories. It selects the categories and counts number of names in each category.

    Also strictly speaking if GROUP BY with aggregate function is used, you need to group by all non-aggregate fields. I think MySQL would allow the query you have shown, but still I would recommend to change the GROUP BY to:
    Code:
    GROUP BY c.c_id,c.c_name
    Last edited by SKDevelopment; 02-26-2010 at 08:16 AM. Reason: grammar

  • #5
    Regular Coder
    Join Date
    Aug 2009
    Posts
    215
    Thanks
    74
    Thanked 0 Times in 0 Posts
    Yah Exactly , It dose not list the Names but it Lists the Category names

    Cat1 Cat2 ..

    I could not Figuer how to Get the Title Names from the Names Table with the Command i used above ..

  • #6
    Regular Coder
    Join Date
    Mar 2006
    Posts
    238
    Thanks
    3
    Thanked 37 Times in 37 Posts
    Try this (not checked):
    Code:
    SELECT c.c_id
         , c.c_name
         , (SELECT GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR '\n') FROM name AS p WHERE p.category = c.c_id GROUP BY p.category) as names
      FROM category AS c


  •  

    Posting Permissions

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