Combine two searches into one

General PHP Help
#1

Here is my code. I want to combine both searches into one if I can and still show all functions. Ticked if it exists in permissions table ticked if SetValue is 1 and unticked if 0 or non existent

$result2 = mysqli_query($link2, "SELECT * FROM `member_functions`");
	while($row2 = mysqli_fetch_assoc($result2)){
		$c = '';
		$fID = $row2['ID'];
		$result4 = mysqli_query($link2, "SELECT * FROM `member_permissions` WHERE `MemberID` = $ID and `FunctionID` = $fID");
		while($row4 = mysqli_fetch_assoc($result4)){
			if (mysqli_num_rows($result4) > 0){
				if ($row4['SetValue'] == 1){
					$c = 'checked';
				} else {
					$C = '';
				}
			}
	?>      
        <p>
        <label for="email"><b><?php echo $row2['FunctionName']; ?></b></label>
        <input type="checkbox" name="<?php echo $row2['ID']; ?>" value="<?php echo $row2['ID']; ?>" <?php echo $c; ?>>  
        </p>	
	<?php	
		}
	}
    ?>
#2

Show us the database structure and some sample data.

#3

structure, numbers in brackets is some example data.
member_permissions
ID (1)
FunctionID (2)
MemberID (23)
SetValue (1)

member_functions
ID (2)
FunctionName (UserAdmin)

#4

Well, assuming you have set the $ID value to the current member id, something loosely like this should work:

SELECT member_permissions.*, member_functions.FunctionName
FROM member_permission
LEFT JOIN member_functions ON member_permissions.FunctionID = member_functions.ID
WHERE member_permissions.MemberID = $ID

This joins the two tables based on the member function’s ID number. Not tested, but, should work.
(I think, just off the top of my head…)

But, if you already have the data in the member_permissions table, why not just put the function name in that table too. ??? That would make it much simpler.

#5

Ran that code and added a member ID to it but it returned no rows at all

#6

Well, I created two test tables going by your notes:
Table 1 fields: ID (int), FunctionID (int), MemberID (int) and SetValue (int).
I created two test entries into them:
Record 1 data: 1 , 1 , 1 , 1
Record 2 data: 1 , 2 , 1 , 1
Created a second table using your notes:
Table 2 fields: ID (int), FunctionName (Varchar-255)
I created two entries into them:
Record 1 data: 1 , “Function Name Number 1”
Record 2 data: 2 , “Function Name Number 2”

Then I used that same query with WHERE member_permissions.MemberID=1
And it pulled in both functions for for the member #1. (I entered two functions for the one member.)

So, it works. My guess is that it is not coded correctly. Did you try it it directly in your control panel or just in your program? Did you use a valid MemberID that is in your database? It should work.

#7

Yes, but if you change table 1 so that record 2’s first column is 2 not one and the last column is 0

So;

Record 2 data: 2, 2,1,0

Essentially if in the permissions table the member doesn’t have a row it won’t show that function but I want it to.

#8

Well, I am guessing I do not understand what you want. You have member permissions in one table.
In that table you have pointers to another table to various function names. You select one member only and use their database table id in a variable named $ID. You query the member’s permissions table and get their list of all the permissions for that one $ID. In there, you get a list of function id’s and the value of the function.

I assumed you wanted to pull the matching function name in for use displaying the function check-boxes.

So, what are you trying to do with those two queries?

#9

So you want all the functions returned even if the member doesn’t have them, as in you get a null value back when they do not have that permission?

#10

Yes that is correct, how can I do that?

#11

This is how I would do it…

http://sqlfiddle.com/#!9/87984c/2

Sponsor our Newsletter | Privacy Policy | Terms of Service