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 5 of 5
  1. #1
    Regular Coder
    Join Date
    May 2008
    Location
    Michigan
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post

    Listing database rows and checking to see if those row values exist in another table

    I am pulling my hair! So before I break down and cry, I figured I'd ask for help in my favorite forum

    I have a user login system where users can upload files. There are two types of users, admins and regular users. The admins can register new users and assign them folders that they're allowed to upload in. Admins can also edit users profiles if needed.

    Where I'm getting stuck is that when I'm on the edit user page as an admin, I list out the available folders which is fine, but what I'm trying to accomplish is that I want to show the admin which folders the user currently is assigned. I'm having a hard time relaying this into code.

    PHP Code:
    $id htmlspecialchars($_GET['id']);

    // grab the user's info
    $sql "SELECT * FROM users WHERE id = '$id'";
    $result mysql_query($sql);

    // grab all the folders
    $sql2 "SELECT * FROM folders";
    $result2 mysql_query($sql2);

    // grab the folders that have been access to the user (multiple folders can be assigned to a single user)
    $sql3 "SELECT access.folder_id FROM access INNER JOIN folders ON access.folder_id = folder.folder_is WHERE user_id = '$id'";
    $result3 mysql_query($sql3); 
    Code:
    <form action="#" method="post">
    First Name: <?php echo $rows['fname']; ?>
    
    Folder Access: <select multiple="multiple" name="access[]">
    <option selected="selected" value="none">None</option>
    <?php while($rows2 = mysql_fetch_array($result2)) { ?><option value="<?php echo $rows2['folder_id']; ?>"><?php echo $rows2['folder_id']; ?></option>
    </select>
    
    <input type="submit" value="submit"/>
    </form>
    What I'm trying to accomplish is if the list of folders is:

    A
    B
    C

    I need it to list all the folders, but then if the user is already assigned to folder C, I want the select option to look like:

    Code:
    <option value="B">B</option>
    <option selected="selected" value="C">C</option>
    Basically I need to know if the id of folder C (3) exists in the access table that houses which user id is assigned to which folder id.

    Sorry if this is confusing. If there's also a less simpler way to organize this, please let me know. I appreciate your help!

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Show us the list of fields in the relevant tables.

    FWIW, you will almost surely be using a LEFT JOIN.

    But i need to know what fields are in what tables. (Relevant fields, if there are many that aren't relevant, please.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    May 2008
    Location
    Michigan
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    I hope this helps. Ignore the underscores used as spacers.

    ---

    SELECT * FROM access

    +--------+---------+
    | user_id | folder_id |
    +--------+---------+
    | 1 _____| 1 ______|
    | 1 _____| 3 ______|

    SELECT * FROM folders

    +---------+-------------+
    | folder_id | folder_name |
    +---------+-------------+
    | 1 ______| A _________|
    | 2 ______| B _________|
    | 3 ______| C _________|

    SELECT * FROM users

    +---+-------+
    | id | fname |
    +---+-------+
    | 1 | Nicole |
    | 2 | Frank |
    | 3 | George |

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,211
    Thanks
    75
    Thanked 4,343 Times in 4,309 Posts
    Okay...looks great.

    Please understand that I don't use PHP, so could be PHP goof in here. The SQL looks easy.

    Code:
    <select multiple="multiple" name="access[]">
    <?php
    $sql3 = "SELECT F.folder_id, F.folder_name,IF(A.folder_id IS NULL,'','selected') AS sel " 
          . " FROM folders AS F LEFT JOIN access AS A "
          . " ON ( F.folder_id = A.folder_is AND A.user_id = $id "
          . " ORDER BY F.folder_name";
    $result3 = mysql_query($sql3);  
    
    while( $rows3= mysql_fetch_array($result3) ) 
    { 
        echo '<option value="' . $rows3["folder_id"] . '" ' . $rows3["sel"] . '>'
                . $rows3["folder_name"] . "</option>\n";
    }
    ?>
    </select>
    How it works:

    When you LEFT JOIN two tables, you get *all* the records from the left side table and then *only* records from the right side table that match the ON condition.

    So in this case, you will get all the folder_id and folder_name values from the folders table. And then you will get only the record(s) from the access table where *both* the user_id is the one given (e.g., 1) and the folder_id matches one in the folders table (e.g., 1 and 3 in the data shown).

    But now it gets sneaky:

    When there IS NOT a match, then all the fields in the right side table (the "A" table in this query) will be NULL!!!

    So we use the IF statement to test for that: If the value of A.folder_id is *NOT* null, then we give the output sel field a value of "selected". If A.folder_id *IS* null, then sel is the blank string.

    And now we can simply dump the value of sel into the <option>s as we create them and all the ones that matched in the access table will indeed show as selected and the others won't.

    Make sense?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    nicky (09-19-2011)

  • #5
    Regular Coder
    Join Date
    May 2008
    Location
    Michigan
    Posts
    216
    Thanks
    10
    Thanked 1 Time in 1 Post
    Eureka, it worked! Thank you so much!


  •  

    Posting Permissions

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