SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Advanced SQL help (big)

    I am building a FAQ manager for my website. I have three tables that look like this:

    ---------------------------------------
    FAQ TABLE
    +---+-----------+---------+
    | id | question | answer |
    +---+-----------+---------+
    | 1 | alskdfal | akjsas |
    | 2 | alskdfal | akjsas |
    | 3 | alskdfal | akjsas |
    +---+-----------+---------+

    FAQ_CATEGORIES TABLE
    +---+-------+-------------+
    | id | name | description|
    +---+-------+-------------+
    | 1 | asdf | asdfasddfa |
    | 2 | asdf | asdfasddfa |
    | 3 | asdf | asdfasddfa |
    +---+-------+-------------+

    FAQ_LOOKUP TABLE
    +---+-----+
    | fid | cid |
    +---+-----+
    | 1 | 1 |
    | 1 | 3 |
    +---+-----+
    ---------------------------------------

    Ok, so that is what my tables look like. Now, I am trying to setup two queries. One that will gather all categories assigned to the specified question. I can do that one, it looks like this:

    SELECT * FROM faq_categories, faq_lookup WHERE faq_categories.id = faq_lookup.cid AND faq_lookup.fid = '1';

    My problem is trying to select all the categories that are not currently assigned to the specified question. I am using a lookup table because I want to be able to assign the question to multiple categories. This is what is causing my problem with selecting the categories that are not currently assigned to the question. Because if the category is not assigned to the question, it simply is not in the lookup table.

    I sure hope my explanation was clear enough. If I need to add more detail, please reply to this with the detail I need to add... and I will do it. This has been bugging me for a few hours now.

    I hope it is something simple that I have overlooked, imagining it to be more difficult than it really should be.

    Thanks in advance!

    Z
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.



  2. #2
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, i don't know if i understand exactly what you want, but here goes. this should give you all categories that are NOT assigned to the current question. i'm assuming you have the current question's id in a variable, right? i'll call that $qid. here we go, with LEFT JOIN.

    Code:
    SELECT * FROM faq_categories c LEFT JOIN faq_lookup l ON c.id=l.cid WHERE l.fid<>$qid
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  3. #3
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it! Thanks for your help DR_LaRRY_PEpPeR. While the reply your posted above doesn't seem to work, a reply you posted to "table joins" does... Here is what my query looks like to select all categories NOT assigned to the current question:

    SELECT * FROM faq_categories LEFT JOIN faq_lookup ON cid = faq_categories.id WHERE faq_lookup.fid IS NULL;

    It works beautifully! I just wasn't aware of the IS NULL part...

    Thanks!

    [Z]
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.



  4. #4
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    your query will only give you categories that don't have a corresponding cid in the lookup table, do you know that? or is that what you want? for example, if there is other questions that, when combined, are in all categories, you won't get any rows -- if that makes sense. i thought you wanted all categories that weren't associated w/ a certain question.

    say you're on question 1 and it's in cat 1. question 2 is in cat 2. for question 1, don't you want category 2, since question 1 isn't in that category? cuz your query won't give it to you.

  5. #5
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Now that you metion it... your right. I have been testing with only one question being assigned. Oh man... MAJOR oversight. Dang. Guess I would have found out sooner or later. ;c(

    Your query...

    SELECT * FROM faq_categories c LEFT JOIN faq_lookup l ON c.id=l.cid WHERE l.fid<>$qid;

    ...stumps me a little. What is the "c" and the "l" for in the LEFT JOIN part? Is it an alias?

    Thanks!

    [Z]
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.



  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    that's what i was thinking, is that you hadn't tested it.

    and yeah, that's just an alias for the table names. you can take them out, but then you'll have to use the full table name, that's all. does my query not work? like i said, that $qid variable should be the question that you're on. so if you were on question 1, it would be:

    SELECT * FROM faq_categories c LEFT JOIN faq_lookup l ON c.id=l.cid WHERE l.fid<>1;

    let me know what's wrong if it's not working.

  7. #7
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am going to try to rebuild that FAQ manager I am working on at home. Currently it is only at my job. I work better from home... wish my job would let me.

    Appreciate all the help. Your kinda like my mentor. I will probably ask a lot of questions here because of you. Don't worry though, I hate asking questions just to ask... I have to be stumped before I ask. And even then I usually will try to find it on my own. I just think you learn more that way. But sometimes you simply need a little advice.

    Thanks!

    [Z]
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.



  8. #8
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, your query works... but guess what. It's not exactly what I need. ;(

    Here is a dump of the data from mySQL:
    -----
    FAQ Table:

    INSERT INTO `faq` VALUES (4, 'Why do the graphics on the site appear to overlap?', 'The problem lies in what browser you are using to surf the web. The Yokota Air Base, Japan website has been designed to be standards compliant. These standards are not new, matter-of-fact they are over 2 years old.\r<br />\n\r<br />\nTo learn more about standards in web browsers <a href="http://www.webstandards.org" target="_blank">click here</a>.\r<br />\n\r<br />\nTo fix the problem with the overlapping graphics you will need to update your web browser. Internet Explorer users can <a href="http://www.microsoft.com/windows/ie/default.asp" target="_blank">click here</a>. Netscape Navigator users <a href="http://home.netscape.com/computing/download/" target="_blank">click here</a>.\r<br />\n\r<br />\n<i>Note: All links will open in a new window. Reference to external links do not constitute endorsement by Yokota nor the U.S. Air Force. </i>');
    INSERT INTO `faq` VALUES (6, 'test', 'test');

    FAQ_CATEGORIES Table

    INSERT INTO `faq_categories` VALUES (1, 'Display and Graphics Problems', 'Are you noticing visual glitches in the Yokota website? This section of FAQs will help fix your display and graphics problems with the Yokota website.');
    INSERT INTO `faq_categories` VALUES (2, 'Site Usability and Navigation', 'Can\'t find your way around the Yokota website? This section of FAQs will help you understand the navigation system the Yokota website uses. ');
    INSERT INTO `faq_categories` VALUES (3, 'Contacting Yokota', 'Need to find a way to contact Yokota? This section will help you with finding ways of contacting Yokota.');

    FAQ_LOOKUP TABLE

    INSERT INTO `faq_lookup` VALUES (4, 1);
    INSERT INTO `faq_lookup` VALUES (4, 3);
    INSERT INTO `faq_lookup` VALUES (6, 2);
    INSERT INTO `faq_lookup` VALUES (6, 3);
    -----

    Lets say I am editing the question 4 (id of 4). You'll notice it is assigned to two categories - 1 and 3. When building the list of checkboxes, I have two queries. One to select all categories not assigned to the question, and one to select all categories assigned to the question. Well, at first I thought this was what I wanted. But it's not. You'll notice that category is assigned to question 4 AND is it assigned to question 6. This causes the category to appear twice in the list of checkboxes when editing (once checked and once unchecked).

    So my question changes to:

    What's the best way to do this?

    Thanks!

    [Z]
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.



  9. #9
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i see. ok, the query for finding out which categories the question is in is still good though, correct? you just need the query that finds out which categories the questions aren't in modified so that it doesn't list the category AGAIN if the question is already in that category. yeah, i understand how mine wouldn't work right. now, what you're going to do is keep track of which categories the question is in when you fetch them, then you'll know which to exclude in the second query. some code (of course, $qid is the id of your question again):

    PHP Code:
    $r mysql_query("SELECT * FROM faq_categories c, faq_lookup l
        WHERE c.id=l.cid AND l.fid=
    $qid");

    // fetch your stuff like you are now
    // except now you're going to add the category ids to an array

    while ($row mysql_fetch_array($r))
    {
    $assigned_cats[] = $row['id'];
    // do your other stuff
    }

    $r mysql_query('SELECT * FROM faq_categories
        WHERE id NOT IN(0' 
    implode(',' $assigned_cats) . ')'); 
    now that i think about it, that's what i should've used in the first place! you don't need the LEFT JOIN anymore. in the "...NOT IN..." part, i put the "0" in there so it wouldn't cause a MySQL error in the case that a question isn't assigned to any categories. just so you know.

    that's off the top of my head. let me know how it works.
    Last edited by DR_LaRRY_PEpPeR; Dec 10, 2001 at 00:03.

  10. #10
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your fix almost worked. Well, it did work - you just forgot a couple commas.

    PHP Code:
    $sql "SELECT * FROM faq_categories WHERE id NOT IN(0," implode(','$assigned_cats) . ")"
    I never thought of doing it that way. It's so much easier than what I was thinking about doing. I was going to make two arrays and loop through one to remove rows in another.

    Really Appreciate the help!

    [Z]
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.



  11. #11
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    no problem. i only forgot one comma though. it should be:

    PHP Code:
    $sql "SELECT * FROM faq_categories WHERE id NOT IN(0" implode(','$assigned_cats) . ")"
    don't put the comma after the 0, because in the case that there's nothing in $assigned_cats, what will be sent to MySQL?

    NOT IN(0,)

    which will cause an error. my way will send

    NOT IN(0)

    which is fine. and when there is stuff in $assigned_cats, this will be sent:

    NOT IN(01,2,4,5,8)

    and it will still work fine b/c "01" is treated the same as "1".

    if you're gonna put the 0, there, you might as well not put anything, since they'll both cause an error if $assigned_cats is empty.

    glad it's working now.

  12. #12
    eigo hanasemasu ka? Yes. =) ZuulJin's Avatar
    Join Date
    Dec 2001
    Location
    Japan
    Posts
    655
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hmm... I understand it know.

    Thanks for the help Dr.

    [Z]
    U.S. DoD Member in Japan?
    Choose your base. Buy|Sell. Easy
    @ APO Ads.




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
  •