View Full Version : Listing database rows and checking to see if those row values exist in another table

09-19-2011, 06:01 PM
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.

$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);

<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>

<input type="submit" value="submit"/>

What I'm trying to accomplish is if the list of folders is:


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:

<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!

Old Pedant
09-19-2011, 07:09 PM
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.)

09-19-2011, 07:54 PM
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 _________|


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

Old Pedant
09-19-2011, 08:23 PM
Okay...looks great.

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

<select multiple="multiple" name="access[]">
$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";

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?

09-19-2011, 09:55 PM
Eureka, it worked! Thank you so much!