SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    San Diego, CA (USA)
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help merging two mysql queries...

    PHP Code:
    $query $db->query("SELECT detid FROM details WHERE userid='".$user->id."'");

    $no_use[] = 0;

    while (
    $row $db->obj_loop($query)) {
        
    $no_use[] = $row->detid;
    }

    $query $db->query("SELECT id, name FROM detcats ORDER BY name ASC");

    while (
    $row $db->obj_loop($query)) {
        if (!
    in_array($row->id$no_use)) {
            
    //Loop through results and generate a drop down html menu
        
    }

    This is part of a detail editing system where a user can provide details to certain categories (such as Personal Quote, Hobbies, etc.), available by a mysql table generated select list.

    What the above code accomplishes is making the drop-down list of available categories, depending on what categories they have already provided details to.

    What I want to do is have a query that gets the available categories and ignores categories already entered into (basically what the script above does with two queries). The "details" table is a table holding the user-submitted details and thier respective category ids, while the "detcats" table is a table holding available categories and thier id numbers (auto-increment column).

    I know it is possible to mix the two queries... I tried everything I knew, but I just couldn't do it.

    Here is the closest I got:

    PHP Code:
    $query "
            SELECT detcats.*
            FROM detcats
            LEFT JOIN details ON details.detid != detcats.id
            WHERE details.userid = '"
    .$user->id."'
            ORDER BY detcats.name ASC
    "

    What this query does is return two rows for each single category not submitted to, and one row for each category already submitted to.

    I want it to return one row for each single category not submitted to, and zero rows for each single category already submitted to. How can I do this?

    Thanks in advance.

  2. #2
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Southwest US
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think this query should do the trick for you:
    PHP Code:
    $query "SELECT detcats.* 
              FROM detcats LEFT JOIN details
              ON details.detid = detcats.id
              WHERE details.userid = '"
    .$user->id."'
              AND details.detid IS NULL"

    Hope this helps,
    Itshim

  3. #3
    SitePoint Zealot
    Join Date
    Nov 2004
    Location
    San Diego, CA (USA)
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, thanks!

    I mixed what you suggested with some changes of my own. Here is the final, working query:

    PHP Code:
    "SELECT detcats.id, detcats.name
    FROM detcats LEFT JOIN details
    ON (details.detid = detcats.id AND details.userid = '"
    .$user->id."')
    WHERE detcats.minrank <= '"
    .$user->rank->power."'
    AND details.detid IS NULL" 

  4. #4
    SitePoint Addict
    Join Date
    Oct 2004
    Location
    Southwest US
    Posts
    203
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Glad I was some help, left join queries can be such a b!#%h.


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
  •