Check if value exists in MySQL table and then select [Laravel 5]

Question

Here is my pivot table project_group:

+-----+----------+------------+----------+---------+
| ids | group_id | project_id | admin_id | user_id |
+-----+----------+------------+----------+---------+
|   4 |      115 |          1 |        1 | [3,4,5] |
|   5 |      115 |          2 |        1 | [5,2,1] |
|   6 |      115 |          3 |        1 | [1,3,6] |

This table represent group linked to the projects....user_id is which users can see projects/group... Is there any way to display correct projects/group only to the users defined in user_id?

Also content in user_id field can be changed....

yes. http://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query — Dimi, May 27 '15 at 13:23
Basically resuting from some bad database design. Dont store data like that — RiggsFolly, May 27 '15 at 13:26
you should have another table with 3 columns. id(auto_incremented), project_id, user_id. Then you do joins, and selects with the results. — Dimi, May 27 '15 at 13:30

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

The best way to handle this would be to first normalize your database. Storing comma separated lists in a cell is allowed, but generally bad practice, as explained in this question.

If you can have multiple users per project, you should have a linking table with a column for project and a column for user, like this:

project_users:
| project_id | user_id |

and you can make (project_id, user_id) a composite primary key.

That way, you can select the users for a project (say, project 1) like this:

SELECT user_id
FROM project_users
WHERE project_id = 1;

Once you have these, you can display the project data only to users whose id is returned in the above list.

I have built an SQL Fiddle that helps demonstrate this visually, if it helps.

It is good to note that this proper normalization gives the opportunity to a lot of useful data as well, as it becomes easier to search for users by project, but also you can search for project information based on a user.

edited May 23 '17 at 11:58

Community

1
1

answered May 27 '15 at 13:43

AdamMc331

16,492
10
71
133

Thanks! After I change database how can I select projects/group with Laravel 5 for user that is currently logged? – Vladimir Djukic May 27 '15 at 14:00
Is that possible because I have 2 pivot table project_user, project_group? – Vladimir Djukic May 27 '15 at 14:02
@VladimirDjukic I'm not sure what you mean? – AdamMc331 May 27 '15 at 14:05
For example user with id `3` is logged into the system and he should get on the home page name of project with id 1 and 3 (look from table above).... How to create query for that with Laravel 5? – Vladimir Djukic May 27 '15 at 14:17
I don't have a lot of experience with Laravel, but you can get projects 1 and 3 by using `SELECT * FROM project_users WHERE user_id = 3`. Hope that pushes you in the right direction! – AdamMc331 May 27 '15 at 14:19
Also I think I will have problem with `FOREIGN KEY` because I use MyISAM – Vladimir Djukic May 27 '15 at 14:26
That's possible, it was just for demonstration purposes. You don't have to use that. – AdamMc331 May 27 '15 at 14:27
Is ok to store json to MySQL? – Vladimir Djukic May 27 '15 at 14:29

Check if value exists in MySQL table and then select [Laravel 5]

1 Answers1