SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jan 2004
    Location
    cambridge uk
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    many-to-many left join select

    Hi People!
    I am making a document management system and have many-to-many relationships that I am storing with a 3rd table.

    so imagine each doc can be in more than one category:

    useful_files:
    ----------
    file_id
    file_desc

    useful_files_category
    --------
    cat_id
    cat_name

    file_cats
    -------
    file_id
    cat_id

    I can search for docs by category by using this query:
    Code:
    $result = @mysql_query('SELECT file_cats.file_id, file_cats.cat_id, useful_files.file_url, useful_files.file_desc, useful_files.file_comments
                            FROM file_cats
                            LEFT JOIN useful_files ON (useful_files.id = file_cats.file_id)
                            WHERE (file_cats.cat_id LIKE "'.$cat.'")
                            AND useful_files.file_comments LIKE "%'.$search.'%" ORDER BY useful_files.file_desc '.$max.'');
    But when $cat and $search are % it lists all the doc/category relationships. So if a doc is in more than one category it lists it twice. When people list "All Documents" I want to filter out the duplicates. If you know what I mean... Is there anything I can add to my query to do this?

    thanks!!

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,494
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    You are using different names in your table descriptions and in your query.
    Anyway, since you're selecting both the file id and the category id, if a file has more than one category, there's no way you can filter out the duplicates with MySQL. You'll have to do that in your script.
    If you don't need the category id and take it out of the SELECT, then adding a DISTINCT might take care of the duplicates.

  3. #3
    SitePoint Member
    Join Date
    Jan 2004
    Location
    cambridge uk
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok thanks. I tried distinct earlier and it did not work but I will have another bas at it. I know the column names are different... I just used them as an example of the schema... probably a bad idea!

    thanks!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by aerris View Post
    probably a bad idea!
    always is

    and so is using LIKE on id columns (which i assume are numeric)

    what actually drives the query? searching a specific category?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2004
    Location
    cambridge uk
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    a drop down:

    Code:
    // Request
    $result = @mysql_query('SELECT * FROM useful_files_category ORDER BY cat_name');
    if (!$result) {
    exit('<p>Error performing query: ' . mysql_error() . '</p>');
    }
    echo'<form action="kb.php" method="post"><table border="0" bgcolor="#1B18AF"><tr><td><font face="tahoma" color="white" size="2">Category: <select name="cat"><option value="&#37;">All Documents</option>';
    // Display
    
    while ($row = mysql_fetch_array($result)) {
      echo '<option value="'.$row['id'].'"';
           // select dropdown category selected
           if($cat==$row['id']){
             echo' selected';
             }
      echo'>' . $row['cat_name'] . '</option>';
    }
    
    
    echo"</select></td><td>";
    echo'<font face="tahoma" color="white" size="2">Keyword: <input type="text" name="search" size="20"></td><td><font face="tahoma" color="white" size="2">Results per page:<select name="rpp">';
    $counter=5;
    while($counter<=30){
      echo'<option';
                    if($counter==$page_rows){
                      echo' selected';
                      }
      echo' value="'.$counter.'">'.$counter.'</option>';
      $counter=$counter+5;
    }
    
    echo'</select></td><td><input type="submit" value="Search"></td></tr></table></form>';
    Which looks like this:



    /edit
    i just realised it would probably make more sense for the top drop down option to say "All Categories" rather than "All Docs" but that doesn't really change functionality does it?!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, i don't do php, so the only part of the code you posted that i understood was:
    Code:
    SELECT * FROM useful_files_category ORDER BY cat_name
    i am going to go out on a limb and guess that once the user selects a category, you want to return all the files in that category?
    Code:
    SELECT f.file_id
         , f.file_desc
      FROM useful_files_category AS c
    INNER
      JOIN file_cats AS fc
        ON fc.cat_id = c.cat_id
    INNER
      JOIN useful_files AS f
        ON f.file_id = fc.file_id
     WHERE c.cat_name = 'Some Category Name'
    if you pass in the cat_id from the dropdown, you can actually bypass the categories table:
    Code:
    SELECT f.file_id
         , f.file_desc
      FROM file_cats AS fc
    INNER
      JOIN useful_files AS f
        ON f.file_id = fc.file_id
     WHERE fc.cat_id = 5
    the easiest option to deal with is this one:

    ... <option value="&#37;">All Documents</option>

    in this case, you detect the % value in your php code, and run this query:
    Code:
    SELECT file_id
         , file_desc
      FROM useful_files
    easy, yes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Jan 2004
    Location
    cambridge uk
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    done and dusted!

    thanks a lot mr internet help man!


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
  •