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 10 of 10
  1. #1
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Ignore empty links in table

    Hello. I have a set of tables like so

    Code:
    category -> subcategory - gallery -> gallerylink-> images
    each linked by a parentid

    Code:
    category - holds a categoryID
    subcategory table is linked to category via categoryid
    gallery linked to subcat via subcatid
    gallerylink holds galleryid and imageids
    images linked to gallery via gallerylink table
    Ok so I am trying to get a random imageid(to display a thumbnail) out of my images table for a specific user. At the point I run this query, all I have is the userid. Here is my query:

    Code:
    select CategoryID from category where UserID = 1 ORDER BY RAND() limit 1
    I then take the categoryid I get here and my userid and run a more specifc query to get the imageid I need using all the linked tables.

    The problem is when I run this categoryid query, it is possible that it grabs a categoryid that has no subcategory assigned yet, or gallery or images. So I only want to look at categoryids to which have a record in the subcat table and the subcat table has a record in the gallery table and it has a record in the gallerylink table.

    So the categoryid that is randomly selected has to be linked all the way to an existing image.

    How do I accomplish this without running 4 separate queries?

    THank you for any help with this.

  • #2
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    "images linked to gallery via gallerylink table"

    so from here u just want to grab the image name? and you want it to be randomized?

  • #3
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks for the reply

    Well I have a query that is not posted that handles getting the random image from the gallerylink table. That works if I give it a valid categoryid that has subcats, galleries and images assigned.

    All I need is a random categoryID from my category table to put in that query. The query I posted is the one I am concerned about. As it grabs a random catID it is possible that it grabs one that does not have a subcategory assigned or does have a subcat but does not have a gallery etc.

    What I need to know is how to select a category out of the category table but ONLY if there is a record in teh subcategory table with that categoryid.

    I actually need more than this but if I can at least figure this out then I can use the solution to continue checking on down the line for gallery and images.

  • #4
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    Right, i understand what your asking for.
    and I'm trying to answer your question, and at the same time show you how to do this all at once, while only grab stuff that exists. work with me here! ;-)

    so for:
    "images linked to gallery via gallerylink table"
    whats the field name that your grabing?

  • #5
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    oh ok, I am getting the imageid. THe gallery table has a galleryid. The images table has a imageid and the gallerylink table holds both and links them together.

    Here is the second query:

    Code:
    $query = "select images.ImageID from category, subcategory, gallery, images, gallerylink where category.CategoryID = ".$row["CategoryID"]." and subcategory.CategoryID = category.CategoryID and subcategory.SubCategoryID = gallery.SubCategoryID and gallery.GalleryID = gallerylink.GalleryID and category.UserID = ".$_Session["UserID"]."  and category.UserID = images.UserID and images.ImageID = gallerylink.ImageID and images.Type = 1 and images.Active = 1 and images.Private = 2 and subcategory.SubCategoryID is not null and gallery.SubcategoryID is not null ORDER BY RAND() limit 1";

    Thanks
    Last edited by tripwater; 02-19-2007 at 10:30 PM.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    what you should be doing is grab a random image id and work your way up the table to make sure it is in the relevant categories.

    Have a look at this article if you are not overly familiar with traversing trees (categories/subcategories).

  • #7
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I appreciate the response guelphdad.

    From what I see in what you posted, I need to be using outer join to accomplish my task? This issue I am having is needing to be resolved in two different places on my page. Once at the gallery level and the other at the category level. Selecting a random imageid first then checking to see if it belongs to the root is what I am trying to do in one query. Is this not possible?

    I do not fully understand what I was looking at...

    Thanks again for you time. Any further direction with this is very welcome as well
    Last edited by tripwater; 02-20-2007 at 03:00 PM.

  • #8
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I am working with the query exapmle in the link you provided. I got it to work from cat->gallery. It shows me all of the names in each table. This is something I have never done or used (outer join).

    I will continue chiseling away at this in the hopes that I will be able to link this new method all teh way to the image level inside of the gallerlink table. I am really ignorant to this method so I have a feeling there is going to be a lengthy learning curve here for me on this one

    angst, any luck?

  • #9
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Ok, here is what I have to the gallery level and it works. It excludes results that have null in the subcat or gallery table.

    The problem I am having now is how to incorporate my (images and gallerylink table)

    Code:
    images.ImageID
    gallery.GalleryID
    gallerylink.ImageID, gallerylink.GalleryID
    Here is what I have working in a test query right now...

    Code:
    select root.name  as root_name
         , down1.name as down1_name
         , down2.name as down2_name
    	    
      from category as root
    left outer
      join subcategory as down1
        on down1.CategoryID = root.CategoryID
    left outer
      join gallery as down2
        on down2.SubCategoryID = down1.SubCategoryID
    where down1.CategoryID is not NULL  and down2.SubCategoryID is not NULL
    order 
        by root_name 
         , down1_name 
         , down2_name
    Last edited by tripwater; 02-20-2007 at 04:49 PM.

  • #10
    Regular Coder
    Join Date
    May 2005
    Posts
    262
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thanks for the replies, I figured it out with the help from another programmer I know. Here is my query

    Code:
    Select images.ImageID from images, gallerylink, gallery, subcategory Where images.ImageID = gallerylink.ImageID And gallerylink.GalleryID = gallery.GalleryID And gallery.SubCategoryID = subcategory.SubCategoryID And images.Type = 1 and images.Active = 1 and images.Private = 2 And subcategory.CategoryID IN ( select CategoryID from category where UserID = 1 and CategoryID is not null) And gallerylink.ImageID is not null And gallery.GalleryID is not null And subcategory.SubCategoryID is not null And subcategory.CategoryID is not null ORDER BY RAND() limit 1


  •  

    Posting Permissions

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