mysql和PHP | 显示组是否可以访问某些表单

Im trying to understand how to write a query to distinguish if a user has access to certain form or not. I know what i am asking here looks easy but when i tried to implement it, it was whole different thing. Maybe i am doing it wrong, but if i am doing it wrong so please tell what should be the proper way of implementing the solution.

Before Starting i want to mention the two tables names in start.

1. sys_forms,
2. sys_forms_in_groups

Also i am putting a SQLFIDDLE Link in the End of Explanation of my Problem. So if you might can edit the fiddle or tell me what changes should i make to achieve my goal.

Here is what i am trying to do.

if you can see the picture, On top dropdown box(its a select2 dropdown), user selects the group and it will return GroupID, on the base of which i want to populated the below datatable. (DataTable is just showing groups, its a dummy. but it will show forms, will fix it if problem is solved)

Now here the problem arise:

I want datatables to show all the forms available in sys_forms table in datatables but in actions columns of datatables only those checkboxes/switches should show granted which are avaialable in the selected group(Group can be selected for select2 dropdown as said before).

GroupID is the column of other table sys_forms_in_groups.

All i want is that all the forms should show in the datatables no matter what group i choose, But Actions Column in table should display Granted if the Group Has Access to that particular form.

forms_in_groups is for showing if group has access to that certain form or not. e-g

    FormID  GroupID
------------------------------
    1          1
    2          1
    1          2

FormID 1 is available to both groupID 1 and 2, on other hand FormID 2 is avaialable only to GroupID 1.

I Hope i did explained my problem, i am not a complex query coder. i only know simple queries. If anyone can help me solve this issue, i will be very grateful.

Please Open SQLFIDDLE: by clicking or Click Below Link:

http://sqlfiddle.com/#!2/1689a/2

EDIT:

SQL Fiddle is not working, so putting screenshots here.

table : sys_forms

table : sys_forms_in_groups

i have tried this query, but it only returns forms for the selected group, where i want that all forms should show but they must show granted in Actions Columns on checkboxes/switchButtons

SELECT * FROM (`sys_forms`) INNER JOIN `sys_forms_in_groups` 
ON `sys_forms_in_groups`.`FormID` = `sys_forms`.`FormID` WHERE `GroupID` = 1;

Finally Problem Solved and working perfectly fine, but after solving that problem i found out there is an other issue i didnt think of, but thats another matter. xD. However, It was a little rough way to solve but i got what i wanted.

Here is how i did it,

I first needed to update my select query to,

SELECT f.FormID
     , f.FormName
     , f.FormCIPath
     , MAX(g.IsMenuLink) AS IsMenuLink
     , GROUP_CONCAT(DISTINCT g.GroupID ORDER BY g.GroupID) AS GroupIDs
  FROM `sys_forms` f 
  JOIN `sys_forms_in_groups` g
    ON g.FormID = f.FormID
   AND g.GroupID IN (1,2)
 GROUP BY f.formID 

Here you can see it will return all the forms which belongs to group 1 and group 2 but in a way that 1 and 2 will be in same column separated by comma.

Here how it shows now.

I am not a very complex query master so i am very much grateful to stackoverflow community to help me with the query. As i wanted to join both results to show in comma separated value.

After the MysQL the jquery work was not much difficult, i only sent the group ID for which i wanted the result to show in table. and there i got this result in return where i separate the GroupIDs with javascript split function and i get my groups.

Thankyou again everyone.

I think you're, incorrectly, trying to offload the issue onto MySQL.

It's not MySQL's job to render the forms and show whether a selected group has access to a specific form. That will fall onto your rendering of the page; using Select2 in this case.

You just want to retrieve all forms, then show whether the selected group has access to a specific form. That comes down to some Javascript that makes that check for you and displays it properly.

If you want to return all forms, the query would be:

SELECT * FROM (`sys_forms`) INNER JOIN `sys_forms_in_groups`
ON `sys_forms_in_groups`.`FormID` = `sys_forms`.`FormID`;

If you only want the forms that group 1 has access to, the query would be just as you have it:

SELECT * FROM (`sys_forms`) INNER JOIN `sys_forms_in_groups` 
ON `sys_forms_in_groups`.`FormID` = `sys_forms`.`FormID` WHERE `GroupID` = 1;

I didn't do a lot of digging into Select2, so I can't really help you in that aspect.

What I can see from your database schema, an inner join is the correct choice here based on the data you are looking for. The only issue I can see is in the WHERE clause. Seems that it may try to look for GroupID in the sys_forms table, so try specifying.

SELECT * FROM (`sys_forms`) INNER JOIN `sys_forms_in_groups` 
ON `sys_forms_in_groups`.`FormID` = `sys_forms`.`FormID` WHERE
`sys_forms_in_groups`.`GroupID` = 1;