Controlling the display of Column if the user is Admin or Not

Question

I am new to sql I am using myphpadmin for my database I am trying to Control the display of Column if the user login is Admin or Not. The column I am displaying is From Two different Tables which are TableA and Tableb. I did combine the two tables that has common value in column display all the columns that need to be display if the user who logged in is an Admin but I have no idea how to control the display if its user who logged in

I have three tables named;

TableA:

 id      Name    Section    Grade    status

 1a      aika    Section1    A       Active
 2s                          B       Inactive

===========================================

TableB:

id     PR_id     Name      Section    

111    2s      laika       Section2   
222    2s      Bes         Section3 

Query used to display column all together

 SELECT * FROM TableA WHERE Name != ''
 UNION
 SELECT TableA.id, TableB.Name, TableB.Section, TableA.Grade, TableA.status 
 FROM TableA 
 INNER JOIN TableB ON TableA.id = TableB.PR_id

Output is;

 id   Name   Section     Grade    status    

 1a   aika   Section1    A        Active      
 2s   laika  Section2    B        Inactive
 2s   Bes    Section3    B        Inactive

Tableuser

  id    username     usertype
  1     aika         admin
  2     den          user
  3     lina         user

if aika is the one who logged in because she is admin

all this column will display for Admin

 id   Name   Section     Grade    status    

 1a   aika   Section1    A        Active      
 2s   laika  Section2    B        Inactive
 2s   Bes    Section3    B        Inactive

---

but if den/lina who who logged in because she is a user the column that will only be display for her is below she will not see the column Grade

 id   Name   Section       status    

 1a   aika   Section1     Active      
 2s   laika  Section2     Inactive
 2s   Bes    Section3     Inactive

I am trying this query but its not working I would treally appreciate any advice or suggestion / help thank you very much

 Select usertype from Tableuser Where usertype='Admin'( SELECT * FROM TableA 
 WHERE Name != ''
 UNION
 SELECT TableA.id, TableB.Name, TableB.Section, TableA.Grade, TableA.status 
 FROM TableA 
 INNER JOIN TableB ON TableA.id = TableB.PR_id

Answer 1

I think you're looking in the wrong place for a solution to your problem. In fact, you've asked about this in two different ways (restricting through phpMyAdmin and through an SQL query) and I think doing it a third way is the best solution.

Through phpMyAdmin

You certain can restrict this through phpMyAdmin and MySQL/MariaDB column-level permissions. This requires you to give each user their own MySQL/MariaDB user account, which is unrelated to your Tableuser. I don't recommend this, because you generally want to handle user authentication in your application rather than as MySQL/MariaDB users, but you asked, so here's what to do:

First, create the new user account like normal. Then on the resulting page (or any time you go to edit that user from the User accounts tab), click the "Database" sub-tab:

Pick the correct database from the list, then click Go.

The next page shows database-level permissions. It sounds like you don't want to allow any, but your needs may vary.

Look at the top again for the "Table" tab. This takes you to the table-level permissions.

Select the appropriate permissions for this user; for instance SELECT privileges for all columns except 'grade':

Now you've restricted — at the database level — that user account from being able to SELECT from that column.

One caveat is that when that user is in phpMyAdmin and tries to Browse that table, they will get an error; this is because phpMyAdmin tries to run a "SELECT *..." query on the table and of course the permissions prevent that. The easiest workaround is to add a bookmark that instead has a query like SELECT `id`, `name`, `section`, `status` FROM `TableA`. Name this bookmark the same as the table and it will be used instead of the "SELECT *" query when the user clicks the Browse link.

The results:

Through your application

If your application queries the database, you can dynamically generate the appropriate query : SELECT `usertype` FROM `Tableuser` WHERE `id` = $userid;

Then use a simple if test; if the result is 'admin', generate the query as SELECT `id`, `name`, `section`, `grade`, `status` FROM `TableA`, else set the query to SELECT `id`, `name`, `section`, `status` FROM `TableA`. Since you're probably building the 'id' part anyway, this is a good way to handle these things. You can also show the admin users extra features, such as user management or section administration, through the same test. (Note that I'm using a simplified SELECT compared to your UNION/INNER JOIN query but the concept is the same; I've just reduced complexity to improve readability)

As an SQL query

I think you would need to use a stored procedure or function to do the test strictly in SQL. There is no 'if/then/else' construct in native MySQL/MariaDB-flavor SQL, and I don't see any obvious way to perform two different queries depending on the result of the admin-test. Someone sufficiently motivated may be able to piece together a CASE or IF statement, but in a few minutes of testing I wasn't able to get anything remotely close to what you want (for instance running SELECT (SELECT IF(1>2,'`first_name`','`last_name`')) FROM `actor` WHERE 1` in the 'sakila' database did not return the intended result). I'd be happy to be shown wrong, but this seems like a fragile test that's better handled as application-level logic.

Answer 2

Its a little hard to answer your question but i can make a guess. What you are looking for is not achieved in phpmyadmin. PhpAdmin displays all the tables and all that the tables contain.

To show various results as you require from phpAdmin you have to learn at least one of the programming languages that query the data in those tables. Many people learn and use PHP so that would be a good place for you to start.

Good luck in your programming.