Comparing queries with PHP while loop

Question

Here is my table setup:

employee: Employee information, name, address, etc...
group: List of distribution groups.
employee2group: table that links employees to multiple groups.

I have it setup this way so I can add groups in the future, without having to add columns to my employee table. This may seem like common sense to you all, but I just started with PHP a couple months ago, so everything is still new and confusing to me.

So, I'm working on my update form, which will display a list of check boxes that is populated with a simple SELECT * FROM group query. The idea is to show the ones that an employee is part of as "checked" when I view the employee in the update form.

Currently, my while loop to show the list of check boxes is this:

<?php
 $group_list_query = "SELECT * FROM group"
 $group_list_result = mysql_query($group_list_query, $cmsWrite)
 while ($row = mysql_fetch_assoc($group_list_result)) {
  echo "<input type=\"checkbox\" name=\"distro_{$row['group_name']}\"> {$row['group_name']}";
 }
?>

Pretty simple. I might have some syntax errors in there, but in my code they don't exist, because it works fine.

So what I need to do, is run another query that returns ONLY the names of the groups that the employee belongs to:

SELECT group.group_name 
FROM group JOIN employee2group ON group.group_id = employee2group.group_id 
WHERE employee2group.employee_id ='{$_GET['employee_id']}'

Then, I need to compare the two queries, and output a normal check box when there isn't a match, and output a checked check box when there is a match.

I tried doing a while statement that set $row = query1 and $row2 = query2, and compare the $row and $row2 values, but then it only returned instances where both queries had results, instead of all of them.

I hope this makes sense, I've been trolling the internet for a while, and haven't found anything that pertains to my problem.

Thanks for reading!

linead · Accepted Answer · 2010-11-13 15:59:02Z

2

Two solutions :

1 : Get a array of the groupIds that user is a member of, then when looping through the group list. Check if the id is in that array with in_array, if it is dont display the check box

2 : Do a left join on the employee2group table and check if the value = null or not, if its null display the check box.

--

Off question topic but you should also look at using bound paramaters rather than just including them inthe sql statement like that, leaves it open to sql injection otherwise.

answered Nov 13, 2010 at 15:59

linead

1,64614 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

prodigitalson Over a year ago

+1 on the parameter binding advice. Actually he shouldnt use the mysql library unless he been legacied into it, instead he should use PDO_mysql or Mysqli.

Ben Over a year ago

Thank you very much linead. That works great, well, i should say, I did a test scenario with a created array, and it worked. Now i just need to figure out how to get my query into an array.

Ben Over a year ago

I used your first answer, by the way. Thanks!!

Collectives™ on Stack Overflow

Comparing queries with PHP while loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related