SitePoint Sponsor

User Tag List

Results 1 to 25 of 25
  1. #1
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Category sql issue

    Hi,

    I have categories table like the following:

    id medint auto incr
    pid medint
    catname varchar 25

    now in listings table i have

    id medint auto incr
    cid medint
    etc.
    etc.

    on showcat.php

    i have query like:

    select * from listings where cid = $_GET["cid"];


    It all works great. But now the trouble is that the catgeories table which i have shown above is a single table which also keeps sub categories. So in that table i have records like:

    ==========================
    ID PID CATNAME
    ==========================
    1 0 Catgeory1
    2 1 Sub-Catgeory1
    3 1 Sub-Catgeory2
    4 0 Catgeory2
    5 2 Sub-Catgeory1
    6 2 Sub-Catgeory2

    What i am trying to do is that even if the record is in a sub-category it should display when the user is viewing the parent category.

    So if the listing is in Sub Category2 of Category 1 i.e (3) and user is doing showcat of 1 even then it should display records of 1,2,3. How do i do that ?

    So if the showcat.php as query liek: SELECT * FROM listings WHERE cid = 1 then it should display records of all categories which are related to cid 1 i.e of 2 and 3 also.

    Please help.

    Thanks.

  2. #2
    SitePoint Addict ruba's Avatar
    Join Date
    Apr 2005
    Location
    Amman -Jordan
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Open Blocked website
    Open Blocked Website
    Knowledge Is Knowing That A Tomato Is A Fruit,
    Wisdom Is Not Putting It In A Fruit Salad.

  3. #3
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    If I understand what you are looking for, then this should do the trick:
    Code:
    SELECT * 
    FROM listings
    LEFT JOIN categories
    ON listings.cid l = categories.id c
    WHERE l.cid = 1 OR c.pid = 1

  4. #4
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If your database supports sub queries try like this:
    Code sql:
    SELECT 
    	listings.* 
    FROM 
    	listings 
    WHERE 
    	listings.cid IN(SELECT id FROM categories WHERE categories.id=1 OR pid=1);
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  5. #5
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I use php/mysql will the sub queries work ?

    Thanks.

  6. #6
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by samanime View Post
    If I understand what you are looking for, then this should do the trick:
    Code:
    SELECT * 
    FROM listings
    LEFT JOIN categories
    ON listings.cid l = categories.id c
    WHERE l.cid = 1 OR c.pid = 1
    samanime!
    Your query statement seems little different. Which database does this belong? MySQL gives the syntax error. I think the following is the correct one for MySQL.
    Code sql:
    SELECT * 
    FROM listings l
    LEFT JOIN categories c
    ON l.cid=c.id
    WHERE l.cid = 1 OR c.pid = 1
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  7. #7
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Tapan View Post
    Hi,

    I use php/mysql will the sub queries work ?

    Thanks.
    MySQL 5 and upper version should support the sub queries. But the last one that i have edited the samanime's style that should work for you. Try that one.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you guys have both mixed up which table should be on the left in the left outer join

    by writing listings LEFT OUTER JOIN categories you are actually suggesting that there could be listings which don't belong to a category

    now, that might very well be the case (if listings.cid is NULL), although it would be unusual in most categorization schemes i've seen

    but it is irrelevant to this problem, because this is a category list
    Code:
    SELECT c.id
         , c.catname
         , l.id
         , l.etc
      FROM categories AS c
    LEFT OUTER
      JOIN listings AS l
        ON l.cid = c.id
    UNION ALL
    SELECT c.id
         , c.catname
         , l.id
         , l.etc
      FROM categories AS c
    INNER
      JOIN categories AS s
        ON s.pid = c.id
    INNER
      JOIN listings AS l
        ON l.cid = s.id
    p.s. subqueries came in with 4.1

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh.. yes I did not think about that, sorry. I just edited the query posted by samanime to remove the syntax error in MySQL.
    r937
    but it is irrelevant to this problem, because this is a category list
    But using sub-query should work because Tapan has said in his first post:
    Tapan
    So if the showcat.php as query liek: SELECT * FROM listings WHERE cid = 1 then it should display records of all categories which are related to cid 1 i.e of 2 and 3 also.
    If i have understood the lines well he wants to display all the listings of the selected category and sub-category of the selected category as well.
    Last edited by Raju Gautam; Feb 3, 2008 at 11:18.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  10. #10
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    Ah, in my question I just put my table alias names in the wrong place (it was about 4 AM when I posted that). What I had said should have been what you said, rajug.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by rajug View Post
    If i have understood the lines well he wants to display all the listings of the selected category and sub-category of the selected category as well.
    yup, so a listing without a category does not belong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Seems due to my simple database/table design the queries have became complex. If you had to design a db/table like that then how would you do it ?

    Thanks.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    are you asking me?

    i would have one table for categories, where each category links to its parent category with a pid foreign key, and one table for listings, where each listing links to its category with a cid foreign key

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    Quote Originally Posted by r937 View Post
    yup, so a listing without a category does not belong
    Hi,

    Yes that is corrcect but we all missed 1 point here.

    We are just concentrating on 1 sub category. What if there is more than 1 sub-category under a category ? Or what if its like this:

    ID PID CatName
    1 0 Category1
    2 1 SubCategory1
    3 1 SubCategory2
    4 2 SubSubCategory1_of_SubCategory1
    5 3 SubSubCategory1_of_SubCategory2

    Now all the categories/sub catgeories are linked to Main category. So how do i get listings of all the sub categories which eventually belong to the main category ?

    Thanks.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tapan View Post
    So how do i get listings of all the sub categories which eventually belong to the main category ?
    just extend the pattern...

    CAT left outer join LISTING
    UNION ALL
    CAT inner join SUBCAT inner join LISTING
    UNION ALL
    CAT inner join SUBCAT inner join SUBSUBCAT inner join LISTING

    alternatively, you could write a recursive procedure, but be warned, repetitive calls to the database are much less efficient

    or you could abandon the adjacency model (parent_id) altogether, and use nested sets to store the category hierarchy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    or you could abandon the adjacency model (parent_id) altogether, and use nested sets to store the category hierarchy
    Any example please...i am desperate.

    Thank you.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tapan View Post
    Any example please...
    Storing Hierarchical Data in a Database



    note: at the end of the article, there's a bad link to an article by Joe Celko, try these instead --

    http://searchoracle.techtarget.com/t...072375,00.html
    http://searchoracle.techtarget.com/t...072361,00.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I just check the tuts. How is that any different from the one we use ? They are using fullnames instead of autonum id's. For eg. in the tut they say:

    For instance, the path to ‘Cherry' is ‘Food' > ‘Fruit' > ‘Red'. To get this path, our function will have to start at the deepest level: ‘Cherry'. It then looks up the parent of this node and adds this to the path.
    So we can do the same for the auto number id's. Look for the last one and get its parent etc. etc. So where's the difference ?

    Thanks.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you've just read the adjacency model stuff

    go to page 2 for the model which eliminates the parent_id and uses lft and rgt columns instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    HI,

    So according to artcle my categories table will now be:

    id medint autonum
    parent varchar(25)
    title varchar(25)
    lft int
    rgt int

    Correct ?

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think you would remove the parent column

    i'm sorry, i can't help you on the lft/rgt model, i just cited it as an alternative, but i don't use it myself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Can you please tell me what method you use ?

    Thanks.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Tapan View Post
    Can you please tell me what method you use ?
    i use the adjacency model

    see Categories and Subcategories

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    No matter which model of categories i use i don't understand how it will solve my original question. :-p ? I don't want to get a category tree as illustrated in the examples I am trying to fetch all listings (not categories) which belong to a category which can be a parent or sub category and get listings that belong to that parent or its all sub categories and not just 1 sub category.

    Thanks.

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    SELECT LISTING.columns
    FROM CAT inner join LISTING
    UNION ALL
    SELECT LISTING.columns
    FROM CAT inner join SUBCAT inner join LISTING
    UNION ALL
    SELECT LISTING.columns
    FROM CAT inner join SUBCAT inner join SUBSUBCAT inner join LISTING
    ...
    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
  •