SELECT WHERE CASE statement

Developers Programming
#1

I am working on a project in which the user’s access to records is restricted based on the user’s User Group. I have created a global variable $usr_sec_group, and I want to add to the WHERE clause in the SELECT statement for several applications a CASE statement that applies a different filter based on the value of $usr_sec_group. I am a relative “newbie” with regards to mySQL, and my attempts at writing such a statement haven’t worked. Here is the basic logic:

SELECT 
   field1,
   field2,
   etc
FROM
  Organizations
CASE $user_sec_group
  WHEN 1 THEN 'filter_statement_1'
  WHEN 2 THEN 'filter_statement_2'
  WHEN 3 THEN 'filter_statement_3'
  ELSE 'filter_statement_else'
END CASE
ORDER By
   field1

The ‘filter_statements’ could be any valid filter, such as

'oName => 'a' AND oName < 'g'

I am assuming that the problem is a relatively simple matter of syntax, but so far I haven’t been able to write a CASE statement that works.

I will be grateful for some guidance!

Best regards,

Eric

#2

One possibility is:

SELECT field1, field2, fieldx FROM tablename WHERE [filter_statement]

Set [filter_statement] to “1=1” then it shows all records, set [filter_statement] to field1 = ‘x’ then shows only these data sets.

I do this more often, for example, if I need occasionally several filters: SELECT … WHERE 1=1 AND 2=2 AND 3=3 etc. and replace in the sql string then 1=1 by field1=x … speak, a placeholder for the conditional.

#3

Thanks, Reinhard, for the suggestion. I am afraid I don’t understand the last part of your comment:

Could you please explain that in more detail? – Eric

#4

another solution could be to set the whole sql-statement on the starting event manually. You are able to set the statement as you want and can change the condition as you want. Made that in an app for member-management and works perfect …

BR

EricB

#5

[QUOTE=EricB;13536]another solution could be to set the whole sql-statement on the starting event manually. You are able to set the statement as you want and can change the condition as you want. Made that in an app for member-management and works perfect …

BR

EricB[/QUOTE]

Eric, could you explain a bit more on what you did and how?? If you don’t want to explain here, could you send me PM?
I think this would solve a few problems that I’m having and sure would help me out.
Thanks
Alan

#6

Bump … how do I do this?