CODEWITHSUNDEEP
phpmysqlselect
RolandFlyBoy
RolandFlyBoy

Reputation: 193

MySQL SELECT: Returning multiple columns based on a column value in the same table

I'm reading in rows from a single table containing file meta information. One of the columns, acl, contains a flag (0,1 or 2) mapping to (unrestricted, restricted or privaleged). I'd like to create a SELECT query that will return file data based on two PHP variables, $userId and $isAdmin. The logic is as follows:

So basically I'm doing a query on the table and only returning the rows that the user is allowed to see

I've tried using all sorts of CASE and IF statements in a subquery but can't solve it. Starting to thing it's not possible to do without processing the SQL result in PHP.

Any gurus see a way?

Upvotes: 0

Views: 1040

Answers (2)

Peter Zeller
Peter Zeller

Reputation: 2276

I think something like this should do the job:

$qry = 'SELECT * FROM yourtable 
WHERE (acl = 0)
OR (acl = 1 AND '.$isAdmin.')
OR (acl = 2 AND '.$userId.' = fileownerid)';

Upvotes: 0

Paul Tomblin
Paul Tomblin

Reputation: 182772

select * from file where (acl = 0) or (acl = 1 and ? = 'TRUE') or (acl = 2 and ? = fileownerid) then bind isAdmin and userid to variables 1 and 2.

Upvotes: 1

Related Questions