I have a table from which I need to get results varying by certain user data:
I need to return the rows where each column or hit with its user parameter or is blank (null). Consider as user parameters the bottom of the image - USER_ID = 29 , USER_GROUP = ADMIN , etc.
In the case above, I would only return IDs 30, 31, 33 and 36.
The query below returns the records where each field is equal to or is null. The MySql find_in_set function does the trick of searching for a value in a textual list of comma-separated values.
select
ID, USER_ID, USER_GROUP, USER_ROLE, USER_LEVEL, USER_TYPE
from
tabela
where
(USER_ID = 29 OR USER_ID is null)
and (USER_GROUP = 'ADMIN' OR USER_GROUP is null)
and (find_in_set(1, USER_LEVEL) OR USER_LEVEL is null)
and (find_in_set(3, USER_TYPE) OR USER_TYPE is null)
and (USER_ROLE = 10 OR USER_ROLE is null)
See working in sqlfiddle .
The query below does exactly what is proposed in the statement:
SELECT *
FROM USER
WHERE (USER_ID = 29 OR USER_ID IS NULL)
AND (USER_GROUP = 'ADMIN' OR IFNULL(USER_GROUP, '') = '')
AND (find_in_set('1', USER_LEVEL) OR IFNULL(USER_LEVEL, '') = '')
AND (find_in_set('3', USER_TYPE) OR IFNULL(USER_TYPE, '') = '')
AND (find_in_set('10', USER_ROLE) OR IFNULL(USER_ROLE, '') = '')
You can check the correct return on SQLFiddle .
This is kind of weird and does not have enough information but come on.
select * from tabela where user_id = $user_id || user_group = $user_group || find_in_set($user_level, user_level) || find_in_set($user_type, user_type) || find_in_set($user_role, user_role) || (user_id = null && user_group = null && user_level = null && user_type = null && user_role)
This works if this field can contain more than one value.
I interpreted the rules as I could see in the question. If this is not this you should make the rules clearer. You did not even tell where this information comes from, I guessed it again. I also assumed that the void would be null.
I think that's it, I'll answer in PURE SQL
SELECT * FROM nomeDaTable WHERE USER_ID = 29 OR USER_GROUP = "ADMIN" OR USER_LEVEL = 1 OR USER_TYPE = 3 OR USER_ROLE = 10 OR (USER_ID = "" && USER_GROUP = "" && USER_LEVEL = "" && USER_TYPE = "" && USER_ROLE = "")