Two table search

[NZ_Kiwis]

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

[Weedpacket]

(Helpful hint: use ```backticks``` around your code to make it format as code.)

SELECT <what you really want> from
member_functions f
Inner join member_permissions p on p.FunctionID=f.ID
where p.MemberId=?

[NZ_Kiwis]

thanks, wasn't aware how we did that on the new format.

When I run your code with a user who has nothing in the permissions table, I get no functions either.

[dalecosp]

Well, should a user with no permissions have any functions? That's a policy decision, of course; and one for which I have no opinion either way 😃

Anyway, you get "no functions either" because it's an inner join ... you'll want either a left outer join or a right outer join if you want the extra records, left/right possibly depending on your table layout. Also, most types of joins have a tendency to return huge result sets, so test in a sandbox and check how many rows are returned before you put JOINs into production code ...