Trying to tag Multiple Dropdown as Selected from Database

I appreciate any help as I am learning (fast but I have gaps!).

I currently have an app in PHP that associates a User in a table called "users" with multiple Departments from a table called "departments" and makes the relationship in a table called "mgrdepts".  

When the user record is entered, the PHP form used has a dropdown box populated from "departments" and it allows multiple selections.  Those selections are inserted into "mgrdepts" where users.userid is recorded as mgrdepts.mgrid and the departments.deptid is recorded as mgrdepts.deptid.  One user may have as many as 10 records in the mgrdepts table.  

I am trying to make an edit user form that will show the same dropdown box as well as the departments selected for that user in mgrdepts.  I am new at PHP.  I just started using PDO.

The code below works ALMOST right, but it only selects a SINGLE item in the Dropdown list, though it always a correct one.... just never more than one:

  <?
			// Retrieve departments selected from mgrdepts table for this userid
			
			$result4 = $conn->prepare('SELECT * FROM mgrdepts WHERE `mgrid`= ?');
			$result4->bindParam(1, $uid, PDO::PARAM_INT);
			$result4->execute();
			$row4 = $result4->fetch(PDO::FETCH_ASSOC);
			
			// Retrieve all departments for the dropdown.
			$result3 = $conn->prepare('SELECT deptid, deptname FROM departments');
			$result3->execute();
			$dropdown3 = "<select name='mgrdept[]' size='6' multiple='multiple'>";
			while($row3 = $result3->fetch(PDO::FETCH_ASSOC)) {
				$selected3 = $row3[deptid] == $row4[deptid] ? 'selected="selected"' : '';
				$dropdown3 .= "\r\n<option value='{$row3['deptid']}' $selected3>{$row3['deptname']}</option>";
				}
			$dropdown3 .= "\r\n</select>";
			echo $dropdown3
			?>

Open in new window

hydraziAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
Something like this would work:
  <?
			// Retrieve departments selected from mgrdepts table for this userid
			
			$result4 = $conn->prepare('SELECT * FROM mgrdepts WHERE `mgrid`= ?');
			$result4->bindParam(1, $uid, PDO::PARAM_INT);
			$result4->execute();

                        #Build an array of selected depts:
                        $selected_depts = array(); #initialise here, in case it remains empty
			while($selected_value = $result4->fetch(PDO::FETCH_ASSOC)) {
                           $selected_depts[$selected_value[deptid]] = true;
                        }
			
			// Retrieve all departments for the dropdown.
			$result3 = $conn->prepare('SELECT deptid, deptname FROM departments');
			$result3->execute();
			$dropdown3 = "<select name='mgrdept[]' size='6' multiple='multiple'>";
			while($row3 = $result3->fetch(PDO::FETCH_ASSOC)) {
				$selected3 = isset($selected_depts[$row3[deptid]]? 'selected="selected"' : '';  # Note the change to this line
				$dropdown3 .= "\r\n<option value='{$row3['deptid']}' $selected3>{$row3['deptname']}</option>";
				}
			$dropdown3 .= "\r\n</select>";
			echo $dropdown3
			?>
                                  

Open in new window

0
Terry WoodsIT GuruCommented:
Actually, putting the logic into a single query would be much neater:
  <?

			// Retrieve all departments for the dropdown, and whether the user is a manager of it.
			$result3 = $conn->prepare('SELECT deptid, deptname, (select count(*)
from mgrdepts where mgrid = ? and deptid = departments.deptid) as selected FROM departments');
			$result3->bindParam(1, $uid, PDO::PARAM_INT);
			$result3->execute();
			$dropdown3 = "<select name='mgrdept[]' size='6' multiple='multiple'>";
			while($row3 = $result3->fetch(PDO::FETCH_ASSOC)) {
				$selected3 = $row3[selected]>0 ? 'selected="selected"' : '';
				$dropdown3 .= "\r\n<option value='{$row3['deptid']}' $selected3>{$row3['deptname']}</option>";
				}
			$dropdown3 .= "\r\n</select>";
			echo $dropdown3
			?>
                                  

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hydraziAuthor Commented:
I learn so much when people give me examples and help like this.
I am trying the second solution now... having a small syntax issue somewhere...
0
hydraziAuthor Commented:
Not only answered the question to get the code working, but taught me a lot and cleaned the code up!  Wow!  Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.