Mysql with if condition

Question

My Question is I need to get two conditions first one if a user is admin I need to display all transactions but if a users is not admin I need to display his own transaction only. hers is the code that I tried but does not work.

Tables I have

1. Users(Id, username, password, category ...etc)

2. Transaction (Id, name, description, username, …etc)

SELECT IF (  (select category from users where username = ‘ali’ )==’A’) THEN
    Select * from transaction
ELSE
    Select * from transaction where username = ‘ali’

I also tried with this one but still no solution

SELECT IF(
  SELECT category FROM users where username= 'ali' AND category ='A',
  SELECT * FROM Transaction ,
  SELECT * FROM Transaction where username= 'ali'
)

Thanks

Answer 1

This should work:

SELECT * FROM Transactions WHERE username = 'username' OR (SELECT 1 FROM users WHERE username = 'username' AND category = 'A')

Answer 2

I would prefer to do this logic in the code but you can check using an OR in the where clause:

SELECT * FROM Transaction t
WHERE t.username = 'ali' 
OR EXISTS (SELECT category FROM users where username= 'ali' AND category ='A')

Answer 3

try this

$where_text = ""; // initially blank

// here $user variable contains the current username

if($user!='admin') // check $user is admin or not if not 
{
   $where_text = " WHERE username='$user' ";  // update $where_text
}

$query = "SELECT * FROM `transaction` ".$where_text; // apply in the query

Now you can user your query by $query variable